REPLICATING TABLES FROM BACK END TO FRONT END FOR BETTER PERFORMANCE

Some tables are updated only once or twice a week, if that often. These semi-static tables could be brought into the local database and increase performance because local tables generally are faster to access than linked tables. Such semi-static tables aren't quite static enough to keep them only in the front end. The solution to this is to replicate them from the back end to the front end when the back-end version is updated.

Tip

This works great for Access-based back ends, but performance will probably be better if you leave all the tables in the back end when it's a SQL Server back end. This is because when you have queries with the data located in more than one location, Access brings all the data down locally to process.


Access 9x/2000 and Windows 9x/NT support another kind of replication, using the Briefcase utility, from a copy of a complete database to another. This is different from what's being discussed here and is fully covered in Chapter 23, “Welcome to the World of Database Replication.”

To replicate tables from the back end to the front end, follow these general steps (the specific steps taken will be explained shortly):

1.
Edit the back-end version of a replicated table.

2.
When you're through editing, store the date and time of the edit in another table that tracks replicated tables.

3.
Replicate the local version right away.

4.
In the system startup routines, add a routine that checks the date of the last replication, comparing the front-end and back-end date and times.

5.
If the back-end version has been updated since the last replication, download it again. Then update the date/timestamp of the front-end version.

That's all there is to it. The first place to start is by creating the editor that will edit the back-end version of replicated tables and then update the back-end date/timestamp.

Creating a Replicated Table Editor

This editor works similar to the generic code editor mentioned in the preceding section, except that it also creates a temporary link to the back end. Figure 28.10 shows this editor, named ap_UpdateReplicatedBackend, with the Users table being edited.

Figure 28.10. The values being edited are in the back end and will be replicated the next time the system is started at other stations.


Unlike the code editor in the preceding section, the combo box used to choose which table to edit doesn't have to be populated with code. This makes the OnLoad event for the form much more straightforward. Listing 28.5 shows the event procedure.

Listing 28.5. VideoApp.mdb: Setting Up the Environment
Private Sub Form_Load()

   On Error GoTo Error_Form_Load

   '-- Initialize the edit flag
   intRepEdited = False
   '-- Grab the backend and path
   mstrBackEndPath = ap_GetDatabaseProp(CurrentDb(), "LastBackEndPath")
   mstrBackEndName = ap_GetDatabaseProp(CurrentDb(), "BackEndName")

   Exit Sub

Error_Form_Load:
   MsgBox Err.Description
   Exit Sub

End Sub

The variable intRepEdited is declared in the declarations section of the ap_GlobalRoutines module. It's initialized here in the OnLoad event as false and will be flagged as true in the AfterUpdate and OnDelete flags of the subforms used. Figure 28.11 shows the subform named UserRepSubform, its property sheet, and the event procedure for the AfterUpdate event.

Figure 28.11. The intRepEdited variable must be set to true in each subform's AfterUpdate and OnDelete events.


The variables strBackEndPath and strBackEndName are assigned values that are used in the rest of the events on the form. The row source for the cboTablesToEdit combo box is set to the table tblReplicatedTables, stored in the front end (see Figure 28.12).

Figure 28.12. The front-end version of tblReplicated-Tables contains the fields TableName and LastReplication.


The real power behind this form is when a table is selected from the cboTablesToEdit combo box. Figure 28.13 shows this combo box, along with its property sheet, in Design view.

Figure 28.13. This combo box is populated much more easily than the preceding section's combo box.


Listing 28.6 shows the code that occurs when a table is chosen from the list of replicated tables, in the BeforeUpdate event procedure. It will be broken down and discussed piece by piece after the listing.

Listing 28.6. VideoApp.mdb: Performing the Replication
Private Sub cboTablesToEdit_BeforeUpdate(Cancel As Integer)

   On Error Resume Next

   DoCmd.Echo False ' Me!subReplicatedTable.Form.Painting = False

   '-- Delete the previous attached table if there
   CurrentDb.TableDefs.Delete "RPL" & Me!cboTablesToEdit.OldValue

   On Error GoTo 0 'On Error GoTo Error_cboTablesToEdit_BeforeUpdate

   '-- Check and see if an edit has occurred
   CheckUpdateTime

   '-- Link the new table to edit and add RPL on the front of the name
   DoCmd.TransferDatabase acLink, "Microsoft Access", mstrBackEndPath & _
                "" & mstrBackEndName, , Me!cboTablesToEdit, _
                "RPL" & Me!cboTablesToEdit

   CurrentDb.TableDefs.Refresh
   '-- Assign the subform based on the table chosen
   '-- then update the form's caption.
   Me!subReplicatedTable.SourceObject = Mid$(Me!cboTablesToEdit, 4) & _
      "RepSubform"
   Me.Caption = "Editing Table: " & Me!cboTablesToEdit

   DoCmd.Echo True

   Exit Sub

