DAAUG Denver Area Access Users Group

1
DAAUGDenver Area Access Users Group

• Thirty Minute Topic
• How To Merge Identical MDBs

Danny J. Lesandrini ? DataFast Consulting
http//amazecreations.com/datafast/
2
The Problem

• Someone cloned your mdb
• Now you need to combine 2 (or more) of these
  popular files together

1 2 12
My_Data2.mdb
My_Data1.mdb
My_Data12.mdb
3
What does your data look like?

• Core Data Tables
• tblEmployee
• tblProject
• tblSite
• tblSiteNotes
• tblOrders
• tblOrderDetails

• Lookup Tables
• tblStatus
• tblState
• tblPriority

4
How to combine tables, in theory
Let's assume that each table has an Autonumber ID
field which starts counting from 1. Both sites
will have their own Employee and Time Entry
records from 1 to n. If you merge Employee
records from Site B into Site A, you will have to
renumber the Site B records so their values don't
collide with any Site A ID numbers. If you
change the EmployeeID for Site B, you will have
to cascade those changes to other related Site B
tables, such as tblTimeEntry. The same holds
true for lookup tables, but often, values must be
mapped Table A Table B
ID Status ID Status ----------
------------- ---------- -------------
1 Open 1 Open 2 Pending 2 Closed
3 Pending
5
Step by step (convert to code)

• Remove the Autonumber from EmployeeID field in
  tblEmployee for Site B.
• Store largest EmployeeID from Site A in
  variable named lngMaxSiteA_ID.
• Make sure that Cascade Updates are turned on
  for every relationship in the Site B
  database that has a relation on EmployeeID.
• Run a simple UPDATE query to add the value,
  lngMaxSiteA_ID, to EmployeeID for every
  record at Site B tblEmployee. The Cascade
  Updates should propagate those changes throughout
  your database.
• Append all Site B records from tblEmployee to
  the Site A tblEmployee. (Even though Site A
  still has an autonumber on EmployeeID, the
  records will go in without error and the
  correct ID numbers will be assigned.)
• Repeat this process for every table with an
  autonumber ID.

6
How to combine tables, in theory
tblEmployee_B
tblEmployee_A
UPDATE tblEmployee_BSET EmployeeID
(EmployeeID 13)WHERE EmpName NOT IN
(select EmpName
from tblEmployee_A)
7
Demo

• Select and link to databases
• Compare mdb2 with mdb1 data
• Run Scripts to update IDs
• Append new records to SQL database

8
Where to get the code?

Check out my sitehttp//www.amazecreations.com/da
tafast/Click on the DAAUG link Email me at
dlesandrini_at_comcast.net