Compare Microsoft Access tables row by rows to each other

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

I need learn how to write it myself.  My company doesn't allow me download software.

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

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?

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.

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

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.

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.

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

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") & "'")

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

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?

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.

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.

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

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.

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") & "'")

Thanks Scott for all your help.  The process works great!!!!!!!

I would like to say Thanks to Scott for all his assistance and paitients wtih me.