<div>
Forgot to mention that both tables may contain data.
</div>

<div>
What do you mean when you refer to Properties? Are you referring to the properties of a table Field? If so, what properties are you not transferring correctly? 
 
Your tool could certainly iterate the properties of each Field before creating it in the external db ... but it's not overly clear what you're trying to achieve.
</div>

<div>
Thanks for your questions... Apparently, I was not as clear as I was trying to be... ;-) 
 
Yes, I am referring to the properties of a table field. &nbsp;Want the fields in the table created by the add-in to be exact clones of what is in a table I am using for reference. &nbsp; 
 
We are using reference tables to get around a limitation in how the add-in produces database tables. 
The reference tables would just be tables in databases that we have already modified to meet our requirements. 
 
The table created by the add-in may have the same fields as my reference table, but if the fields in my reference table have properties that the add-in has not created, I want the field properties migrated over also. &nbsp; 
 
So... I really have three cases: 
 
case 1: &nbsp;A field does not exist in the new table. Must copy from the reference table with all field properties. 
 
case 2: &nbsp;Field does exist in both new table and reference table, but field does not have all properties of same field in reference table. &nbsp;Must copy over field properties. 
 
case 3: &nbsp;Field exists in both tables and all properties are the same. &nbsp;Do nothing in this case. 
 
I have attached a code excerpt. &nbsp; 
 
Is there a way to easily clone a table field? 
 
e.g. &nbsp; Set NewField = TableDef(&quot;table1&quot;).Fields(&quot;MyField&quot;).Clone &nbsp;would be nice. 
 

<pre><code class="code-10 nolinks" id="code-121212"> If tDefD.Updatable = True Then
 ' Set up some variables used just here...
 Dim NewField As DAO.Field, NewProp As DAO.Property, PropFound As Boolean
 
 Set NewField = tDefD.CreateField(fldS.Name, fldS.Type, fldS.Size)
 
 ' Copy all the source field's property's to the new field...
 
 'Set the common properties first...
 On Error Resume Next 'This section is prone to runtime errors...
 With NewField
 .AllowZeroLength = fldS.AllowZeroLength
 .Attributes = fldS.Attributes
 .DefaultValue = fldS.DefaultValue
 .OrdinalPosition = fldS.OrdinalPosition
 .Required = fldS.Required
 .ValidateOnSet = fldS.ValidateOnSet
 .ValidationRule = fldS.ValidationRule
 .ValidationText = fldS.ValidationText
 .VisibleValue = fldS.VisibleValue
 End With
 On Error GoTo 0
 
 'Copy the more obscure properties...
 
 For Each PropS In fldS.Properties 'Iterate the source field's properties...
 
 'Does the destination field already posess the source field's property?
 PropFound = False
 
 For Each PropD In NewField.Properties ' Iterate the destination field's properties...
 If PropD.Name = PropS.Name Then 'Found the property, but must make same...
 PropFound = True
 End If
 Next PropD
 
 If Not PropFound Then 'Property not found, must create it...
 On Error Resume Next 'This is prone to runtime errors...
 Set NewProp = NewField.CreateProperty(PropS.Name, PropS.Type, PropS.Value)
 NewField.Properties.Append NewProp 'Attempt to append the property...
 On Error GoTo 0
 End If 'Not PropFound
 
 Next PropS
 
 'Done with setting all the damn properties... now just append the new field...
 
 tDefD.Fields.Append NewField
 Log = Log &amp; vbCrLf &amp; &quot;FIELD [&quot; &amp; fldS.Name &amp; &quot;] was added in TABLE [&quot; &amp; _
 tblName &amp; &quot;] in the destination database.&quot;
 FieldsMatch = False
 
 End If 'tDefD.Updatable = True
 
 Else
#If 0 Then
 'Did find the field... examine properties and do property updates HERE...
 ' Sync the properties to make same...
 
 Set fldD = tDefD(fldS.Name)
 
 For Each PropS In fldS.Properties 'Iterate the source field's properties...
 
 'Does the destination field already posess the source field's property?
 For Each PropD In fldD.Properties ' Iterate the destination field's properties...
 If PropD.Name = PropS.Name Then 'Found the property, but must make same...
 
 Select Case PropD.Name
 Case Is = &quot;FieldSize&quot; And ((fldD.Attributes And dbVariableField) &gt; 0)
 
 'On Error Resume Next 'Some properties are read only...
 If PropD.Value &lt;&gt; PropS.Value Then
 PropD.Value = PropS.Value
 
 If PropD.Value = PropS.Value Then
 Log = Log &amp; vbCrLf &amp; &quot;PROPERTY [&quot; &amp; PropD.Name &amp; _
 &quot;] of FIELD [&quot; &amp; fldD.Name &amp; _
 &quot;] was modified in TABLE [&quot; &amp; tDefD.Name &amp; _
 &quot;] in the destination database.&quot;
 End If 'If PropD.Value = PropS.Value
 
 End If 'PropD.Value &lt;&gt; PropS.Value
 
 End Select 'Case PropD.Name
 
 End If 'PropD.Name = PropS.Name
 
 Next PropD
 
 Next PropS
#End If
 End If 'Not FieldFound
 
 Next fldS
</code></pre>
</div>

<div>
Part of the challenge of copying field properties is that some properties are specific to certain data types. &nbsp;To really do this properly, the copying of field properties must be tied to the data type of the field. 
 
This quickly can turn into a mountain of code for something that seems like it should be simpler. 
 
Steve
</div>

<div>
Ok, well I think you answered my question... there really isn't a good way to make this significantly easier--I suspected as much. &nbsp;:-( &nbsp; &nbsp; 
 
I have tried to use DoCmd.CopyObject and similar, but I keep running into its limitations in this situation. 
 
Thanks for your suggestions for the code tweaks... I will take a look at them. &nbsp; 
 
Thanks for your assistance and insights. &nbsp; 
 
Have a great day! 
 
Steve
</div>

<div>
<div class="content wysiwyg-content">
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. &nbsp;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. &nbsp;There is a table definition text file for the tables it creates, but it is a very basic, very limited approach. &nbsp;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 &amp;&nbsp;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. &nbsp;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: &nbsp;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: &nbsp;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. &nbsp;Not the field data, just the FieldDefs. &nbsp;ALSO, &nbsp;this is a one way synchonization, from Table2 to Table1. &nbsp;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: &nbsp;(not actual code below) 
&nbsp; &nbsp; &nbsp;For Each fDef2 in Tdef2.Fields &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&lt;--- Iterate fields in Table2 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Found=False 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;For Each fDef1 in Tdef1.Fields 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; If fDef1 &quot;is same as&quot; fDef2 Then Found=True 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Next 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;If Not Found Then 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Create a copy of fDef2 and place it in Tdef1 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;End If 
&nbsp; &nbsp; &nbsp;Next 
.....Etc... 
 
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. &nbsp; 
 
&gt;&gt;&gt;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: &nbsp;There has to be an easier way... &nbsp;what am I missing here? 
 
&gt;&gt;&gt;I will be happy to answer any questions for clarification prior to awarding points. 
 
Thanks! 
 
Steve
</div>
</div>

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
 Found=False
 For Each fDef1 in Tdef1.Fields
 If fDef1 "is same as" fDef2 Then Found=True
 Next 
 If Not Found Then
 'Create a copy of fDef2 and place it in Tdef1
 End If
 Next
.....Etc...

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.

Thanks!

Steve

Synchronize Tabledefs in Different DBs

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.