REPLICATING CODE TABLES USING ADO

Although Chapter 28 discusses replicating code tables, I thought it would be worthwhile to look briefly at ap_CheckReplicatedTables in this chapter (see Listing 27.6).

Listing 27.6. VideoApp(ADO).mdb: Replicating Code Tables
Sub ap_CheckReplicatedTables()

   Dim catLocal As New ADOX.Catalog
   Dim rstCheckRep As New ADODB.Recordset
   Dim cmdUpdateRep As New ADODB.Command
   Dim strTableName As String

   On Error GoTo Error_ap_CheckReplicatedTables

   Set catLocal.ActiveConnection = CurrentProject.Connection

   DoCmd.Echo True, "Checking for Replicated Tables..."

   '-- Attach the backend replicated table
   '-- and open the query that shows updated replicated tables
   On Error Resume Next

   catLocal.Tables.Delete "tblBackEndReplicatedTables"
   catLocal.Tables.Refresh

   On Error GoTo Error_ap_CheckReplicatedTables

   ap_CreateLinkedTableWithADO catLocal, "tblBackEndReplicatedTables", _
                "tblReplicatedTables", pstrBackEndPath & pstrBackEndName
   catLocal.Tables.Refresh

   Set cmdUpdateRep = _
      catLocal.Procedures("qryUpdateLastReplication").Command

   rstCheckRep.Open "qryCheckBackEndReplication", _
      CurrentProject.Connection, adOpenStatic

   '-- If a table has been updated, loop through


   Do Until rstCheckRep.EOF
      DoCmd.Echo True, "Replicating " & rstCheckRep!TableName & _
            ", Please wait..."

      '-- Delete the current local table,
      '-- and import the backend table
      On Error Resume Next
      strTableName = rstCheckRep!TableName

      catLocal.Tables.Delete strTableName
      catLocal.Tables.Refresh

      On Error GoTo Error_ap_CheckReplicatedTables

      DoCmd.TransferDatabase acImport, "Microsoft Access", _
         pstrBackEndPath & pstrBackEndName, acTable, _
         rstCheckRep!TableName, rstCheckRep!TableName

      catLocal.Tables.Refresh

      cmdUpdateRep.Parameters("CurrReplicatedTable") = strTableName
      cmdUpdateRep.Execute

      rstCheckRep.MoveNext
   Loop

   catLocal.Tables.Delete "tblBackEndReplicatedTables"

   DoCmd.Echo True

   '-- Clean up
   rstCheckRep.Close

   Exit Sub

Error_ap_CheckReplicatedTables:
   MsgBox Err.Description
   Resume 'Exit Sub

End Sub

First, the code needs to see whether any records in the tblReplicatedTables back-end table have more recent time stamps than those in the front-end version. The back-end table is linked to the front end as tblBackEndReplicatedTables. The first lines of code make sure that the link to compare the tables isn't already existing:

catLocal.Tables.Delete "tblBackEndReplicatedTables"
catLocal.Tables.Refresh

On Error GoTo Error_ap_CheckReplicatedTables

ap_CreateLinkedTableWithADO catLocal, "tblBackEndReplicatedTables", _
             "tblReplicatedTables", pstrBackEndPath & pstrBackEndName
catLocal.Tables.Refresh

Set cmdUpdateRep = _
   catLocal.Procedures("qryUpdateLastReplication").Command

rstCheckRep.Open "qryCheckBackEndReplication", _
   CurrentProject.Connection, adOpenStatic

After deleting the table if it exists, the next code line calls the ap_CreateLinkedTableWithADO routine, which creates a single ADO linked table. This is convenient if you have to do this at one time or another in an application. Here's the code for ap_CreateLinkedTableWithADO:

Sub ap_CreateLinkedTableWithADO(catCurr As ADOX.Catalog, _
  strDestTableName As String, strSourceTableName As String, _
               strDataMDB As String)

   Dim tblCurr As New ADOX.Table

   tblCurr.Name = strDestTableName
   Set tblCurr.ParentCatalog = catCurr

   tblCurr.Properties("Jet OLEDB:Link Datasource") = strDataMDB
   tblCurr.Properties("Jet OLEDB:Create Link") = True
   tblCurr.Properties("Jet OLEDB:Remote Table Name") = strSourceTableName

   catCurr.Tables.Append tblCurr

End Sub

The code for ap_CheckReplicatedTables goes on to set a reference to the command object and to the qryUpdateLastReplication query:

Set cmdUpdateRep = _
   catLocal.Procedures("qryUpdateLastReplication").Command

The last code segments for ap_CheckReplicatedTables perform the following tasks for each table that needs to be replicated:

  • Delete the current local copy of the code table.

  • Copy the back-end copy into the front end.

  • Execute a query that updates the back-end tblReplicatedTables table with a timestamp.

And there you go. Hopefully, this will help you get going using ADO in your code.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.15.219.80