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.
Column name | Data type | Description |
TblName | Text | Name of table to be imported |
UpdateSQL | Memo | SQL that was generated for last import/append |
SortBy | Integer | Sort order for table relative to other tables |
RecordsImported | Long | Number of records updated/appended in last run |
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:
- 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_
- 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)
- 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