' This example describes how your scoring program can create a Bridgemate database. ' Language: Microsoft Visual Basic 6.0 ' Last update: February 8th, 2015 'To create the database, add DAO 3.51 or DAO 3.60 support to your VB application and 'call the routine CreateScoreDatabase. Afterwards, you fill in the correct data in the tables 'Section, Tables, RoundData and PlayerNumbers. Option Explicit Public dbScores As Database Public Const SCORES_TBL_CLIENTS = "Clients" Public Const SCORES_CLI_ID = "ID" Public Const SCORES_CLI_COMPUTER = "Computer" Public Const SCORES_TBL_SESSION = "Session" Public Const SCORES_SES_ID = "ID" Public Const SCORES_SES_NAME = "Name" Public Const SCORES_SES_DATE = "Date" Public Const SCORES_SES_TIME = "Time" Public Const SCORES_SES_GUID = "GUID" Public Const SCORES_SES_STATUS = "Status" Public Const SCORES_SES_SHOWINAPP = "ShowInApp" Public Const SCORES_TBL_SECTION = "Section" Public Const SCORES_SEC_ID = "ID" Public Const SCORES_SEC_LETTER = "Letter" Public Const SCORES_SEC_TABLES = "Tables" Public Const SCORES_SEC_MISSINGPAIR = "MissingPair" Public Const SCORES_SEC_EWMOVEBEFOREPLAY = "EWMoveBeforePlay" Public Const SCORES_SEC_SESSION = "Session" Public Const SCORES_SEC_SCOREMETHOD = "ScoringType" Public Const SCORES_TBL_TABLES = "Tables" Public Const SCORES_TAB_SECTION = "Section" Public Const SCORES_TAB_TABLE = "Table" Public Const SCORES_TAB_COMPUTERID = "ComputerID" Public Const SCORES_TAB_STATUS = "Status" Public Const SCORES_TAB_LOGONOFF = "LogOnOff" Public Const SCORES_TAF_CURRENTROUND = "CurrentRound" Public Const SCORES_TAF_CURRENTBOARD = "CurrentBoard" Public Const SCORES_TAF_UPDATEFROMROUND = "UpdateFromRound" Public Const SCORES_TAF_GROUP = "Group" Public Const SCORES_TBL_ROUNDS = "RoundData" Public Const SCORES_ROU_SECTION = "Section" Public Const SCORES_ROU_TABLE = "Table" Public Const SCORES_ROU_ROUND = "Round" Public Const SCORES_ROU_NSPAIR = "NSPair" Public Const SCORES_ROU_EWPAIR = "EWPair" Public Const SCORES_ROU_LOWBOARD = "LowBoard" Public Const SCORES_ROU_HIGHBOARD = "HighBoard" Public Const SCORES_RON_CUSTOMBOARDS = "CustomBoards" Public Const SCORES_TBL_DATA = "ReceivedData" Public Const SCORES_TBL_INTERMEDIATEDATA = "IntermediateData" Public Const SCORES_DAT_ID = "ID" Public Const SCORES_DAT_SECTION = "Section" Public Const SCORES_DAT_TABLE = "Table" Public Const SCORES_DAT_ROUND = "Round" Public Const SCORES_DAT_BOARD = "Board" Public Const SCORES_DAT_PAIRNS = "PairNS" Public Const SCORES_DAT_PAIREW = "PairEW" Public Const SCORES_DAT_DECLARER = "Declarer" Public Const SCORES_DAT_DIRECTION = "NS/EW" Public Const SCORES_DAT_CONTRACT = "Contract" Public Const SCORES_DAT_RESULT = "Result" Public Const SCORES_DAT_LEADCARD = "LeadCard" Public Const SCORES_DAT_REMARKS = "Remarks" Public Const SCORES_DAT_DATE = "DateLog" Public Const SCORES_DAT_TIME = "TimeLog" Public Const SCORES_DAT_PROCESSED = "Processed" Public Const SCORES_DAT_ERASED = "Erased" Public Const SCORES_DAT_EXTERNALUPDATE = "ExternalUpdate" Public Const SCORES_TBL_BIDDINGDATA = "BiddingData" Public Const SCORES_BID_ID = "ID" Public Const SCORES_BID_SECTION = "Section" Public Const SCORES_BID_TABLE = "Table" Public Const SCORES_BID_ROUND = "Round" Public Const SCORES_BID_BOARD = "Board" Public Const SCORES_BID_COUNTER = "Counter" Public Const SCORES_BID_DIRECTION = "Direction" Public Const SCORES_BID_BID = "Bid" Public Const SCORES_BID_DATE = "DateLog" Public Const SCORES_BID_TIME = "TimeLog" Public Const SCORES_BID_ERASED = "Erased" Public Const SCORES_TBL_PLAYDATA = "PlayData" Public Const SCORES_PLA_ID = "ID" Public Const SCORES_PLA_SECTION = "Section" Public Const SCORES_PLA_TABLE = "Table" Public Const SCORES_PLA_ROUND = "Round" Public Const SCORES_PLA_BOARD = "Board" Public Const SCORES_PLA_COUNTER = "Counter" Public Const SCORES_PLA_DIRECTION = "Direction" Public Const SCORES_PLA_CARD = "Card" Public Const SCORES_PLA_DATE = "DateLog" Public Const SCORES_PLA_TIME = "TimeLog" Public Const SCORES_PLA_ERASED = "Erased" Public Const SCORES_TBL_PLAYERCODES = "PlayerNumbers" Public Const SCORES_NUM_SECTION = "Section" Public Const SCORES_NUM_TABLE = "Table" Public Const SCORES_NUM_DIRECTION = "Direction" Public Const SCORES_NUM_NUMBER = "Number" Public Const SCORES_NUM_NAME = "Name" Public Const SCORES_NUM_UPDATED = "Updated" Public Const SCORES_NUM_TIME = "TimeLog" Public Const SCORES_NUM_PROCESSED = "Processed" Public Const SCORES_TBL_SETTINGS = "Settings" Public Const SCORES_SET_SECTION = "Section" Public Const SCORES_SET_SHOWRESULTS = "ShowResults" Public Const SCORES_SET_SHOWOWNRESULT = "ShowOwnResult" Public Const SCORES_SET_REPEATRESULTS = "RepeatResults" Public Const SCORES_SET_MAXIMUMRESULTS = "MaximumResults" Public Const SCORES_SET_SHOWPERCENTAGE = "ShowPercentage" Public Const SCORES_SET_GROUPSECTIONS = "GroupSections" Public Const SCORES_SET_SCOREPOINTS = "ScorePoints" Public Const SCORES_SET_ENTERRESULTSMETHOD = "EnterResultsMethod" Public Const SCORES_SET_SHOWPAIRNUMBERS = "ShowPairNumbers" Public Const SCORES_SET_INTERMEDIATERESULTS = "IntermediateResults" Public Const SCORES_SET_AUTOPOWEROFFTIME = "AutopoweroffTime" Public Const SCORES_SET_VERIFICATIONTIME = "VerificationTime" Public Const SCORES_SET_SHOWCONTRACT = "ShowContract" Public Const SCORES_SET_LEADCARD = "LeadCard" Public Const SCORES_SET_MEMBERNUMBERS = "MemberNumbers" Public Const SCORES_SET_MEMBERNUMBERSNOBLANKENTRY = "MemberNumbersNoBlankEntry" Public Const SCORES_SET_BOARDORDERVERIFICATION = "BoardOrderVerification" Public Const SCORES_SET_HANDRECORDVALIDATION = "HandRecordValidation" Public Const SCORES_SET_AUTOSHUTDOWNBPC = "AutoShutDownBPC" Public Const SCORES_SET_BM2PINCODE = "BM2PINcode" Public Const SCORES_SET_BM2CONFIRMNOPLAY = "BM2ConfirmNP" Public Const SCORES_SET_BM2REMAININGBOARDS = "BM2RemainingBoards" Public Const SCORES_SET_BM2NEXTSEATINGS = "BM2NextSeatings" Public Const SCORES_SET_BM2SCORERECAP = "BM2ScoreRecap" Public Const SCORES_SET_BM2SHOWSCORERECAPAUTOMATICALLY = "BM2AutoShowScoreRecap" Public Const SCORES_SET_BM2CORRECTSCORES = "BM2ScoreCorrection" Public Const SCORES_SET_BM2AUTOBOARDNUMBER = "BM2AutoBoardNumber" Public Const SCORES_SET_BM2FIRSTBOARDMANUALLY = "BM2FirstBoardManually" Public Const SCORES_SET_BM2RESULTSOVERVIEW = "BM2ResultsOverview" Public Const SCORES_SET_BM2SHOWPLAYERNAMES = "BM2ShowPlayerNames" Public Const SCORES_SET_BM2RANKING = "BM2Ranking" Public Const SCORES_SET_BM2GAMESUMMARY = "BM2GameSummary" Public Const SCORES_SET_BM2SUMMARYPOINTS = "BM2SummaryPoints" Public Const SCORES_SET_BM2PAIRNUMBERENTRY = "BM2PairNumberEntry" Public Const SCORES_SET_BM2RESETFUNCTIONKEY = "BM2ResetFunctionKey" Public Const SCORES_SET_BM2RECORDBIDDING = "BM2RecordBidding" Public Const SCORES_SET_BM2RECORDPLAY = "BM2RecordPlay" Public Const SCORES_SET_BM2VALIDATERECORDING = "BM2ValidateRecording" Public Const SCORES_SET_BM2SHOWHANDS = "BM2ShowHands" Public Const SCORES_SET_BM2MEMBERNUMBERVALIDATION = "BM2NumberValidation" Public Const SCORES_SET_BM2NAMESOURCE = "BM2NameSource" Public Const SCORES_SET_BM2VIEWHANDRECORD = "BM2ViewHandRecord" Public Const SCORES_SET_BM2ENTERHANDRECORD = "BM2EnterHandRecord" Public Const SCORES_SET_BM2ENTERHANDRECORDENDOF = "BM2EnterHandRecordWhen" Public Const SCORES_SET_BM2TEXTBASEDNUMBER = "BM2TextBasedNumber" Public Const SCORES_TBL_HANDRECORD = "HandRecord" Public Const SCORES_HAN_SECTION = "Section" Public Const SCORES_HAN_BOARD = "Board" Public Const SCORES_HAN_NORTHSPADES = "NorthSpades" Public Const SCORES_HAN_NORTHHEARTS = "NorthHearts" Public Const SCORES_HAN_NORTHDIAMONDS = "NorthDiamonds" Public Const SCORES_HAN_NORTHCLUBS = "NorthClubs" Public Const SCORES_HAN_EASTSPADES = "EastSpades" Public Const SCORES_HAN_EASTHEARTS = "EastHearts" Public Const SCORES_HAN_EASTDIAMONDS = "EastDiamonds" Public Const SCORES_HAN_EASTCLUBS = "EastClubs" Public Const SCORES_HAN_SOUTHSPADES = "SouthSpades" Public Const SCORES_HAN_SOUTHHEARTS = "SouthHearts" Public Const SCORES_HAN_SOUTHDIAMONDS = "SouthDiamonds" Public Const SCORES_HAN_SOUTHCLUBS = "SouthClubs" Public Const SCORES_HAN_WESTSPADES = "WestSpades" Public Const SCORES_HAN_WESTHEARTS = "WestHearts" Public Const SCORES_HAN_WESTDIAMONDS = "WestDiamonds" Public Const SCORES_HAN_WESTCLUBS = "WestClubs" Public Const SCORES_TBL_PLAYERNAMES = "PlayerNames" Public Const SCORES_NAM_ID = "ID" Public Const SCORES_NAM_NAME = "Name" Public Const SCORES_NAM_STRID = "strID" Public Const SCORES_NAM_INDEX_ID = "IDIndex" Public Const SCORES_NAM_INDEX_STRID = "strIDIndex" Public Const SCORES_TBL_HANDEVALUATION = "HandEvaluation" Public Const SCORES_HEV_SECTION = "Section" Public Const SCORES_HEV_BOARD = "Board" Public Const SCORES_HEV_NORTHSPADES = "NorthSpades" Public Const SCORES_HEV_NORTHHEARTS = "NorthHearts" Public Const SCORES_HEV_NORTHDIAMONDS = "NorthDiamonds" Public Const SCORES_HEV_NORTHCLUBS = "NorthClubs" Public Const SCORES_HEV_NORTHNOTRUMP = "NorthNotrump" Public Const SCORES_HEV_EASTSPADES = "EastSpades" Public Const SCORES_HEV_EASTHEARTS = "EastHearts" Public Const SCORES_HEV_EASTDIAMONDS = "EastDiamonds" Public Const SCORES_HEV_EASTCLUBS = "EastClubs" Public Const SCORES_HEV_EASTNOTRUMP = "EastNotrump" Public Const SCORES_HEV_SOUTHSPADES = "SouthSpades" Public Const SCORES_HEV_SOUTHHEARTS = "SouthHearts" Public Const SCORES_HEV_SOUTHDIAMONDS = "SouthDiamonds" Public Const SCORES_HEV_SOUTHCLUBS = "SouthClubs" Public Const SCORES_HEV_SOUTHNOTRUMP = "SouthNotrump" Public Const SCORES_HEV_WESTSPADES = "WestSpades" Public Const SCORES_HEV_WESTHEARTS = "WestHearts" Public Const SCORES_HEV_WESTDIAMONDS = "WestDiamonds" Public Const SCORES_HEV_WESTCLUBS = "WestClubs" Public Const SCORES_HEV_WESTNOTRUMP = "WestNotrump" Public Const SCORES_HEV_INDEX_ID = "IDIndex" 'API call for retrieving computer name. Declare Function apiGetComputerName Lib "kernel32.dll" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long Public Sub AddFieldToTable(tblName As TableDef, strFieldName As String, booAllowZeroLength As Boolean, _ intFieldType As Integer, Optional lngAttributes As Long, _ Optional intMaxLength As Integer, Optional varStandardValue As Variant) 'Add a new field to an existing table. Table must have been opened before 'as a Tabledef object. Dim tmpNewField As Field 'Create field with the given parameters. Set tmpNewField = tblName.CreateField(strFieldName, intFieldType, intMaxLength) 'AllowZeroLenght only when it is a textfield or memo field. If intFieldType = dbText Or intFieldType = dbMemo Then tmpNewField.AllowZeroLength = booAllowZeroLength End If 'Set attributes. tmpNewField.Attributes = lngAttributes 'Default value. If Not IsMissing(varStandardValue) Then tmpNewField.DefaultValue = varStandardValue End If 'Add field to the table. tblName.Fields.Append tmpNewField End Sub Public Function NoRecordSelected(recRecordSet As Recordset) As Boolean 'Check if there is a record selected. If not, BOF or EOF is false. If recRecordSet.BOF = True Or recRecordSet.EOF = True Or recRecordSet.AbsolutePosition = -1 Then NoRecordSelected = True Else NoRecordSelected = False End If End Function Public Function EditOneField(recRecordSet As Recordset, strField As String, varValue As Variant) As Boolean 'Update one field within the current record. On Error GoTo ErrorHandler recRecordSet.Edit recRecordSet.Fields(strField) = varValue recRecordSet.Update EditOneField = True ErrorHandler: End Function Public Sub CreateScoreDatabase(strFileName As String, intDBVersion As Integer) 'Create the Bridgemate database with the filename strFIleName. 'By calling this subroutine the database will be created automatically. 'NOTE: the database is only to be created here. It does not fill the database with the correct 'records. 'intDBVersion: 'Access 97 format = 32 (dbVersion30) 'Access 2000-2003 format = 64 (dbVersion40) Dim i As Integer, t As Integer Dim tblNew As TableDef Dim strIndexFields() As String Set dbScores = CreateDatabase(strFileName, dbLangGeneral, intDBVersion) 'Add the tables to the database. 'table Clients. Used to store the name of the clients computers. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_CLIENTS) Call AddFieldToTable(tblNew, SCORES_CLI_ID, False, dbLong, dbAutoIncrField) Call AddFieldToTable(tblNew, SCORES_CLI_COMPUTER, False, dbText, , 255, "") dbScores.TableDefs.Append tblNew 'table Sessions. Contains the sessions. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_SESSION) Call AddFieldToTable(tblNew, SCORES_SES_ID, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_SES_NAME, True, dbText, , 40) Call AddFieldToTable(tblNew, SCORES_SES_DATE, True, dbDate) Call AddFieldToTable(tblNew, SCORES_SES_TIME, True, dbDate) Call AddFieldToTable(tblNew, SCORES_SES_GUID, True, dbText, , 80) Call AddFieldToTable(tblNew, SCORES_SES_STATUS, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SES_SHOWINAPP, True, dbBoolean) dbScores.TableDefs.Append tblNew 'table Sections. Contains the sections within your session. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_SECTION) Call AddFieldToTable(tblNew, SCORES_SEC_ID, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_SEC_LETTER, False, dbText, , 2) Call AddFieldToTable(tblNew, SCORES_SEC_TABLES, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_SEC_MISSINGPAIR, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SEC_EWMOVEBEFOREPLAY, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SEC_SESSION, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SEC_SCOREMETHOD, False, dbInteger, , , 1) dbScores.TableDefs.Append tblNew 'table Tables. Contains all the tables for all sections. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_TABLES) Call AddFieldToTable(tblNew, SCORES_TAB_SECTION, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_TAB_TABLE, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_TAB_COMPUTERID, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_TAB_STATUS, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_TAB_LOGONOFF, False, dbInteger, , , 2) Call AddFieldToTable(tblNew, SCORES_TAF_CURRENTROUND, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_TAF_CURRENTBOARD, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_TAF_UPDATEFROMROUND, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_TAF_GROUP, False, dbInteger, , , 0) dbScores.TableDefs.Append tblNew 'table RoundData. Contains the movement data for all tables. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_ROUNDS) Call AddFieldToTable(tblNew, SCORES_ROU_SECTION, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_ROU_TABLE, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_ROU_ROUND, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_ROU_NSPAIR, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_ROU_EWPAIR, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_ROU_LOWBOARD, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_ROU_HIGHBOARD, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_RON_CUSTOMBOARDS, True, dbText, , 255) dbScores.TableDefs.Append tblNew 'table ReceivedData. You need to create it here, but it will be filled by 'BPC when results are coming in. For t = 1 To 2 Set tblNew = dbScores.CreateTableDef(IIf(t = 1, SCORES_TBL_DATA, SCORES_TBL_INTERMEDIATEDATA)) Call AddFieldToTable(tblNew, SCORES_DAT_ID, False, dbLong, dbAutoIncrField) Call AddFieldToTable(tblNew, SCORES_DAT_SECTION, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_DAT_TABLE, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_DAT_ROUND, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_DAT_BOARD, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_DAT_PAIRNS, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_DAT_PAIREW, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_DAT_DECLARER, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_DAT_DIRECTION, True, dbText, , 2, "") Call AddFieldToTable(tblNew, SCORES_DAT_CONTRACT, True, dbText, , 10, "") Call AddFieldToTable(tblNew, SCORES_DAT_RESULT, True, dbText, , 10, "") Call AddFieldToTable(tblNew, SCORES_DAT_LEADCARD, True, dbText, , 10, "") Call AddFieldToTable(tblNew, SCORES_DAT_REMARKS, True, dbText, , 255, "") Call AddFieldToTable(tblNew, SCORES_DAT_DATE, False, dbDate) Call AddFieldToTable(tblNew, SCORES_DAT_TIME, False, dbDate) Call AddFieldToTable(tblNew, SCORES_DAT_PROCESSED, False, dbBoolean, , , False) For i = 1 To 4 Call AddFieldToTable(tblNew, SCORES_DAT_PROCESSED & CStr(i), False, dbBoolean, , , False) Next i Call AddFieldToTable(tblNew, SCORES_DAT_ERASED, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_DAT_EXTERNALUPDATE, False, dbBoolean, , , False) dbScores.TableDefs.Append tblNew Next t 'table PlayerNumbers. Only required if you want your scoring program to have 'the possibility of registering the player numbers from Bridgemate input. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_PLAYERCODES) Call AddFieldToTable(tblNew, SCORES_NUM_SECTION, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_NUM_TABLE, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_NUM_DIRECTION, False, dbText, , 2, "") Call AddFieldToTable(tblNew, SCORES_NUM_NUMBER, True, dbText, , 16, "") Call AddFieldToTable(tblNew, SCORES_NUM_NAME, True, dbText, , 18, "") Call AddFieldToTable(tblNew, SCORES_NUM_UPDATED, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_NUM_TIME, False, dbDate) Call AddFieldToTable(tblNew, SCORES_NUM_PROCESSED, False, dbBoolean, , , False) dbScores.TableDefs.Append tblNew 'table BiddingData. Only required when you want to record the complete bidding 'and play using Bridgemate. Requires "Bridgemate Recorder" version. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_BIDDINGDATA) Call AddFieldToTable(tblNew, SCORES_BID_ID, False, dbLong, dbAutoIncrField) Call AddFieldToTable(tblNew, SCORES_BID_SECTION, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_BID_TABLE, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_BID_ROUND, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_BID_BOARD, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_BID_COUNTER, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_BID_DIRECTION, True, dbText, , 2, "") Call AddFieldToTable(tblNew, SCORES_BID_BID, True, dbText, , 10, "") Call AddFieldToTable(tblNew, SCORES_BID_DATE, False, dbDate) Call AddFieldToTable(tblNew, SCORES_BID_TIME, False, dbDate) Call AddFieldToTable(tblNew, SCORES_BID_ERASED, False, dbBoolean) dbScores.TableDefs.Append tblNew 'table PlayData. Only required when you want to record the complete bidding 'and play using Bridgemate. Requires "Bridgemate Recorder" version. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_PLAYDATA) Call AddFieldToTable(tblNew, SCORES_PLA_ID, False, dbLong, dbAutoIncrField) Call AddFieldToTable(tblNew, SCORES_PLA_SECTION, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_PLA_TABLE, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_PLA_ROUND, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_PLA_BOARD, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_PLA_COUNTER, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_PLA_DIRECTION, True, dbText, , 2, "") Call AddFieldToTable(tblNew, SCORES_PLA_CARD, True, dbText, , 10, "") Call AddFieldToTable(tblNew, SCORES_PLA_DATE, False, dbDate) Call AddFieldToTable(tblNew, SCORES_PLA_TIME, False, dbDate) Call AddFieldToTable(tblNew, SCORES_PLA_ERASED, False, dbBoolean) dbScores.TableDefs.Append tblNew 'table Settings. Only required when you want to control Bridgemate settings 'from the scoring program. Include only those fields you want to control the 'values. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_SETTINGS) Call AddFieldToTable(tblNew, SCORES_SET_SECTION, False, dbInteger, , , 1) Call AddFieldToTable(tblNew, SCORES_SET_SHOWRESULTS, False, dbBoolean, , , True) Call AddFieldToTable(tblNew, SCORES_SET_SHOWOWNRESULT, False, dbBoolean, , , True) Call AddFieldToTable(tblNew, SCORES_SET_REPEATRESULTS, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_MAXIMUMRESULTS, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SET_SHOWPERCENTAGE, False, dbBoolean, , , True) Call AddFieldToTable(tblNew, SCORES_SET_GROUPSECTIONS, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_SCOREPOINTS, False, dbInteger, , , 1) Call AddFieldToTable(tblNew, SCORES_SET_ENTERRESULTSMETHOD, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SET_SHOWPAIRNUMBERS, False, dbBoolean, , , True) Call AddFieldToTable(tblNew, SCORES_SET_INTERMEDIATERESULTS, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_AUTOPOWEROFFTIME, False, dbInteger, , , 20) Call AddFieldToTable(tblNew, SCORES_SET_VERIFICATIONTIME, False, dbInteger, , , 2) Call AddFieldToTable(tblNew, SCORES_SET_SHOWCONTRACT, False, dbInteger, , , 1) Call AddFieldToTable(tblNew, SCORES_SET_LEADCARD, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_MEMBERNUMBERS, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_MEMBERNUMBERSNOBLANKENTRY, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BOARDORDERVERIFICATION, False, dbBoolean, , , True) Call AddFieldToTable(tblNew, SCORES_SET_HANDRECORDVALIDATION, False, dbBoolean, , , True) Call AddFieldToTable(tblNew, SCORES_SET_AUTOSHUTDOWNBPC, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2PINCODE, False, dbText, , 4, "0000") Call AddFieldToTable(tblNew, SCORES_SET_BM2CONFIRMNOPLAY, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2REMAININGBOARDS, False, dbBoolean, , , True) Call AddFieldToTable(tblNew, SCORES_SET_BM2NEXTSEATINGS, False, dbBoolean, , , True) Call AddFieldToTable(tblNew, SCORES_SET_BM2SCORERECAP, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2SHOWSCORERECAPAUTOMATICALLY, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2CORRECTSCORES, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2AUTOBOARDNUMBER, False, dbBoolean, , , True) Call AddFieldToTable(tblNew, SCORES_SET_BM2FIRSTBOARDMANUALLY, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2RESULTSOVERVIEW, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SET_BM2SHOWPLAYERNAMES, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SET_BM2RANKING, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SET_BM2GAMESUMMARY, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2SUMMARYPOINTS, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SET_BM2PAIRNUMBERENTRY, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SET_BM2RESETFUNCTIONKEY, False, dbBoolean, , , True) Call AddFieldToTable(tblNew, SCORES_SET_BM2RECORDBIDDING, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2RECORDPLAY, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2VALIDATERECORDING, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2SHOWHANDS, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2MEMBERNUMBERVALIDATION, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SET_BM2NAMESOURCE, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SET_BM2VIEWHANDRECORD, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2ENTERHANDRECORD, False, dbBoolean, , , False) Call AddFieldToTable(tblNew, SCORES_SET_BM2ENTERHANDRECORDENDOF, False, dbInteger, , , 0) Call AddFieldToTable(tblNew, SCORES_SET_BM2TEXTBASEDNUMBER, False, dbBoolean, , , False) dbScores.TableDefs.Append tblNew 'Add Handrecord table. Contains the hand records. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_HANDRECORD) Call AddFieldToTable(tblNew, SCORES_HAN_SECTION, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_HAN_BOARD, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_HAN_NORTHSPADES, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_NORTHHEARTS, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_NORTHDIAMONDS, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_NORTHCLUBS, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_EASTSPADES, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_EASTHEARTS, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_EASTDIAMONDS, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_EASTCLUBS, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_SOUTHSPADES, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_SOUTHHEARTS, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_SOUTHDIAMONDS, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_SOUTHCLUBS, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_WESTSPADES, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_WESTHEARTS, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_WESTDIAMONDS, True, dbText, , 13) Call AddFieldToTable(tblNew, SCORES_HAN_WESTCLUBS, True, dbText, , 13) dbScores.TableDefs.Append tblNew 'Add HandEvaluation table. Will be used by Bridgemate Control to store the makeable contract data. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_HANDEVALUATION) Call AddFieldToTable(tblNew, SCORES_HEV_SECTION, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_BOARD, False, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_NORTHSPADES, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_NORTHHEARTS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_NORTHDIAMONDS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_NORTHCLUBS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_NORTHNOTRUMP, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_EASTSPADES, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_EASTHEARTS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_EASTDIAMONDS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_EASTCLUBS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_EASTNOTRUMP, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_SOUTHSPADES, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_SOUTHHEARTS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_SOUTHDIAMONDS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_SOUTHCLUBS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_SOUTHNOTRUMP, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_WESTSPADES, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_WESTHEARTS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_WESTDIAMONDS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_WESTCLUBS, True, dbInteger) Call AddFieldToTable(tblNew, SCORES_HEV_WESTNOTRUMP, True, dbInteger) dbScores.TableDefs.Append tblNew 'Add PlayerNames table. Contains all players of your club for easy player name lookup. 'Add also the indices. Set tblNew = dbScores.CreateTableDef(SCORES_TBL_PLAYERNAMES) Call AddFieldToTable(tblNew, SCORES_NAM_ID, False, dbLong) Call AddFieldToTable(tblNew, SCORES_NAM_NAME, True, dbText, , 18) Call AddFieldToTable(tblNew, SCORES_NAM_STRID, True, dbText, , 18, "") dbScores.TableDefs.Append tblNew ReDim strIndexFields(1 To 1) strIndexFields(1) = SCORES_NAM_ID Call CreateIndexForTable(tblNew, SCORES_NAM_INDEX_ID, strIndexFields(), False, False) ReDim strIndexFields(1 To 1) strIndexFields(1) = SCORES_NAM_STRID Call CreateIndexForTable(tblNew, SCORES_NAM_INDEX_STRID, strIndexFields(), False, False) 'Add the current computer to the client list. This computer will be the default 'client. Call AddClientToDatabase(dbScores) 'Database is created and will be closed. The following step you should do, is fill in the 'tables Section, Tables, RoundData and PlayerNumbers (optionally) with the correct 'records and data. Use INSERT and UPDATE queries to insert the data. dbScores.Close End Sub Public Sub AddClientToDatabase(DB As Database) 'Add the current computer to the client tables. Dim tblClient As Recordset Set tblClient = DB.OpenRecordset(SCORES_TBL_CLIENTS) With tblClient .AddNew .Fields(SCORES_CLI_COMPUTER) = GetComputerName 'This is a API call .Update .Close End With End Sub Public Function GetClientIDFromDB(strClientName As String) As Integer 'Find the client name strClientName in the Client table and return its ID. Dim tblClient As Recordset Set tblClient = dbScores.OpenRecordset(SCORES_TBL_CLIENTS, dbOpenDynaset) tblClient.FindFirst SCORES_CLI_COMPUTER & "='" & strClientName & "'" GetClientIDFromDB = IIf(tblClient.NoMatch = True, 0, tblClient.Fields(SCORES_CLI_ID)) tblClient.Close End Function Public Sub AddPropertyToDB(DB As Database, strProperty As String, dbType As Long, varStandardValue As Variant) 'Add a property to the database. DB.Properties.Append DB.CreateProperty(strProperty, dbType, varStandardValue) End Sub Public Function ReadDBProperty(DB As Database, strProperty As String) As Variant 'Read property from database. ReadDBProperty = DB.Properties(strProperty) End Function Public Function GetRecordCount(recTbl As Recordset) As Long 'Return the number of records within a given recordset. On Error Resume Next recTbl.MoveLast recTbl.MoveFirst GetRecordCount = recTbl.RecordCount Err.Clear End Function Public Function GetComputerName() As String 'get the name of the current computer. Dim ComputerName$, Success As Long ComputerName$ = String$(1024, 32) Success = apiGetComputerName(ComputerName$, 1024) If Success Then GetComputerName = Left$(LTrim(RTrim(ComputerName$)), Len(LTrim(RTrim(ComputerName$))) - 1) End Function Public Function GetBMPROVersionString(booIncludeRevision As Boolean) As String 'Create a string containing BMPro version number GetBMPROVersionString = CStr(App.Major) & "." & CStr(App.Minor) & IIf(booIncludeRevision = True, "." & CStr(App.Revision), "") End Function Public Sub CreateIndexForTable(tblName As TableDef, strIndexName As String, strIndexField() As String, booUnique As Boolean, booPrimary As Boolean) 'Add an index to an existing table. Dim idxNew As Index Dim i As Integer Set idxNew = tblName.CreateIndex(strIndexName) For i = 1 To UBound(strIndexField) idxNew.Fields.Append idxNew.CreateField(strIndexField(i)) Next i idxNew.Unique = booUnique idxNew.Primary = booPrimary tblName.Indexes.Append idxNew End Sub