Solved

Comparing tables in Access and showing in VB

Posted on 2000-04-12
48
187 Views
Last Modified: 2010-05-02
Here is my delima.  I currently have one table in Access that is a permanent table.  I have a second table that is created once a month with new or updated records.  Both of these tables have the same fields within them.  What I would like to do is if there is an address change in the new table, I can compare the new table to the archive table and make the change to the address in the archive table.  This new table would also include new records that would be appended to the archive table.  Also, in my application I would like to be able to show the changed field with the text box in my application and show that text box as a highlighted box showing that there was a change.  Please be specific.  If you need more detail to answer this question, let me know.  I'll do my best to explain.  Thanks.  
0
Comment
Question by:jwellis
  • 21
  • 18
  • 6
  • +2
48 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 2708242
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.
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2708277
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
0
 
LVL 18

Expert Comment

by:mdougan
ID: 2708380
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)
0
 

Author Comment

by:jwellis
ID: 2708399
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
0
 

Author Comment

by:jwellis
ID: 2708446
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
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2708504
You may need to change it to:

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

In case the data field is empty (null).

M
0
 

Author Comment

by:jwellis
ID: 2708540
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)
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2708579
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.
0
 

Author Comment

by:jwellis
ID: 2708652
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?
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2708698
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
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2708701
The RS1.Movenext should iterate thru all the records in the transaction table.

M
0
 

Author Comment

by:jwellis
ID: 2708771
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.
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2708928
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
0
 

Author Comment

by:jwellis
ID: 2709209
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"
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2709246
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
0
 

Author Comment

by:jwellis
ID: 2709297
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
0
 
LVL 18

Expert Comment

by:mdougan
ID: 2710128
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.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2710964
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
0
 

Author Comment

by:jwellis
ID: 2711714
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
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2711756
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.

0
 

Author Comment

by:jwellis
ID: 2711775
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.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2711818
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.
0
 
LVL 4

Expert Comment

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

Author Comment

by:jwellis
ID: 2711868
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2711934
'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
0
 

Author Comment

by:jwellis
ID: 2712005
Jeremy, I'm getting the following error:
"Syntax error in update statement" on the following line: db.Execute SQL
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2712057
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.

0
 

Author Comment

by:jwellis
ID: 2712087
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
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2712107
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.

   
0
 

Author Comment

by:jwellis
ID: 2712149
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
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2712201
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.

0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2712247
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".

0
 

Author Comment

by:jwellis
ID: 2712251
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.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2712263
(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>
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2712283
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"??
0
 

Author Comment

by:jwellis
ID: 2712327
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
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2712417
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.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2712435
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.
0
 

Author Comment

by:jwellis
ID: 2712479
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.  
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2715341
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).
0
 

Author Comment

by:jwellis
ID: 2715879
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.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2716346
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.
0
 

Author Comment

by:jwellis
ID: 2716421
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.
0
 

Author Comment

by:jwellis
ID: 2716517
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.
0
 
LVL 4

Accepted Solution

by:
Jeremy_D earned 200 total points
ID: 2716621
Two things you can try (I'm not sure why it doesn't work, so just try them and tell me the results)

1.---------------------------------
In stead of the line:

db.Execute SQL, dbFailOnError

try this:

'Create a QueryDef object with the INSERT command
Dim qdfTemp As DAO.QueryDef
Set qdfTemp = db.CreateQueryDef("", SQL)
'Execute the action-query
qdfTemp.Execute
'And clean up the rubbish
qdfTemp.Close
Set qdfTemp = Nothing


2.---------------------------------
Change the INSERT query to include all the fieldnames in stead of using the '*', something like:

INSERT INTO MAIN (SSN, Add1, First, Last, DOB, Add2, City, State, Zip)
 SELECT TR.SSN, TR.Add1, TR.First, TR.Last, TR.DOB, TR.Add2, TR.City, TR.State, TR.Zip
  FROM TRANSACTIONS As TR
   WHERE TR.SSN NOT IN
    (SELECT SSN FROM MAIN)


If both of those don't work, you can also try this (maybe even before you try the other solutions):
Run only the select part of the query against your database (from Access), so:

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

This query should give all the records that need to be inserted in the MAIN table. If it doesn't return any records, the problem is probably not DAO or VB related, but there must be something wrong with your database design. Could be that you have different datatypes in the MAIN table then those in the TRANSACTIONS table, or maybe a problem with an autonumbering field.
0
 

Author Comment

by:jwellis
ID: 2717003
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.
0
 

Author Comment

by:jwellis
ID: 2717096
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.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2721843
Congrats, we worked it out!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now