Avatar of PumpMeister

asked on 

Synchronize Tabledefs in Different DBs

Sorry in advance if this seems a bit long...

I am a Chemical Engineer who has also been developing Access DBs since the mid-90s.
I use Access DBs to manage the multitude of information that is often generated in engineering projects.

My current employer, has a 3rd party plug-in for AutoCAD, that generates backend databases containing the text information from various drawings.  This allows for easy creation of bill of materials lists that are always in sync wiht project drawings.

Unfortunatly, the 3rd party add-in creates tables for a new project that do not contain all the fields we need and use.  There is a table definition text file for the tables it creates, but it is a very basic, very limited approach.  The fields it creates don't have all the properties we want to use.

To get around this, I have created a tool (a form & code in the form module) to synchonize the relevant
tabledefs between the tabledefs the add-in created and our improved version from an existing/old project db.  I know that this probably seems like overkill as opposed to just modifying the definiition file, but my principle user would prefer not to mess with the definition text file.

THE CURRENT PROBLEM:  As it exists now, my tool works fine, except that it does not get all the field properties copied over, which limits its usefulness.

FOR EXAMPLE:  If Table1 is a table that the add-in created, and Table2 is a table in ***another db*** that has the desired fields in it, then I need to make sure all the fields and their associated properties from Table2 end up being copied to Table1.  Not the field data, just the FieldDefs.  ALSO,  this is a one way synchonization, from Table2 to Table1.  Reason is if we get a newer version of the add-in, it may have new fields in it that it is not currently creating (current version), and we would want to keep them.

MY CURRENT APPROACH:  (not actual code below)
     For Each fDef2 in Tdef2.Fields              <--- Iterate fields in Table2
           For Each fDef1 in Tdef1.Fields
                If fDef1 "is same as" fDef2 Then Found=True
           If Not Found Then
              'Create a copy of fDef2 and place it in Tdef1
           End If

What I think I need here is a copy constructor for the FieldDef like in C++... darn VBA!
Right now, it looks like I have iterate every property in the source FieldDef and copy them over one by one to effectively clone the relevant fields, which is a real pain.  

>>>What complicates this is that both tables may contain data that I must keep...
I would love to use DoCmd.CopyObject or somesuch with this, but I don't think I can do that.

QUESTION:  There has to be an easier way...  what am I missing here?

>>>I will be happy to answer any questions for clarification prior to awarding points.


Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon