Retrieving data isn’t all that you can do with generated SQL statements. Ann Zeigler shows how to create an update-and-append statement on the fly.

Ever since reading the Smart Access tip by Alan Biggs (October 1996) on using one query to “update-and-append” records in one step, I’ve used the technique many times to import data from remote tables into central tables. The beauty of Alan’s solution is that a single SQL statement joins two tables and performs two actions:

• Where the two tables have matching records, the primary table is updated with the data from the secondary table.
• If the secondary table has records that the primary table doesn’t, those records are appended to the primary table.

This technique gives me all the benefits of synchronization without the hassle of replication.

In my system, I use a table with one row for each table to be synchronized (see Table 1). This table lets me control the order that I process the tables (to avoid relationship problems), gives me a place to store the number of records affected, holds the SQL statement that was run, and addresses other needs. My technique does have two downsides:

• It’s a tedious task to set up the SQL queries that I need, especially on tables with many fields.
• Whenever the data structure changes for one of the tables, I have to remember to go back and update the SQL statement that will update the table.

Table 1. The zstblRemoteDataImport table structure.

After looking at my SQL for the “update-and-append” queries, I realized that I should be able to write a procedure that inspects the properties of each table to be synchronized and then creates the “update-and-append” SQL for that table based on the table’s current structure.

Generating the SQL

For this article, I’m assuming that the remote table is in another database, and that the table into which the data is being imported/appended has the same name and same structure as the remote table. My “update-and-append” SQL has three sections or clauses:

1. The UPDATE clause, which requires the path to the database containing the table from which data will be imported/appended and an alias for the remote table (that is, TableName_1):

UPDATE [FullPath and Filename].TableName AS TableName_

2. The LEFT JOIN section, which requires the table name, as well as each field in the table’s primary key (it’s the LEFT JOIN that makes this SQL an “update-and-append”). This code assumes that the table has three fields in its primary. If your primary key consists of a single field, then this code gets simpler:

LEFT JOIN TableName
ON (TableName_1.PKField1 = TableName.PKField1)
AND (TableName_1.PKField2 = TableName.PKField2)
AND (TableName_1.PKField3 = TableName.PKField3)

3. The SET clause, which sets each field in the local table to the value of its counterpart field in the remote table:

SET TableName.Field1 = [TableName_1].[Field1],
TableName.Field2 = [TableName_1].[Field2],
TableName.Field3 = [TableName_1].[Field3],
etc.

I have a function called ImportRemoteData() that handles importing the records. ImportRemoteData is passed the name of the table and the path to the MDB and, in turn calls the routine that builds my SQL. For brevity, I’ve omitted the error-handling code and other project-specific code from the functions that follow.

BuildUpdateSQL() builds the last two portions of the update-and-append statement. It accepts a table name as a parameter and creates a tabledef object for that table. The code will extract information about the table from the TableDef object:

Sub BuildUpdateSQL(strTbl As String) As String
Dim db As DAO.Database
Dim strSQL As String
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTbl)

The function first builds the SQL statement’s FROM clause, and then loops through all the fields in the tabledef’s Fields collection to determine which ones are part of the table’s primary key. To simplify this activity, my IsFieldInPK() function calls the FMS Total Access function IndexFieldsToArray_TSB() passing the database name, table name, field name, and the index name of “PrimaryKey.” The FMS function returns True if the passed field is in the table’s primary key:

strSQl = "." & strTbl & " AS " & strTbl & _
"_1 LEFT JOIN " & strTbl & " ON"

For Each fld In tdf.Fields
If IsFieldInPK(CurrentDb.Name, _
tdf.Name, fld.Name) Then
strSQl = strSQl & " (" & strTbl & "_1." & _
fld.Name & " = " & strTbl & "." & _
fld.Name & ") AND"
End If

Next fld

