Ms-access – Access 2013: Merging the same table-field with different data

Unless I'm missing something your problem is not moving the data it's dealing with the identity values that are already set up. If that is the case then try this.

1. Pick a value greater than your current ident values on either DB. I would pick a round value, say 1,000,000.
2. Pick the ident values you want to change (for example if you have lookup tables that are the same for both DBs then you probably want to leave them the same.)
3. When you move your data add the value you selected in step 1 to the idents value you want to change as you move them.

As long as you are consistent in the value you are adding all of your relationships will stay the same into the combined DB. So for example

You have an employee table with a current max id of 200,000 in DB A and 1,400,000 in DB B. You decide to move the data from DB A to DB B because it means moving less data. As you move your employee table you add 2,000,000 to the Employee_Id column.

USE DatabaseB
GO

SET IDENTITY_INSERT Employee ON

INSERT INTO Employee (Employee_Id, Other_Columns)
SELECT Employee_Id + 2000000, Other_Columns
FROM DatabaseA.dbo.Employee

SET IDENTITY_INSERT Employee OFF
GO

And last piece of advice back up everything before you start in case you make a mistake :)

An "After Insert" data macro can certainly modify records in other tables. You can use the LookupRecord, EditRecord, and CreateRecord Data Blocks as required.

However, a data macro cannot run a VBA script. Your actions are limited to those available in the data macro environment itself.