Table comparison between two databases with separate logins 2

[codehead]

Hello,

I am trying to compare the same table on both our development and production databases to find the differences. I can connect to both databases with Access 97 and I have linked to the tables in it (they are too large to import), but I can't seem to figure out how to compare each field for differences. I suspect that I need to write some VBA code, but I need an example. Or I could create a small Java program, but again I need an example to use as a starting point. I asked my DBA to link the two tables, but he refuses to do it because of the administration headaches. Has anyone done this before? Can anyone point me to an example/reference?

Thanks!

 

[essnrv]

Table Data or Table Structure ? Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[essnrv]

Hi,
If you want to compare data.Try this

Dim curdb As Database, SQLStmt As String, rs As Recordset, strTable As String
Set curdb = CurrentDb()
'First Find out the table names in your db
SQLStmt = "SELECT MSysObjects.Name, MSysObjects.Type FROM MSysObjects "
SQLStmt = SQLStmt & "WHERE (((MSysObjects.Name) Not Like 'MSys*') AND ((MSysObjects.Type)=1))"
Set rs = curdb.OpenRecordset(SQLStmt)
If Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
strTable = rs!Name
CompareData strTable
rs.MoveNext
Loop
End If
rs.Close


Private Sub CompareData(sTableName As String)

Dim curdb As Database, SQLStmt As String, rs As Recordset, strTable As String
Set curdb = CurrentDb()
SQLStmt = "Select * From " & sTableName & " Where [KeyField] Not IN "
SQLStmt = SQLStmt & "(Select [KeyField] from " & sTableName & " IN "
SQLStmt = SQLStmt & "'" & "[Second Database Location Here]" & "')"
Set rs = curdb.OpenRecordset(SQLStmt)
If Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
'Here Do whatever you want
End If
rs.Close

End Sub

Hope this helps Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[Bon999]

Thanks essnrv, it worked

 

[johnwm]

essnrv
Have a quick look at the Personal Profile link at the top left of the page. It gives clear guidelines on use and construction of signatures.
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[nathanstevenson]

Essnrv:

I have a similar problem as described here. Do you think you could explain the SQL statements, as I am a bit rusty.

Some questions:
- How do you run this code?
- How do you specify the locations of the two DBs?
- Does this code compare only primary keys? How could you compare two other fields, and set record A's primary key to record B's if there is a match (based on 2 other fields)
- What does it actually mean when 2 tables are linked? Can you link 2 tables if they have the same records, but different keys?

Your help would be much appreciated.

Thanks,
Nathan