VBA - Compare and Merge/Join/Match 2 Tables in Separate Dbs

[FantasyCommish]

I have a Db on a shared drive. 2 different depts are responsible for updating different aspects of the data in this Db. To prevent data conflicts, the depts are making copies of the Dbs, making changes, and overwriting the Shared drive files. However, because the changes are being made to different fields, albeit in the same table, and the changes are being simultaneously, simply saving over the previous version will not prevent data loss.

I have attempted to write a VBA program to compare the two tables and put the changes from each into the other. I'm not even half way through and I'm stuck. Additionally, simply manually copying and pasting doesn't bring over necessary lookup relationships. Any help would be great. Below is my code thus far:

Sub AddNewParts()

Dim dbNew As DAO.Connection
Dim dbOld As DAO.Database
Dim db As DAO.Database


Dim dbNewname As String
Dim dbOldname As String
Dim rsNewParts As DAO.Recordset
Dim rsOldParts As DAO.Recordset


Dim rsDifferences As DAO.Recordset


dbNewname = "C:\Users\lsaxon\Desktop\Capacity Tool Database New Parts Testing.accdb"
dbOldname = "C:\Users\lsaxon\Desktop\Capacity Tool Database Working Testing.accdb"
dbName = "C:\Users\lsaxon\Documents\Database2.accdb"


Dim strSQL As String


Dim rcdCnt As Integer

Set dbNew = OpenDatabase(dbNewname)
Set dbOld = OpenDatabase(dbOldname)


strSQL = "SELECT [Capacity Tool Database New Parts Testing.accdb].Parts.Part_Number " & _
         "FROM [Capacity Tool Database New Parts Testing.accdb].Parts " & _
         "WHERE [Capacity Tool Database New Parts Testing.accdb].Parts.Part_Number " & _
         "NOT IN " & _
         "(SELECT [Capacity Tool Database Working Testing.accdb].Parts.Part_Number " & _
         "FROM [Capacity Tool Database Working Testing.accdb].Parts ); "


Set db = OpenDatabase(dbName)
Set rsDifferences = db.OpenRecordset(strSQL)


rsDifferences.MoveLast


rcdCnt = rsDifference.RecordCount








End Sub

I know DAO is not the way to go. I need some direction on how to get started on ADODB if that's the way to go instead, or if there's an easier way.

The error currently is " '3204' can't find the file at path XXXX.accbd"

I'm still a work in process with VBA so links to related articles are appreciated.

 

[ranman256]

There is no need for any code.
simply link/attach the tables you need from the other db, (use external data tab)
then run queries against them.

zero code. (simple)