Error_cboTablesToEdit_BeforeUpdate:

   DoCmd.Echo True
   MsgBox Err.Description
   Exit Sub

End Sub

Here's what occurs in this code:

  1. If a table was chosen before the current one, that link is deleted from the TableDefs collection. The On Error Resume Next line has the code carry on if there wasn't an error, and the OldValue property of the combo box is used to get the name of the prior table:

    On Error Resume Next

DoCmd.Echo False

'-- Delete the previous attached table if there
CurrentDb.TableDefs.Delete "RPL" & Me!cboTablesToEdit.OldValue

  2. The routine named CheckUpdateTime checks to see whether the prior table was updated. It also timestamps the back end, copies the replicated table to the local database, and updates the local replicated timestamp. This routine is performed after the cboTablesToEdit_AfterUpdate routine is completed.

  3. The new table is linked with the literal RPL in front of the table name, and the TableDefs collection is refreshed. These lines of code accomplish this:

    '-- Link the new table to edit and add RPL on the front of the name
DoCmd.TransferDatabase acLink, "Microsoft Access", mstrBackEndPath & _
      "" & mstrBackEndName, , Me!cboTablesToEdit, _
      "RPL" & Me!cboTablesToEdit
CurrentDb.TableDefs.Refresh

Tip

It's always a good idea to refresh the various collections when you append or delete objects to or from them, especially the TableDefs and QueryDefs collections. The reason for this is that new or deleted objects may not be seen immediately unless a Refresh method is performed. For more information on these collections, see Chapter 4.


  1. The final tasks to be accomplished are the assigning of the subform control SourceObject and the caption for the main form:

    '-- Assign the subform based on the table chosen
'-- then update the form's caption.
Me!subReplicatedTable.SourceObject = Mid$(Me!cboTablesToEdit, 4) & _
   "RepSubform"
Me.Caption = "Editing Table: " & Me!cboTablesToEdit

Again, the cboTablesToEdit_BeforeUpdate event procedure occurs whenever a new table is chosen to edit from the list in the cboTablesToEdit combo box. Something similar, although not quite so dramatic, occurs when the form is closed. The UnLoad event of the ap_UpdateReplicatedBackend form occurs, and the code shown in Listing 28.7 is performed.

Listing 28.7. VideoApp.mdb: Cleaning Up on the Way Out
Private Sub Form_Unload(Cancel As Integer)

   On Error Resume Next

   '-- Delete the replicated link from the frontend
   CurrentDb.TableDefs.Delete "RPL" & Me!cboTablesToEdit
   CurrentDb.TableDefs.Refresh

   '-- Check to see if anything was edited.
   CheckUpdateTime

   DoCmd.Echo True

End Sub

This code simply performs as documented. Figure 28.14 shows the ap_UpdateReplicatedBackend form after a table is edited and is being replicated.

Figure 28.14. This routine automatically replicates the changes to the front end immediately.


Finally, it's time to look at the CheckUpdateTime routine (see Listing 28.8).

Listing 28.8. VideoApp.mdb: Actual Replication Takes Place
Sub CheckUpdatetime()

  '-- If current table is edited update backend timestamp,
  '-- update the frontend data and timestamp for local,
  '-- then reset edit flag

  mstrBackEndPath = ap_GetDatabaseProp(CurrentDb(), "LastBackEndPath")
  mstrBackEndName = ap_GetDatabaseProp(CurrentDb(), "BackEndName")

  On Error GoTo Error_CheckUpdateTime

  If intRepEdited Then

     Dim qdfUpdateRep As QueryDef

     DoCmd.Echo False, "Updating Backend..."
     DoCmd.Hourglass True
     DoCmd.SetWarnings False

      '-- Timestamp the backend
      DoCmd.RunSQL "UPDATE tblReplicatedTables IN '" & _
            mstrBackEndPath & "" & mstrBackEndName & _
            "' SET tblReplicatedTables.LastUpdate = Now() " & _
            "Where (((tblReplicatedTables.TableName) = '" _
            & Me!cboTablesToEdit.OldValue & "'));"

     '-- Replicate the modified table
     DoCmd.Echo False, "Replicating " & Me!cboTablesToEdit.OldValue & _
        ", Please wait..."

     '-- Delete the current local table,
     '-- and import the backend table
     On Error Resume Next
     CurrentDb.TableDefs.Delete Me!cboTablesToEdit.OldValue
     CurrentDb.TableDefs.Refresh

     On Error GoTo Error_CheckUpdateTime
     DoCmd.TransferDatabase acImport, "Microsoft Access", _
               mstrBackEndPath & "" & mstrBackEndName, _
               acTable, Me!cboTablesToEdit.OldValue, _
               Me!cboTablesToEdit.OldValue

     CurrentDb.TableDefs.Refresh

     Set qdfUpdateRep = CurrentDb().QueryDefs("qryUpdateLastReplication")
     qdfUpdateRep.Parameters("CurrReplicatedTable") = _
               Me!cboTablesToEdit.OldValue
     qdfUpdateRep.Execute

     intRepEdited = False

     DoCmd.Hourglass False
     DoCmd.SetWarnings True

  End If

  Exit Sub