Once the Join expression has been built, the function loops through all the fields a second time, this time building the SET clause. This part of the code removes a dangling AND from building the Join clause and adds a SET. All of my synchronized tables have two fields that I use to track synchronization: ImportDate and ImportBy. The portion of code that builds the SET clause uses a CASE statement to set the values of the ImportDate field to Now() and the ImportBy field to the CurrentUser() rather than to stored data. With this information, I can track when particular records were imported and by whom:

If Right(strSQl, 3) = "AND" Then
strSQl = Left(strSQl, Len(strSQl) - 3) & "SET"
For Each fld In tdf.Fields
Select Case fld.Name
Case "ImportDate"
strSQl = strSQl & " " & strTbl & "." & _
fld.Name & " = Now(),"
Case "ImportBy"
strSQl = strSQl & " " & strTbl & "." & _
fld.Name & " = CurrentUser(),"
Case Else
strSQl = strSQl & " " & strTbl & "." & _
fld.Name & " = [" & strTbl & "_1].[" & _
fld.Name & "],"
End Select
Next fld

Once the full SQL statement has been created, the function cleans up the syntax, returns the SQL to whatever function calls it, and cleans up the objects created in the routine:

If Right(strSQl, 1) = "," Then
strSQl = Left(strSQl, Len(strSQl) - 1) & ";"
End If
BuildUpdateSQL = strSQl

Set tdf = Nothing
Set fld = Nothing

Exit Function

Necessary functions

Here’s the IsFieldInPK() function, which is called by the BuildUpdateSQL() function:

Public Function IsFieldInPK(strMDB As String, _
strTbl As String, strFld As String) As Boolean

Dim aFields() As String
Dim intCounter As Integer
Dim intCount As Integer
Dim bolPK As Boolean

intCount = IndexFieldsToArray_TSB(strMDB, strTbl, _
"PrimaryKey", aFields())
bolPK = False

For intCounter = 0 To intCount - 1
If strFld = aFields(intCounter) Then
bolPK = True
Exit For
End If
Next intCounter

IsFieldInPK = bolPK

End Function

The function that actually imports the records is very straightforward. The function opens the table that lists the tables to import (zstblRemoteDataImport) and loops through every row, calling the BuildUpdateSQL function for each table. It’s important that the zstblRemoteDataImport recordset be sorted by the table’s SortBy field. This field ensures that the table hierarchy is respected in order to successfully import child records (that is, it ensures that parent records are added before child records).

This routine builds the Update clause and appends the text returned by the BuildUpdateSQL() if valid. The code then creates a querydef using the complete SQL statement. After the querydef is executed, the number of records imported/appended is read from the QueryDef and written into the table for reporting on the import process to the user.

Here’s the function that imports the data from each table listed in zstblRemoteDataImport:

Public Function ImportRemoteData _
(strRemoteDataLoc As String) As Boolean

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strTbl As String
Dim strSQL As String

Set db = CurrentDb()

With rst
.MoveFirst

Do Until .EOF
strTbl = .Fields("TblName")
strSQL = "UPDATE [" & strRemoteDataLoc & "]" & _
BuildUpdateSQL("strTbl")

Set qdf = db.CreateQueryDef("",strSQL)
qdf.Execute

.Edit
.Fields("UpdateSQL") = strSQL
.Fields("RecsImported") = qdf.RecordsAffected
.Update
.MoveNext
Loop

End With
rst.Close

ImportRemoteDataImport = True

Set rst = Nothing
Set qdf = Nothing

End Function

To use this code, just add the names of the tables you wish to synchronize to the zstblRemoteDataImport table. Be sure to assign values in the SortBy field that reflect the referential integrity of your data model. You can then call the ImportRemoteData() function, passing the path and filename of the remote database to be imported when you want to synchronize. The BuildUpdateSQL() function will create SQL statements specific to the current data structure of the tables in the zstblRemoteDataImport table at the time that you start the synchronizing. The SQL statements that are run and the number of records affected will be stored in the zstblRemoteDataImport table for future reference.

Other pages on the site you may wish to read

 Access Subquery Techniques
Navigation Through Recursion