Solved

Compare Microsoft Access tables row by rows to each other

Posted on 2015-01-27
20
184 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 34

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

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Backup Your Microsoft Windows Server®

Backup 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.

Join & Write a Comment

Suggested Solutions

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

759 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

18 Experts available now in Live!

Get 1:1 Help Now