Error_CheckUpdateTime:

  DoCmd.Hourglass False
  DoCmd.SetWarnings True
  DoCmd.Echo True

  MsgBox Err.Description
  Exit Sub

End Sub

These tasks take place in Listing 28.8 if the current table in the subform has been edited:

  1. The table's entry in the table's back end is updated. This table consists of two fields: TableName and LastUpdate (see Figure 28.15). The lines of code that update this table are as follows:

    '-- Timestamp the backend
DoCmd.RunSQL "UPDATE tblReplicatedTables IN '" & _
      mstrBackEndPath & "" & mstrBackEndName & _
      "' SET tblReplicatedTables.LastUpdate = Now() " & _
      "Where (((tblReplicatedTables.TableName) = '" _
      & Me!cboTablesToEdit.OldValue & "'));"

    Figure 28.15. tblUsers and tblRatings are the only replicated tables kept in the back-end version of tblReplicated-Tables at this time.

  2. The current replicated table is deleted, and the updated table is imported from the back end:

    '-- Delete the current local table,
'-- and import the backend table
On Error Resume Next
CurrentDb.TableDefs.Delete Me!cboTablesToEdit.OldValue
CurrentDb.TableDefs.Refresh

On Error GoTo Error_CheckUpdateTime
DoCmd.TransferDatabase acImport, "Microsoft Access", _
          mstrBackEndPath & "" & mstrBackEndName, _
          acTable, Me!cboTablesToEdit.OldValue, _
          Me!cboTablesToEdit.OldValue
CurrentDb.TableDefs.Refresh

  3. Because the table has been replicated, the local version of tblReplicatedTables is updated with a timestamp, using the query qryUpdateLastReplication (see Figure 28.16). Here's the code that uses this query.

    Figure 28.16. This query updates the current entry for LastReplication, in the front-end version of the tblReplicated-Tables table with the Now() system variable.

    Set qdfUpdateRep = CurrentDb().QueryDefs("qryUpdateLastReplication")
qdfUpdateRep.Parameters("CurrReplicatedTable") _
   = Me!cboTablesToEdit.OldValue
qdfUpdateRep.Execute

Note

The piece of code in step 3 updates the front end of the current application making the changes. The front ends of the other stations will have a routine run at startup system checking time.


That's it for updating the replicated data by using the editor created. Next, the startup routines are examined to replicate the data, if required.

Looking at Startup Routines for Replicating Semi-Static Data

The pieces that make up the replication check routine are a query and a subroutine. The query, qryCheckBackEndReplication, compares the front-end and back-end table, tblReplicatedTables, and looks to see whether two things have occurred (see Figure 28.17):

  • The front-end LastReplication field is null. This means that the replication has never been performed for this particular table.

  • The LastUpdated field in the back-end tblReplicatedTables (which is linked as BackEndReplicatedTables for the query in Figure 28.17) is later than the LastReplication field of the front-end tblReplicatedTables.

    Figure 28.17. This query is very useful for comparing the back and front ends for semi-static table replication.

The subroutine that performs the check, named ap_CheckReplicatedTables, can be found in the modGlobalRoutines module. This routine performs some of the same tasks that were done in the CheckUpdateTime routine (refer to Listing 28.8) in that it replicates updated tables and timestamps the LastReplication field in the local tblReplicatedTables. The code, shown in its entirety in Listing 28.9, will be examined more closely later in this section.

