Solved

Compare Microsoft Access tables row by rows to each other

Posted on 2015-01-27
20
193 Views
Last Modified: 2016-02-11
I would like to write basic code that will compare multiple access tables.   I receive a file each day with adds, updates and deletes.  I need to compare the input file to table that already exist for changes and deletes.   Then I need to output the records to a staging table.
BPPR-TABLES-UPDATES.docx
testlink.accdb
0
Comment
Question by:centralmike
  • 9
  • 7
  • 2
  • +1
20 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40573743
Why reinvent the wheel?
http://www.fmsinc.com/microsoftaccess/difference/properties-fields/tables.asp
;-)
I use this utility all the time when testing designs, ...or verifying backups...

JeffCoachman
0
 

Author Comment

by:centralmike
ID: 40573835
I need learn how to write it myself.  My company doesn't allow me download software.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40573941
The suggested software isn't free.  It is a real product and you have to pay for it.  $199 for a single seat license.  Why would your employer prefer to pay you to develop something they can purchase, probably for a lot less money?  There is absolutely no way you could develop anything even close to the FMS product for $199!!!
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 84
ID: 40574192
I don't think you're referring to changes in the Structure - is that correct?

Instead you want to examine incoming data and determine if anything has changed? If so, then how would you match up the incoming lines with your existing lines? Is there some way to identify those in the incoming data?
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40574334
Scott, the FMS software does structure AND data.  Both sets of data would need to be sorted on all columns.  Then you can match row by row.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40574380
My company doesn't allow me download software.
To be clear, ...this product is not one of those seedy freeware "Download" utilities.
It is a product you buy.
It is from a respected company, that is the Premier provider of Access development tools.

So is the issue here that you cannot literally "download" any programs, ...or is the real issue that your company does not allow you to "install" other programs.

FMS (when I purchased it) mailed me a CD ROM.

What you are asking for here can certainly be done, ...and perhaps an expert here will be willing to assist.
My post was simply a suggestion that if a reliable tool exists, ...buy it.

If your company's policy is "Build" rather than "Buy" then that is something you will have to weigh the pros and cons of.

JeffCoachman
0
 

Author Comment

by:centralmike
ID: 40574523
Good evening Scott, The structure does not change.  If the record does not already exist in the table it is a new record. It has a indicator of "A".  If the record is something that change you get two records.  The first record being the record that already exist with change indicator of CF = change from.  The second record with a indicator =  CT change to. The last indicator = "D" delete the record. I am just trying to output all records into a single table.  I didnt think it would be that complicated that I needed to buy additional software.  I thought this could be coded in basic syntax.
0
 
LVL 84
ID: 40574850
the FMS software does structure AND data.
Right - I"m aware of that, and use FMS Detective regularly in my work. My point was - this sounds more like a typical Import than anything else, and we regularly provide help with importing data. If the author were trying to modify or compare structures, then I could see the need for a 3rd party utility, but if this is a typical import ...

centralmike:

So you have 3 "conditions" (Add, Delete, and Update). You can use SQL or Recordsets to do any of them.

To add a record using SQL:

Currentdb.Execute "INSERT INTO YourLiveTable(Col1, Col2, Col3, etc) SELECT Col1, Col2, Col3, etc FROM YourStagingTable WHERE Indicator='A""

To Delete a record:

Currentdb.Execute "DELETE FROM YourLiveTable WHERE SomeIDField IN (SELECT SomeIDField FROM YourStagingTable WHERE Indicator='D')"

Updates are a bit more tricky, but essentially you would refer to the "CT" record, I'd think. Updates are often easier done with a Recordset:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM YourStagingTable WHERE Indicator='CT'")

