?
Solved

Compare Microsoft Access tables row by rows to each other

Posted on 2015-01-27
20
Medium Priority
?
205 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 38

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 85
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 38

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 85
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 85
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 85
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 85
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 85
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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

764 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