Listing 28.9. VideoApp.mdb: Checking for Updated Tables
Sub ap_CheckReplicatedTables()

   Dim dbLocal As Database
   Dim dynCheckRep As Recordset
   Dim qdfUpdateRep As QueryDef
   Dim strBackEndPath As String, strBackEndName As String

   On Error GoTo Error_ap_CheckReplicatedTables

   Set dbLocal = CurrentDb()
   DoCmd.Echo True, "Checking for Replicated Tables..."

   '-- Grab the backend and path
   strBackEndPath = ap_GetDatabaseProp(dbLocal, "LastBackEndPath")
   strBackEndName = ap_GetDatabaseProp(dbLocal, "BackEndName")

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

   dbLocal.TableDefs.Delete "tblBackEndReplicatedTables"
   dbLocal.TableDefs.Refresh

   On Error GoTo Error_ap_CheckReplicatedTables

   DoCmd.TransferDatabase acLink, "Microsoft Access", _
                strBackEndPath & "" & strBackEndName, _
                acTable, "tblReplicatedTables", _
                "tblBackEndReplicatedTables"

   dbLocal.TableDefs.Refresh

   Set dynCheckRep = dbLocal.OpenRecordset _
                ("qryCheckBackEndReplication")

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

   If Not dynCheckRep.RecordCount = 0 Then

      Set qdfUpdateRep = dbLocal.QueryDefs _
                ("qryUpdateLastReplication")

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

         '-- Delete the current local table,
         '-- and import the backend table
         On Error Resume Next

         dbLocal.TableDefs.Delete dynCheckRep!TableName
         dbLocal.TableDefs.Refresh

         On Error GoTo Error_ap_CheckReplicatedTables
         DoCmd.TransferDatabase acImport, "Microsoft Access", _
                strBackEndPath & "" & strBackEndName, acTable, _
                dynCheckRep!TableName, dynCheckRep!TableName

         CurrentDb.TableDefs.Refresh

         qdfUpdateRep.Parameters("CurrReplicatedTable") = _
                dynCheckRep!TableName

         qdfUpdateRep.Execute

         dynCheckRep.MoveNext
      Loop

      qdfUpdateRep.Close

   End If

   dbLocal.TableDefs.Delete "tblBackEndReplicatedTables"

   DoCmd.Echo True

   '-- Clean up
   dynCheckRep.Close

   Exit Sub

Error_ap_CheckReplicatedTables:
   MsgBox Err.Description
   Exit Sub

End Sub

In looking at this routine, the place to start is by checking out what happens after the back-end name and path are stored to variables:

  1. The dynCheckRep recordset is opened off the qryCheckBackEndReplication query. If any records exist, the routine then loops through each table to replicate:

    '-- Open the query that shows updated replicated tables
Set dynCheckRep = dbLocal.OpenRecordset("qryCheckBackEndReplication")

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

If Not dynCheckRep.RecordCount = 0 Then

   Set qdfUpdateRep = dbLocal.QueryDefs("qryUpdateLastReplication")

   Do Until dynCheckRep.EOF

Note

A reference to the qryUpdateLastReplication query has been set in this piece of code as well. The parameter used in this query will be set in step 2.


  1. The current replicated table is deleted from the front end, and then the copy is imported from the back end, refreshing the TableDefs collection after each command. Finally, the parameter is set in the query that updates the LastReplication field for the current replicated table entry:

    '-- Delete the current local table,
'-- and import the backend table
On Error Resume Next

dbLocal.TableDefs.Delete dynCheckRep!TableName
dbLocal.TableDefs.Refresh

On Error GoTo Error_ap_CheckReplicatedTables
DoCmd.TransferDatabase acImpor
t, "Microsoft Access", _
       strBackEndPath & "" & strBackEndName, acTable, _
       dynCheckRep!TableName, dynCheckRep!TableName

CurrentDb.TableDefs.Refresh

qdfUpdateRep.Parameters("CurrReplicatedTable") = _
       dynCheckRep!TableName

qdfUpdateRep.Execute

All that's left is to loop through the recordset and clean up. Again, the routine in Listing 28.9 tests whether a table that's flagged for replication has been updated on the back end since the last replication. Figure 28.18 shows the ap_CheckReplicatedTables subroutine in action.

Figure 28.18. When the system first starts up, the routine should be run.


The ap_CheckReplicatedTables routine is called from the subroutine ap_AppInit, which is called when the system is first started. (For more information about the ap_AppInit routine and other startup system checking routines, see Chapter 26, “Startup Checking System Routines Using DAO.”) Here is the segment of ap_AppInit that calls ap_CheckReplicatedTables:

'-- Check for locally logged errors
ap_ErrorCheckLocal

'-- Check for Replicated Tables
ap_CheckReplicatedTables

'-- Section 11: Close the Splash Screen and Clean Up
DoCmd.Close acForm, "SplashScreen"
DoCmd.Echo True
dynSharedTables.Close

If you need to check for updated tables more frequently, you could put the ap_CheckReplicatedTables routine off the UserLogOutMonitor form and have the system check every so often. A problem that might occur is that it could be pretty slow to keep running that query every minute or so.

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

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