Auto-Generated Update SQL

<< Click to Display Table of Contents >>

Navigation:  Queries >

Auto-Generated Update SQL

Ann Ziegler        

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:

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_1
 

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.

Your download file is called SQLSYNCH.ZIP in the file SA2002-02down.zip

This can be purchased with all the other downloads on this page

 

Other pages on the site you may wish to read

 

Access Subquery Techniques

Navigation Through Recursion