DOCUMENT:Q167227 11-JAN-2001 [vbwin] TITLE :HOWTO: Create and Update a Replicated Access Database PRODUCT :Microsoft Visual Basic for Windows PROD/VER:4.00 5.00 OPER/SYS: KEYWORDS:kbinterop kbVBp500 kbGrpDSVB ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Professional Edition for Windows, versions 4.0, 5.0 - Microsoft Visual Basic Enterprise Edition for Windows, versions 4.0, 5.0 ------------------------------------------------------------------------------- SUMMARY ======= This sample demonstrates how to create an Access MDB and then replicate it. It then shows how to update the replica and master MDBs. The article does not cover handling update conflicts between two replicated MDBs. MORE INFORMATION ================ The following Code will work in Visual Basic 4.0 or 5.0. The steps needed to create the example are for the Visual Basic 5.0 interface. Step-by-Step Example -------------------- 1. Create a new Standard EXE project. Form1 is created by default. 2. From the Project Menu, select Components. 3. From the Components form on the Controls tab, check the Microsoft Data Bound Grid Control and then click the OK button. 4. Place two Data Controls on the form and set the following properties: Data1.Name = datMaster Data2.Name = datRep1 5. Place two Data Bound Grids on the form and set the following properties: Grid1.Name = grdMaster Grid1.Caption = Master Grid1.DataSource = datMaster Grid2.Name = grdRep1 Grid1.Caption = Rep1 Grid1.DataSource = datRep1 6. Place four CommandButtons on the form and set the following properties: Command1.Name = cmdSyncAll Command1.Caption = Synchronize Bidirectional Command2.Name = cmdMsToRep1 Command2.Caption = One Way Master to Rep1 Command3.Name = cmdRep1ToMs Command3.Caption = One Way Rep1 to Master Command4.Name = cmdClose Command4.Caption = Close 7. From the View Menu, select Code, and then place the following code in the code area for the form: 'START CODE Dim mdbMaster As Database Dim mdbRep1 As Database Dim mrsMaster As Recordset Dim mrsRep1 As Recordset Private Sub cmdClose_Click() Unload Me End Sub Private Sub cmdMsToRep1_Click() 'Only send data from Master to Rep1 mdbMaster.Synchronize "rep1.mdb", dbRepExportChanges 'Update the data controls datMaster.Refresh datRep1.Refresh End Sub Private Sub cmdRep1ToMs_Click() 'Only send data from Rep1 to Master mdbMaster.Synchronize "rep1.mdb", dbRepImportChanges 'Update the data controls datMaster.Refresh datRep1.Refresh End Sub Private Sub cmdSyncAll_Click() 'Synchronize bidirectional mdbMaster.Synchronize "rep1.mdb", dbRepImpExpChanges 'Update the data controls datMaster.Refresh datRep1.Refresh End Sub Private Sub Form_Load() Dim iFlag As Integer 'used to check for files 'Allow the grids to have records added, deleted and updated grdrep1.Allowaddnew = true : grdrep1.AllowDelete = True grdrep1.AllowUpdate = True grdMaster.Allowaddnew = true : grdMaster.AllowDelete = True grdMaster.AllowUpdate = True iFlag = 0 ' Set the path to where the sample is running from ChDir App.Path 'check to see the MDBs are in the current directory 'if the MDBs are not in the current directory create them If Len(Dir("master.mdb")) < 1 Then iFlag = 0 Else iFlag = 1 If Len(Dir("rep1.mdb")) > 1 Then iFlag = iFlag + 1 If iFlag = 0 Then 'There are no MDB file CreateMaster 'call the sub that creates the master mdb MakeReplicas 'call the sub that creates the replicas End If If iFlag = 1 Then 'A file is missing MsgBox ("You are missing some MDB files" & vbCr & _ "Please see the readme.txt") Unload Me Exit Sub End If 'Open the MDB files Set mdbMaster = DBEngine(0).OpenDatabase("master.mdb") Set mdbRep1 = DBEngine(0).OpenDatabase("rep1.mdb") 'Open the tables Set mrsMaster = mdbMaster.OpenRecordset("table1") Set mrsRep1 = mdbRep1.OpenRecordset("table1") 'assign the recordsets to the data controls Set datMaster.Recordset = mrsMaster Set datRep1.Recordset = mrsRep1 End Sub Private Sub CreateMaster() Dim td As TableDef Dim fd As Field Dim ix As Index Dim prpReplicable As Property Set mdbMaster = Workspaces(0).CreateDatabase _ ("master.mdb", dbLangGeneral) Set td = mdbMaster.CreateTableDef("table1") Set fd = td.CreateField("id", dbLong) fd.Required = True ' No Null values allowed. fd.Attributes = dbAutoIncrField 'Auto increment field td.Fields.Append fd 'add the field to the tabledef Set fd = td.CreateField("name", dbText, 30) td.Fields.Append fd 'add the field to the tabledef 'create an index Set ix = td.CreateIndex("id") ix.Name = "id" ' Set properties in new Index. ix.Unique = True ix.Primary = True ix.Fields = "id" td.Indexes.Append ix ' Add Index to TableDefs collection. mdbMaster.TableDefs.Append td ' Add TableDefs to Database. ' make the database replicable Set prpReplicable = mdbMaster.CreateProperty("Replicable", _ dbText, "T") mdbMaster.Properties.Append prpReplicable 'Create a single record Set mrsMaster = mdbMaster.OpenRecordset("table1") mrsMaster.AddNew mrsMaster("name") = "Brian" mrsMaster.Update mrsMaster.Close End Sub Private Sub MakeReplicas() Dim s As String s = App.Path & "\rep1.mdb" 'create the replica mdbMaster.MakeReplica "rep1.mdb", dbRepMakeReadOnly mdbMaster.Close End Sub 'END CODE 8. Run the form by pressing the F5 key. 9. Try adding and editing records by entering information in the name field on the DBGrid (be sure to move off the record to ensure it is written to the table), and then select different update methods to see how the data is updated. (c) Microsoft Corporation 1997, All Rights Reserved. Contributions by Brian Combs, Microsoft Corporation Additional query words: kbDSupport DSDKB ====================================================================== Keywords : kbinterop kbVBp500 kbGrpDSVB Technology : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVBA500 kbVB500 kbVB400Search kbVB400 Version : 4.00 5.00 Issue type : kbhowto ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2001.