Comparing tables in Access and showing in VB

Ok, here is a quick stab.

Dim cnnAccess As ADODB.Connection
Dim rstPermanent As ADODB.Recordset
Dim rstTemporary As ADODB.Recordset

Set cnnAccess = New ADODB.Connection
cnnAccess.ConnectionString = "Put in your connection string here"
cnnAccess.Open
Set rstTemporary = New ADODB.Recordset
rstTemporary.Open "SELECT * From MonthlyTable",cnnAccess,adOpenStatic,adLockReadOnly
rstPermanent.Open "SELECT * From PermanentTable",cnnAccess,adOpenStatic,adLockOptimistic
With rstTemporary
  .MoveFirst
  Do
    rstPermanent.Find "ID = '" & !ID & "'"
    'Assumes unique character ID field in both tables, remove 's if numeric
    IF NOT rstPermanent.EOF Then
      'Compare each field
      For intField = 0 To .Fields.Count - 1
        IF rstPermanent.Fields(intField) <> .Fields(intField) Then
          msgbox "Field : " & .Fields(intField).Name & " Is Different" & vbLF & rstPermanent.Fields(intField).Value & " Is Now " & .Fields(intField).Value
        End If
      Next
    End If
    .MoveNext
  Loop Until .EOF
End With
rstTemporary.Close
rstPermanent.Close
cnnAccess.Close
Set rstTemporary = Nothing
Set rstPermanent = Nothing
Set cnnAccess = Nothing


This is just off the top of my head so it may need a little tweaking. It basically checks each record in the monthly table against the permanent one, then if it finds a record that matches it checks each field and displays the differences in a message box. You could change this to put it into a listbox or text box or whatever you like.

Ok, You've got a reference table and a transaction table. The field structures are common. You want to have address changes in the transaction table posted to the main table. You want to append items in the transaction table that are not found in the main table.