Do Until rst.EOF
  Dim rst2 As DAO.Recordset
  Set rst2 = Currentdb.OpenRecordset("SELECT * FROM YourLiveTable WHERE SomeIDField=" & rst("IDField")
  If Not (rst2.EOF and rst2.BOF) Then
    rst2.Edit
    rst2("Col1") = rst("Col1")
    rst2("Col2") = rst("Col2")
    etc etc
    rst2.Update
  End If
Loop

Of course, the Delete and Update methods assume you have some way of determining which incoming record is related to an existing record. If you don't, then you can't do this.
0
 

Author Comment

by:centralmike
ID: 40584273
Running the update module.  Cant get past the error CODE #3061#  listed below.  Tried many different options on the

"AR_POL_ACCT_NUM"
[AR_POL_ACCT_NUM]
'AR_POL_ACCT_NUM'

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tbl_input WHERE type_ind ='CT'")

Do Until rst.EOF

Error code 3061
  Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM tbl_output WHERE AR_POL_ACCT_NUM = " & rst("AR_POL_ACCT_NUM"))

  If Not (rst2.EOF And rst2.BOF) Then
    rst2.Edit
    rst2("SI_PRODUCTION_NUM") = rst("SI_PRODUCTION_NUM")
    rst2("CO_CMPY_CDE") = rst("CO_CMPY_CDE")
    rst2("AR_POL_ACCT_NUM") = rst("AR_POL_ACCT_NUM")
    rst2("AO2_ADMIN_SYS_CDE") = rst("AO2_ADMIN_SYS_CDE")
    rst2("AO2_OPERATOR_ID") = rst("CO_CMPY_CDE")
    rst2("AO2_CURR_TME_STAMP") = rst("AO2_CURR_TME_STAMP")
    rst2("AO2_SPLIT_PCT") = rst("AO2_SPLIT_PCT")
    rst2("FP9_ASGN_NUM") = rst("FP9_ASGN_NUM")
    rst2("Type_IND") = rst("Type_IND")
    rst2("Start_Date") = rst("Start_Date")
    rst2("End_Date") = rst("End_Date")
     'etc etc
    rst2.Update
  End If
  rst.MoveNext
Loop
rst.Close
0
 
LVL 84
ID: 40585278
Error 3061 is "Too Few Parameters", which often means you've spelled something incorrectly. If this is the line that throws the error:

 Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM tbl_output WHERE AR_POL_ACCT_NUM = " & rst("AR_POL_ACCT_NUM"))

Then be sure you've spelled the TAble and Field names correctly. Also be sure of the field name in tbl_input ...

Also, if AR_POL_ACCT_NUM Is a Text field, you'll have to enclose it in single or double quotes:

 Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM tbl_output WHERE AR_POL_ACCT_NUM = '" & rst("AR_POL_ACCT_NUM") & "'")
0
 

Author Comment

by:centralmike
ID: 40586460
Good Morning Scott.  I verified the spelling of the tables and field names.  I copy your code into the module and it never makes it to the set rst2 statement.   Here is  a copy of the module.

Public Sub mytesting_Updates()

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tbl_input WHERE type_ind ='CT'")

Do Until rst.EOF

'Debug.Print rst.RecordCount

  Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM tbl_output WHERE AR_POL_ACCT_NUM = '" & rst("AR_POL_ACCT_NUM") & "'")

'Never makes it to this part of the code ***********************************************

  If Not (rst2.EOF And rst2.BOF) Then
    rst2.Edit
    rst2("SI_PRODUCTION_NUM") = rst("SI_PRODUCTION_NUM")
    rst2("CO_CMPY_CDE") = rst("CO_CMPY_CDE")
    rst2("AR_POL_ACCT_NUM") = rst("AR_POL_ACCT_NUM")
    rst2("AO2_ADMIN_SYS_CDE") = rst("AO2_ADMIN_SYS_CDE")
    rst2("AO2_OPERATOR_ID") = rst("CO_CMPY_CDE")
    rst2("AO2_CURR_TME_STAMP") = rst("AO2_CURR_TME_STAMP")
    rst2(AO2_SPLIT_PCT) = rst(AO2_SPLIT_PCT)
    rst2("FP9_ASGN_NUM") = rst("FP9_ASGN_NUM")
    rst2(Type_IND) = rst("Type_IND")
    rst2(Start_Date) = rst(Start_Date)
    rst2(End_Date) = rst(End_Date)
     'etc etc
    rst2.Update
  End If
  rst.MoveNext
Loop
rst.Close

End Sub
0
 
LVL 84
ID: 40586480
Are you still getting the 3061 error?

Your code is missing this line: "Dim rst2 As DAO.Recordset". See my earlier comment to determine where to include it.

Are "tbl_Input" and "tbl_Output" both Tables, or Queries?
0
 

Author Comment

by:centralmike
ID: 40586793
They are both tables.  I am not getting the error 3061.  Both there are no records being updated to the output table.  So the code is not making in the  Do until loop.  Adding the second rst2 does not fix the issue either.  Maybe I am doing something wrong.
0
 
LVL 84
ID: 40587062
That would seem to indicate the first SQL statement is not returning records:

SELECT * FROM tbl_input WHERE type_ind ='CT'

Are  you sure that returns records, and the tbl_Input contains a field named "AR_POL_ACCT_NUM"?

You can set a breakpoint to see what's happening in your code. To do that, place your cursor on the first line in the code block and press F9. The line should highlight in red. Now run your code through the interface, and when you hit that line you'll be transferred to the VBA Editor in "Debug" mode. You can then step through the code to determine what is happening.
0
 

Author Comment

by:centralmike
ID: 40587207
Scott I have done that.  The code stops at the first line of the "If" statement and then jumps to the "end if" statement.  Does not process any of the code in between the lines.  I can attached a copy of the database.  Mod_3 has a copy of the code.  The procedure name is "mytesting_Updates()"  Please take a look an see if you kind find the error.  I also create a query called "qryreturn_ct_IND.  This will return the one record that should be updated in the output table.
testlink.accdb
0
 
LVL 84
ID: 40588353
You have no data in tbl_Output, so this line would return no records:

Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM tbl_output WHERE AR_POL_ACCT_NUM = '" & rst("AR_POL_ACCT_NUM") & "'")

Therefore, when you hit the "IF" statement, it is correctly evaluation to TRUE for both EOF and BOF.

You do have records in "Complete_tbl_Output".  If you change the statement above to refer to that table, the code will execute - but it then UPDATES the record in Complete_tbl_Output.

I also note that you have extra trailing spaces in some of your data. For example, the AR_POL_ACCT_NUM has several values with trailing spaces. This can cause you troubles at times. You might be wise to Trim all of your data to insure those are removed. You can do that like this:

Currentdb.execute "UPDATE tbl_Output SET AR_POL_ACCT_NUM=Trim(AR_POL_Acct_NUM)"

If this is data you're getting from an external source, then the best way to handle this is to Trim it when it goes into the table.
0
 

Author Comment

by:centralmike
ID: 40591347
Good morning Scott, I have one final question.  How can I add another field to the where statement?  In addition to the AR_POL_ACCT_NUM I would like to include the CO_CMPY_CDE its a character field.  I tried using the and constant "and" I two different type of error messages.

-- this statement works great
Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM tbl_output WHERE AR_POL_ACCT_NUM = '" & rst("AR_POL_ACCT_NUM") & "'")

-- this statement does not work error code of type mismatch
 Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM Complete_tbl_Output WHERE AR_POL_ACCT_NUM = '" & rst("AR_POL_ACCT_NUM") & "'" And CO_CMPY_CDE = "'" & rst("CO_CMPY_CDE") & "'")

--compile error
  Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM Complete_tbl_Output WHERE AR_POL_ACCT_NUM = '" & rst("AR_POL_ACCT_NUM") & "'" And CO_CMPY_CDE = '" & rst("CO_CMPY_CDE") & "'")
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40592517
Try this:

Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM Complete_tbl_Output WHERE AR_POL_ACCT_NUM = '" & rst("AR_POL_ACCT_NUM") & "' And CO_CMPY_CDE = '" & rst("CO_CMPY_CDE") & "'")

I removed a ' and " just before your AND, and also a " just after the = you added. The goal is to enclose the data from the recordset in single quotes ...
0
 

Author Comment

by:centralmike
ID: 40603741
Thanks Scott for all your help.  The process works great!!!!!!!
0
 

Author Closing Comment

by:centralmike
ID: 40603746
I would like to say Thanks to Scott for all his assistance and paitients wtih me.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

790 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