Ok, there are several ways to do this. Lets assume that you have a key field that you can use to match up. (You can't use the Address field as this may change)

Open a record set with the transaction table getting all of the items:

SQL = "SELECT * FROM TRANSACTIONS ORDER BY {keyfield}"
SET RS1 = DB.OpenRecordset(SQL)

then iterate thru the set comparing to the main table:

DO WHILE NOT RS1.EOF
   SQL = "SELECT * FROM MAIN WHERE {Keyfield}=" & RS1(keyfield)
   SET RS2 = DB.OpenRecordset(SQL)
   IF RS2.EOF THEN
      '
      ' Record is not found, insert new
      '
      SQL = "INSERT INTO MAIN FROM TRANSACTION WHERE {keyfield}=" & RS1(keyfield)
     DB.Execute SQL
   ELSE
      '
      ' Record already exists in master, check to see if address changed and
      ' and only update if so
      '
      IF UCase(Trim(RS1!Address)) <> UCase(Trim(RS2!Address))
          SQL = "UPDATE MAIN SET Address='" & rs1!address & "' where {keyfield}=" & rs1(keyfield)
          DB.Execute SQL
      end if
      '
   END IF
   '
   RS1.MoveNext   'Advance to next record in Transaction table
Loop

Anyway,  You should get the general idea.

M

One thing that needs a little clarification is what is the sequence of things here?  It sounds like you have some process that runs that updates the Archive table with changes and additions from the temporary table.  Then, you have an application that is viewing the data, presumably from the Archive table.  You want this application to know which fields have changed.  

How do you define changed?  A field might have changed 2 months ago, do you still want to show it as changed?  More likely, you'll want to show things that have changed this month.  If so, you'll need to have some sort of "Last Modified" time stamp for each field in the table (this is a lot of work).  And, when you're updating the Archive Table from the temp table, you're going to have to check each individual field, and set each updated field's timestamp (even more work).  Then, your application will have to look at the Last Modified timestamp, and highlight each field that has changed in the current month.  What are you planning to do with new records?  Highlight all of the fields?

From a GUI design perspective, I think that what you will end up with is what we call a Disco user interface.  This is a gui that keeps flashing different colors at you as you move through the data.  Not generally a good design, and a lot of work for you.

You might want to consider an approach more like Tim is suggesting above, which would be to run a query to display the differences between the New and the Archive tables before updating the Archive tables - and then generate a hard-copy report of the differences.  Then, update the Archive table.  If you want, you can have a single timestamp on each row that is a Last Modified Timestamp, and this you can easily update as you go.  Then, if necessary, you can add a feature in your application that selects just the records where the Last Modified Timestamp has a date in the current month.  So the users can see which records are either new or have been changed (they'll have to refer to the report to see what fields have changed)

Mark2150, Here is what I put into my code.  Is this what you were talking about?  The line IF UCase(Trim(RS1!Address)) <> UCase(Trim(RS2!Address))is showing up as an error.  Why?



Set dbDatabase = OpenDatabase("I:\P41File\P41File.mdb")
Set dbTable = dbDatabase.OpenRecordset("P41Import", dbOpenTable)
   
SQL = "SELECT * FROM TRANSACTIONS ORDER BY {SSN}"
Set RS1 = DB.OpenRecordset(SQL)

Do While Not RS1.EOF
   SQL = "SELECT * FROM MAIN WHERE {SSN}=" & RS1(SSN)
   Set RS2 = DB.OpenRecordset(SQL)
   If RS2.EOF Then

' Record is not found, insert new

   SQL = "INSERT INTO MAIN FROM TRANSACTION WHERE {SSN}=" & RS1(SSN)
     DB.Execute SQL
   Else
      ' Record already exists in master, check to see if address changed and
      ' and only update if so
      IF UCase(Trim(RS1!Address)) <> UCase(Trim(RS2!Address))
          SQL = "UPDATE MAIN SET Address='" & RS1!Address & "' where {keyfield}=" & RS1(keyfield)
          DB.Execute SQL
      End If
      '
   End If
   '
   RS1.MoveNext   'Advance to next record in Transaction table
Loop

Tim, I'm not an expert so explain to me if this is right.  What is intfield?  Thanks Mdougan,  I think I'll reconsider highlighting fields if a change occurs.


Dim cnnAccess As ADODB.Connection
Dim rstPermanent As ADODB.Recordset
Dim rstTemporary As ADODB.Recordset

Set cnnAccess = New ADODB.Connection
cnnAccess.ConnectionString = "I:\P41File\P41File.mdb"
cnnAccess.Open
Set rstTemporary = New ADODB.Recordset
rstTemporary.Open "SELECT * From P41Import2", cnnAccess, adOpenStatic, adLockReadOnly
rstPermanent.Open "SELECT * From P41Import", cnnAccess, adOpenStatic, adLockOptimistic
With rstTemporary
  .MoveFirst
  Do
    rstPermanent.Find "SSN = '" & !SSN & "'"
    'Assumes unique character ID field in both tables, remove 's if numeric
    If Not rstPermanent.EOF Then
      'Compare each field
      For intField = 0 To .Fields.Count - 1
        If rstPermanent.Fields(intField) <> .Fields(intField) Then
          MsgBox "Field : " & .Fields(intField).Name & " Is Different" & vbLf & rstPermanent.Fields(intField).Value & " Is Now " & .Fields(intField).Value
        End If
      Next
    End If
    .MoveNext
  Loop Until .EOF
End With
rstTemporary.Close
rstPermanent.Close
cnnAccess.Close
Set rstTemporary = Nothing
Set rstPermanent = Nothing
Set cnnAccess = Nothing

You may need to change it to:

UCASE(TRIM("" & RS!FIELD))

In case the data field is empty (null).

M

mark2150, I fixed the UCASE problem.  When I try to run the job, it gives me an "invalid or unqualified reference" error.  This occurs of the following line:

SQL = "SELECT * FROM MAIN WHERE {!SSN}=" & RS1(!SSN)

SQL = "SELECT * FROM MAIN WHERE {!SSN}=" & RS1(!SSN)

should be:

SQL = "SELECT * FROM MAIN WHERE [SSN]=" & RS1(!SSN)

so without the '!' in the query.
Also note that I've changed the '{}' used to enclose fieldnames to '[]'. I assume that '{}' also works since everyone in this thread uses it, but I've never seen it before, so I used the normal convention of square brackets. If curved brackets work, then only removing the '!' from the query should be enough.

Help!  When running the application, I'm only comparing the First record on the new table to the first record on the archive table.  It needs to search through the archive record for the SSN to see if that person exists.  Also getting error on following line with the following error:  Syntax error in INSERT INTO statement.
SQL = "INSERT INTO MAIN FROM TRANSACTION WHERE[SSN]=" & RS1!SSN

When you say "INSERT INTO MAIN" , What is MAIN?

You shouldn't use {} or []. They were placeholders in my example.

MAIN is the name of your main table - substitute what your table is called for MAIN.

SQL = "INSERT INTO MAIN FROM TRANSACTION WHERE SSN=" & RS1!SSN

TRANSACTION is also a placeholder for the transaction table. Your temporary table name should replace TRANSACTION.

M

The RS1.Movenext should iterate thru all the records in the transaction table.

M

Here is what I currently have:

Dim SQL                       As String
Dim RS1, RS2


Set dbDatabase = OpenDatabase("I:\P41File\P41File.mdb")
Set RS1 = dbDatabase.OpenRecordset("P41Import", dbOpenTable)

SQL = "SELECT * FROM P41Import2 ORDER BY !SSN"

Do While Not RS1.EOF
   SQL = "SELECT * FROM P41Import WHERE SSN=" & RS1!SSN
   Set RS2 = dbDatabase.OpenRecordset("P41Import2", dbOpenTable)

   If (RS2.EOF) Then
' Record is not found, insert new
         SQL = "INSERT INTO P41Import FROM P41Import2 WHERE !SSN=" & RS1!SSN
         dbDatabase.Execute SQL
   Else
' Record already exists in master, check to see if address changed and
' and only update if so

         If UCase(Trim("" & RS1!Add1)) <> UCase("" & Trim(RS2!Add1)) Then

             SQL = "UPDATE P41Import SET Add1='" & RS1!Add1 & "' where SSN=" & RS1!SSN
             dbDatabase.Execute SQL
         End If
   End If

   RS1.MoveNext   'Advance to next record in Transaction table
Loop


What's wrong with it.  It gives me problems on the dbDatabase statements.

Dim db as database
dim rs1 as recordset
dim rs2 as recordset

Set db = DBEngine.Workspaces(0).OpenDatabase(...)

You'll also need to have a data control somewhere on your form or to manually include the reference to the database component.

M

mark2150, it's not working.  I also don't see any reason why I must have a data control somewhere on my form.  Here is the code I have at the moment:

Dim SQL As Integer
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set db = DBEngine.Workspaces(0).OpenDatabase("I:\P41File\P41File.mdb")

SQL = "SELECT * FROM TRANSACTIONS ORDER BY SSN"
Set rs1 = db.OpenRecordset(SQL)

Do While Not rs1.EOF
   SQL = "SELECT * FROM MAIN WHERE SSN=" & rs1!SSN
   Set rs2 = db.OpenRecordset(SQL)
   If rs2.EOF Then
      '
      ' Record is not found, insert new
      '
      SQL = "INSERT INTO MAIN FROM TRANSACTION WHERE SSN=" & rs1!SSN
     db.Execute SQL
   Else
      '
      ' Record already exists in master, check to see if address changed and
      ' and only update if so
      '
      If UCase(Trim("" & rs1!Add1)) <> UCase(Trim("" & rs2!Add1)) Then
          SQL = "UPDATE MAIN SET Address='" & rs1!Address & "' where SSN=" & rs1!SSN
          db.Execute SQL
      End If
      '
   End If
   '
   rs1.MoveNext   'Advance to next record in Transaction table
Loop


I am getting an error on             SQL = "SELECT * FROM TRANSACTIONS ORDER BY SSN"

The error is "TYPE MISMATCH"

SQL is a *STRING* NOT an INTEGER as you have it dimensioned.

You need the data control on your form to tell the compiler to include data objects in the project. You can also add the objects to the project references manually. The easies way is to include the references is to drop a control onto your form and make it's .visible property false. If you don't have the controls references you get an "undefined user type" error on the DIM db AS DATABASE statement or any other statement referencing databases.

M

Ok, got past that hurdle.  Now I am getting an error on the following line:
   Set rs2 = db.OpenRecordset(SQL)

Error is: "Data type mismatch in criteria expression"

Thanks for the help

The code listed here is OK for the one part of your problem, updating the archive table with the month table, but it's not doing everything in your original list of requirements.  That said, even this code is not very efficient.  It will be fine, I'm sure if you're tables only have a few hundred items, but it's going to bog down if you have more.

A faster way would be to left join the two tables on SSN.  It would look something like this:

' Note, you had this as an Integer
' when it should be a string
'Dim SQL As Integer
Dim SQL As String
Dim db As Database
Dim rs1 As Recordset
Const sSingleQuote = "'"

' Open the db
Set db = DBEngine.Workspaces(0).OpenDatabase("I:\P41File\P41File.mdb")

' String the Join Syntax
SQL = "Select P4Month.SSN as NewSSN,"
SQL = SQL & " P4Month.Address as NewAddress,"
SQL = SQL & " P4Month.City as NewCity,"
SQL = SQL & " P4Archive.SSN as OldSSN,"
SQL = SQL & " P4Archive.Address as OldAddress,"
SQL = SQL & " P4Archive.City as OldCity"
' put all the rest of the fields here too
SQL = SQL & " from P4Month Left Join P4Archive on P4Month.SSN = P4Archive.SSN"

' Open the combined recordset
Set rs1 = db.OpenRecordset(SQL)

'Loop through each record

Do While Not rs1.EOF
' if the old SSN is Null, then its a new record
   if IsNull(rs1("OldSSN")) then
      SQL = "Insert Into P4Archive ("
      SQL = SQL & " SSN,"
      SQL = SQL & " ADDRESS,"
      SQL = SQL & " CITY) VALUES ("
      SQL = SQL & " " & sSingleQuote & rs1("NewSSN") & sSingleQuote
      SQL = SQL & " " & sSingleQuote & rs1("NewAddress") & sSingleQuote
      SQL = SQL & " " & sSingleQuote & rs1("NewCity") & sSingleQuote & ")"
   Else
'There are a few gotchas, you might want
'You should also be updating your timestamp somewhere here
'You need to make sure that the fields are
'formatted properly for their type.  
'Strings are wrapped in Single quotes
'You have to double any single quotes that appear in the strings like O'Mally Ln. becomes O''Mally Ln.
'Dates are wrapped in # signs
      SQL = ""
      if StrComp(rs1("OLDADDRESS") & "", rs1("NEWADDRESS") & "", vbTextCompare) <> 0 then
          SQL = SQL & " P4Archive.Address = " & sSingleQuote & rs1("NewAddress") & sSingleQuote
       End if
      if StrComp(rs1("OLDCity") & "", rs1("NEWCity") & "", vbTextCompare) <> 0 then
' put a comma between fields if necessary
         if SQL <> "" then SQL = SQL & ","          
         SQL = SQL & " P4Archive.City = " & sSingleQuote & rs1("NewCity") & sSingleQuote
       End if
' if all fields were equal, SQL will be ""
       if SQL <> "" then
          SQL = "UPDATE P4Archive Set " & SQL
       End if
   End If

   if SQL <> "" Then
   db.Execute SQL

   rs1.MoveNext
Loop

I think this will be significantly faster than the other suggestions.

To make it complete, change all the double double-quote representations of the empty string {""} to the vbNullString constant. It will save you some memory (when compiling optimized for speed) and is generally considered better programming practice, so lines like:

if StrComp(rs1("OLDCity") & "", rs1("NEWCity") & "", vbTextCompare) <> 0 then
and
SQL = "" 

should be changed to

if StrComp(rs1("OLDCity") & vbNullString, rs1("NEWCity") & vbNullString, vbTextCompare) <> 0 then
and
SQL = vbNullString

MDOUGAR, I am getting the error: "Type Mismatch" on the following line:

Set rs1 = db.OpenRecordset(SQL)


Here is the code I used:

Dim SQL As String
Dim db As Database
Dim rs1 As Recordset
Const sSingleQuote = "'"

' Open the db
Set db = DBEngine.Workspaces(0).OpenDatabase("I:\P41File\P41File.mdb")

' String the Join Syntax
SQL = "Select TRANSACTIONS.SSN as NewSSN,"
SQL = SQL & " TRANSACTIONS.Add1 as NewAddress,"
SQL = SQL & " TRANSACTIONS.City as NewCity,"
SQL = SQL & " MAIN.SSN as OldSSN,"
SQL = SQL & " MAIN.Add1 as OldAddress,"
SQL = SQL & " MAIN.City as OldCity"
' put all the rest of the fields here too
SQL = SQL & " from TRANSACTIONS Left Join MAIN on TRANSACTIONS.SSN = MAIN.SSN"

' Open the combined recordset
Set rs1 = db.OpenRecordset(SQL)

'Loop through each record

Do While Not rs1.EOF
' if the old SSN is Null, then its a new record
   If IsNull(rs1("OldSSN")) Then
      SQL = "Insert Into MAIN ("
      SQL = SQL & " SSN,"
      SQL = SQL & " ADD1,"
      SQL = SQL & " CITY) VALUES ("
      SQL = SQL & " " & sSingleQuote & rs1("NewSSN") & sSingleQuote
      SQL = SQL & " " & sSingleQuote & rs1("NewAddress") & sSingleQuote
      SQL = SQL & " " & sSingleQuote & rs1("NewCity") & sSingleQuote & ")"
   Else
'There are a few gotchas, you might want
'You should also be updating your timestamp somewhere here
'You need to make sure that the fields are
'formatted properly for their type.
'Strings are wrapped in Single quotes
'You have to double any single quotes that appear in the strings like O'Mally Ln. becomes O''Mally Ln.
'Dates are wrapped in # signs
      SQL = vbNullString
      If StrComp(rs1("OLDADDRESS") & vbNullString, rs1("NEWADDRESS") & vbNullString, vbTextCompare) <> 0 Then
          SQL = SQL & " MAIN.Add1 = " & sSingleQuote & rs1("NewAddress") & sSingleQuote
      End If
      If StrComp(rs1("OLDCity") & vbNullString, rs1("NEWCity") & vbNullString, vbTextCompare) <> 0 Then
' put a comma between fields if necessary
         If SQL <> "" Then SQL = SQL & ","
            SQL = SQL & " MAIN.City = " & sSingleQuote & rs1("NewCity") & sSingleQuote
         End If
' if all fields were equal, SQL will be ""
       If SQL <> "" Then
          SQL = "UPDATE MAIN Set " & SQL
       End If
      End If

   If SQL <> "" Then
      db.Execute SQL
   End If
   rs1.MoveNext
Loop

Check your references. It might be that you've referenced both 'Microsoft DAO x.xx Object Library' as well as 'Microsoft ActiveX Data Objects x.x Library'. In that case, VB can take the Recordset object definition from the ADO library, while you are using the DAO library. Either remove the reference to the ADO library, or if you need that one (you shouldn't), precede the Recordset typename with the library name, so change:

Dim db As Database
Dim rs1 As Recordset

to:

Dim db As DAO.Database
Dim rs1 As DAO.Recordset

If you do need both of the libraries, you'd better change all the data access code to use either DAO *or* ADO, but *not* both. That will save you about, oh well, let's say 6-16 MB of memory footprint.

Ok, that worked.  But now I have another problem.  I setup a test case that the first record in the monthly record had a different address that the first record in the archive address.  When I ran the job, it not only changed the first record's addres in the archive, it changed all of the addresses in the archive.  Now all of the records have the same address.

Change:

SQL = SQL & " from TRANSACTIONS Left Join MAIN on TRANSACTIONS.SSN = MAIN.SSN"

to:

SQL = SQL & " from TRANSACTIONS Left Join MAIN on TRANSACTIONS.SSN = MAIN.SSN and not ISNULL(TRANSACTIONS.SSN)"

I think that should work (depending on how SQL-92 compliant Access is ;-).

Also, check your case in expressions like 'rs1("OLDADDRESS")'. I'm not sure, but these might be case-sensitive. Make sure the case is the same as the one used in the query.

Sorry, I see now that it probably won't work (or does it?). Give me some time and I'll work it out.

Ok, thanks for your time Jeremy.  It's not working.  Now I'm getting the error: "Join expression not supported." This is happening on the following line:
Set rs1 = db.OpenRecordset(SQL)


Here is the entire code:


Dim SQL As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset

Const sSingleQuote = "'"

' Open the db
Set db = DBEngine.Workspaces(0).OpenDatabase("I:\P41File\P41File.mdb")

' String the Join Syntax
SQL = "Select TRANSACTIONS.SSN as NewSSN,"
SQL = SQL & " TRANSACTIONS.Add1 as NewAddress,"
SQL = SQL & " TRANSACTIONS.City as NewCity,"
SQL = SQL & " MAIN.SSN as OldSSN,"
SQL = SQL & " MAIN.Add1 as OldAddress,"
SQL = SQL & " MAIN.City as OldCity"
' put all the rest of the fields here too

SQL = SQL & " from TRANSACTIONS Left Join MAIN on TRANSACTIONS.SSN = MAIN.SSN and not ISNULL(TRANSACTIONS.SSN)"

' Open the combined recordset
Set rs1 = db.OpenRecordset(SQL)

'Loop through each record

Do While Not rs1.EOF
' if the old SSN is Null, then its a new record
   If IsNull(rs1("OldSSN")) Then
      SQL = "Insert Into MAIN ("
      SQL = SQL & " SSN,"
      SQL = SQL & " ADD1,"
      SQL = SQL & " CITY) VALUES ("
      SQL = SQL & " " & sSingleQuote & rs1("NewSSN") & sSingleQuote
      SQL = SQL & " " & sSingleQuote & rs1("NewAddress") & sSingleQuote
      SQL = SQL & " " & sSingleQuote & rs1("NewCity") & sSingleQuote & ")"
   Else
'There are a few gotchas, you might want
'You should also be updating your timestamp somewhere here
'You need to make sure that the fields are
'formatted properly for their type.
'Strings are wrapped in Single quotes
'You have to double any single quotes that appear in the strings like O'Mally Ln. becomes O''Mally Ln.
'Dates are wrapped in # signs
      SQL = vbNullString
      If StrComp(rs1("OldAddress") & vbNullString, rs1("NewAddress") & vbNullString, vbTextCompare) <> 0 Then
          SQL = SQL & " MAIN.Add1 = " & sSingleQuote & rs1("NewAddress") & sSingleQuote
      End If
      If StrComp(rs1("OldCity") & vbNullString, rs1("NewCity") & vbNullString, vbTextCompare) <> 0 Then
' put a comma between fields if necessary
         If SQL <> "" Then SQL = SQL & ","
            SQL = SQL & " MAIN.City = " & sSingleQuote & rs1("NewCity") & sSingleQuote
         End If
' if all fields were equal, SQL will be ""
       If SQL <> "" Then
          SQL = "UPDATE MAIN Set " & SQL
       End If
      End If

   If SQL <> "" Then
      db.Execute SQL
   End If
   rs1.MoveNext
Loop

'This should be better. I've minimized the looping in VB
' by getting only the records that need updating, and
' adding the new records with one single action-query


Dim SQL As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset

' Open the db
Set db = DBEngine.Workspaces(0).OpenDatabase("I:\P41File\P41File.mdb")

' Get fields that need updating (and *only* those)
SQL = "Select SSN, Add1, City, {and all other fields} "
SQL = SQL & "FROM TRANSACTIONS "
SQL = SQL & "WHERE SSN IN (SELECT SSN FROM MAIN)"

' Open the recordset
Set rs1 = db.OpenRecordset(SQL)

If Not (rs1.EOF And rs1.BOF) Then rs1.MoveFirst

' Loop through each record and update it
Do While Not rs1.EOF
    SQL = "UPDATE MAIN SET Add1 = '" & rs1!Add1 & "', "
    SQL = SQL & "City = '" & rs1!City & "', "
    SQL = SQL & "{OtherField} = '" & rs1!{OtherField} & "', "
    SQL = SQL & "{YetAnotherField} = '" & rs1!{YetAnotherField} & "' "
    SQL = SQL & "WHERE SSN = " & CStr(rs1!SSN)
'I assume the SSN field is numeric, if it is alfa, replace the above line with:
'   SQL = SQL & "WHERE SSN = '" & rs1!SSN & "'"
    db.Execute SQL
    rs1.MoveNext
Loop

' Get rid of the recordset, won't need it anymore
On Error Resume Next
rs1.Close
Set rs1 = Nothing
On Error GoTo 0

' Now insert all the new records. This can be done with one action-query
SQL = "INSERT INTO MAIN SELECT * FROM TRANSACTIONS "
SQL = SQL & "WHERE TRANSACTIONS.SSN NOT IN (SELECT SSN FROM MAIN)"

db.Execute SQL, dbFailOnError

' Clean up the db-object here if you don't need it anymore
On Error Resume Next
db.Close
Set db = Nothing
On Error GoTo 0

Set db = Nothing

Jeremy, I'm getting the following error:
"Syntax error in update statement" on the following line: db.Execute SQL

Are you sure you didn't forget the lines:

SQL = "INSERT INTO MAIN SELECT * FROM TRANSACTIONS " 
SQL = SQL & "WHERE TRANSACTIONS.SSN NOT IN (SELECT SSN FROM MAIN)"

before the db.Execute?

Put a 'Debug.Print SQL' statement just before the db.Execute line and tell me what it says in your immediate window.

Here is where the code is going down:

Do While Not rs1.EOF
    SQL = "UPDATE MAIN SET Add1 = '" & rs1!Add1 & "', "
    'SQL = SQL & "Add2 = '" & rs1!Add2 & "', "
    SQL = SQL & "City = '" & rs1!City & "', "
    'SQL = SQL & "State = '" & rs1!State & "', "
    'SQL = SQL & "Zip = '" & rs1!Zip & "' "
    SQL = SQL & "WHERE SSN = " & CStr(rs1!SSN)
'I assume the SSN field is numeric, if it is alfa, replace the above line with:
'   SQL = SQL & "WHERE SSN = '" & rs1!SSN & "'"
    Debug.Print SQL
    db.Execute SQL  ***Going down here***


In the immediate window, I get the following message:

UPDATE MAIN SET Add1 = '2000 G  PALM TERRACE', City = 'HOUSTON', WHERE SSN = 467655657

Change:

SQL = SQL & "City = '" & rs1!City & "', "

to:

SQL = SQL & "City = '" & rs1!City & "' "

There shouldn't be a comma {,} after the last field just before where.

   

I'm sorry to keep bothering you, but now I have another problem.

Getting the following error message:
Data type mismatch in criteria expression"

on the following line: db.Execute SQL

Here is the immediate window data:
UPDATE MAIN SET Add1 = '2000 G  PALM TERRACE', First = 'MONETTA', Last = 'ALLEN', DOB = '19780220', Add2 = '', City = 'HOUSTON', State = 'TX', Zip = '775500000' WHERE SSN = 467655657

Here is the code as of now:

Dim SQL As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset

' Open the db
Set db = DBEngine.Workspaces(0).OpenDatabase("I:\P41File\P41File.mdb")

' Get fields that need updating (and *only* those)
SQL = "Select SSN, Add1, Add2, City, State, Zip, First, Last, DOB "
SQL = SQL & "FROM TRANSACTIONS "
SQL = SQL & "WHERE SSN IN (SELECT SSN FROM MAIN)"

' Open the recordset
Set rs1 = db.OpenRecordset(SQL)

If Not (rs1.EOF And rs1.BOF) Then rs1.MoveFirst

' Loop through each record and update it
Do While Not rs1.EOF
    SQL = "UPDATE MAIN SET Add1 = '" & rs1!Add1 & "', "
    SQL = SQL & "First = '" & rs1!First & "', "
    SQL = SQL & "Last = '" & rs1!Last & "', "
    SQL = SQL & "DOB = '" & rs1!DOB & "', "
    SQL = SQL & "Add2 = '" & rs1!Add2 & "', "
    SQL = SQL & "City = '" & rs1!City & "', "
    SQL = SQL & "State = '" & rs1!State & "', "
    SQL = SQL & "Zip = '" & rs1!Zip & "' "
    SQL = SQL & "WHERE SSN = " & CStr(rs1!SSN)
'I assume the SSN field is numeric, if it is alfa, replace the above line with:
'   SQL = SQL & "WHERE SSN = '" & rs1!SSN & "'"
    Debug.Print SQL
    db.Execute SQL
    rs1.MoveNext
Loop

' Get rid of the recordset, won't need it anymore
On Error Resume Next
rs1.Close
Set rs1 = Nothing
On Error GoTo 0

' Now insert all the new records. This can be done with one action-query
SQL = "INSERT INTO MAIN SELECT * FROM TRANSACTIONS "
SQL = SQL & "WHERE TRANSACTIONS.SSN NOT IN (SELECT SSN FROM MAIN)"

db.Execute SQL, dbFailOnError

' Clean up the db-object here if you don't need it anymore
On Error Resume Next
db.Close
Set db = Nothing
On Error GoTo 0

Set db = Nothing

When you use SET, as in:

SET Add1 = '2000 G  PALM TERRACE'

you'll have to make sure the datatypes match. If it's a text field (alphanumeric), the syntax is:

SET Field = 'Value'
as in:
SET Add1 = '2000 G  PALM TERRACE'

but if it is a numeric field, the syntax is:

Set Field = Value
as in:
Set DOB = 19780220
(So without the single quotes, I'm just guessing here that your DOB field might be numeric).

Check in the Access table design which fields are numeric, and remove the single quotes around those values. Your result will probably be something like:

    SQL = "UPDATE MAIN SET Add1 = '" & rs1!Add1 & "', " 
    SQL = SQL & "First = '" & rs1!First & "', " 
    SQL = SQL & "Last = '" & rs1!Last & "', " 
    SQL = SQL & "DOB = " & CStr(rs1!DOB) & ", " 
    SQL = SQL & "Add2 = '" & rs1!Add2 & "', " 
    SQL = SQL & "City = '" & rs1!City & "', " 
    SQL = SQL & "State = '" & rs1!State & "', " 
    SQL = SQL & "Zip = " & CStr(rs1!Zip) & " " 
    SQL = SQL & "WHERE SSN = " & CStr(rs1!SSN)

(assuming that both the DOB and Zip fields are numeric).

The CStr conversions are not really necessairy, since VB does this for you automagically behind the scenes, but it helps you see what happens when you look back at the code.

You might also want to make sure that this runs correctly if a name contains a single quote (the Last-field for instance could be "d'Argo". In order to do this you might want to make a function QuoteName, something like:

Private Function QuoteName(Name As String) As String
    QuoteName = "'" & Replace(Name, "'", "''") & "'"
End Function

You can then do this:

    SQL = "UPDATE MAIN SET Add1 = " & QuoteName(rs1!Add1) & ", " 
    SQL = SQL & "First = " & QuoteName(rs1!First) & ", " 
    SQL = SQL & "Last = " & QuoteName(rs1!Last) & ", " 
    SQL = SQL & "DOB = " & CStr(rs1!DOB) & ", " 
    SQL = SQL & "Add2 = " & QuoteName(rs1!Add2) & ", " 
    SQL = SQL & "City = " & QuoteName(rs1!City) & ", " 
    SQL = SQL & "State = " & QuoteName(rs1!State) & ", " 
    SQL = SQL & "Zip = " & CStr(rs1!Zip) & " " 
    SQL = SQL & "WHERE SSN = " & CStr(rs1!SSN)

In other words, you can simply forget about all those single quotes and just run all alphanumeric fields through the QuoteName function before concatenating them to your SQL command.
A string like {2000 G  PALM TERRACE} would become {'2000 G  PALM TERRACE'}, and a string like {d'Argo} would become {'d''Argo'}.
The double single-quotes means that it is not a closing quote of the string, but that one single quote is contained within the string.

Otherwise, with a name like d'Argo the resulting SQL command would be something like:

UPDATE MAIN SET Add1 = '2000 G  PALM TERRACE', First = 'MONETTA', Last = 'D'ARGO', DOB = 19780220, Add2 = '', City = 'HOUSTON', State = 'TX', Zip = 775500000 WHERE SSN = 467655657

which would produce a syntax error since the {'} after the {D} from {D'ARGO} would be interpreted as a closing quote. With the QuoteName Function, the command would become:

UPDATE MAIN SET Add1 = '2000 G  PALM TERRACE', First = 'MONETTA', Last = 'D''ARGO', DOB = 19780220, Add2 = '', City = 'HOUSTON', State = 'TX', Zip = 775500000 WHERE SSN = 467655657

in which case the {''} in {D''ARGO} says: "This is not a closing quote, but a quote that belongs in the name".

WoooHooo, it works.  Ok, now how do I add a record that isn't currently in the archive.  Don't worry, your getting all the points.  Your the only person that stuck it out with me and got this thing to work.

(You better better paste that QuoteName function in your VB editor befor looking at it. Even I am losing count of the quotes now.)
<grin>

Cheers! That's what we're here for :)

What do you mean with:
"now how do I add a record that isn't currently in the archive"??

In the new file, it contains updates to the archive record which consist of changed and additions.  I will get change of information ( which we just finished taking care of) and I will also be getting new records that aren't currely on the archive.  I need to add these new records to the archive for future reference.  You had this in your code and I figured it might have something to do with adding new records into the archive if the SSN isn't found in the archive.

' Now insert all the new records. This can be done with one action-query
SQL = "INSERT INTO MAIN SELECT * FROM TRANSACTIONS "
SQL = SQL & "WHERE TRANSACTIONS.SSN NOT IN (SELECT SSN FROM MAIN)"

db.Execute SQL, dbFailOnError

That is exactly the piece of code that should take care of the additions. It checks the SSN fields in TRANSACTIONS, and inserts the records where the SSN field doesn't exist in the MAIN table yet.

So after you run the complete update, you should empty the TRANSACTIONS table ('DELETE * FROM TRANSACTIONS') so that the records added to the MAIN table don't exist in TRANSACTIONS anymore when you do the next update. I assumed that was what you meant. If not, explain me what you want exactly.

I'm going off-line now, do some real-world-socializing :), so any further explanations will have to wait until tomorrow 8:00 AM GMT when I've had enough of the Real World.

See you then.

Ok, thank you.  I'll leave you a note of what exactly I need to complete this job.  I assumed that if the record was not updated in the archive and the SSN in the Update file was not found on the archive file that it would write the new record down at the bottom of the archive file.  The update file consists of both update records and new records.  If I do a "DELETE * FROM TRANSACTIONS" after update, it will delete not only the update records but also the new records that have not been appended to the archive file.  I figured that what we could do is update the records in the first step and then write out the new record in the second step.  For a test case, I even deleted the update records and just left in the new records.  I ran the application and the new record was not added to the archive.  See you tomorrow.  Thanks again.  

Of course you need to do the delete statement only *after* this code has executed:

SQL = "INSERT INTO MAIN SELECT * FROM TRANSACTIONS " 
SQL = SQL & "WHERE TRANSACTIONS.SSN NOT IN (SELECT SSN FROM MAIN)"
db.Execute SQL, dbFailOnError

When I was talking about doing a 'DELETE *' after the 'complete update', I meant the whole process, so updating of all existing records, as well as inserting the new ones.

I ran this quesry against a test-table, and it works fine. It adds those records to the first table that are in the second table but not in the first one yet.
So after you've ran the VB update-loop, as well as the 'INSERT INTO' query, you archive table (MAIN) should contain all the records from the updates table (TRANSACTIONS), either updated or added. After that you won't need the TRANSACTIONS table anymore until you need to do another update again (I assume this process needs to be done periodically).

Jeremy, I did a test on a new record that wasn't currently in the archive table and the updates worked just fine.  The new record does not show up in the archive table eventhough it is in the new table.  I made sure that the number I am keying off of is not already in the archive so it doesn't consider it an update.

Try this in Access:
Create a table, called 'MAIN', with two fields, one autonumber field, call it 'RecID', and one text field, call it 'RecValue'.
Now insert some values in the 'RecValue' field, about 5 records should be enough.
Copy the table, both structure as well as data, and name the new table 'TRANSACTIONS'
Now delete some records from the MAIN table, about three or so.
Now create a query with the following SQL-code:

INSERT INTO MAIN
 SELECT * FROM TRANSACTIONS
 WHERE TRANSACTIONS.RecID NOT IN
  (SELECT RecID FROM MAIN);

Save the query and run it.
If I do this, the MAIN table gets updated with the records from the TRANSACTIONS Table that aren't in the MAIN table yet. In other words, it works perfectly in my Access 97 version.

Ok, that worked in Access97.  But, when running from my application it doesn't work.  What could be the problem?

Here is the code:

SQL = "INSERT INTO MAIN SELECT * FROM TRANSACTIONS "
SQL = SQL & "WHERE TRANSACTIONS.SSN NOT IN (SELECT SSN FROM MAIN)"

   Debug.Print SQL
   db.Execute SQL, dbFailOnError

Here is what I am getting in the Immediate Window:

INSERT INTO MAIN SELECT * FROM TRANSACTIONS WHERE TRANSACTIONS.SSN NOT IN (SELECT SSN FROM MAIN)

And that's it.

JD, I also modified the Query to work on my tables and it didn't insert the new record.

I did a Select * From Transactions and got the new record.

I did a SELECT SSN FROM MAIN and got the new record.  


But, when I try to run the entire thing I get nothing.

Ok, here is a question for you.  I deleted all the records out of the archive table and then ran the query.  It inserted the new record in the archive record without any problems.  But, if you have data in the arcive table, it won't enter the new data into the archive table.  I tried everything you said to do and it doesn't seem to work.  I'll go ahead and give you the points.  But, if you could keep helping me on this problem, I would really appreciate it.

JD, figured it out.  The archive record had a footer record in it.  The footer record just gave a total line count of the original data.  I deleted that footer record and the query ran fine.  Thanks for the help.

Congrats, we worked it out!