Copying fields from one linked table to another

I have an MS Access application and one of the functions is a comparison function where I compare the Master back-end structure to another back-end.  Currently, when it sees a discrepancy, it highlights the field and I add it manually.  

I was wondering if there was a way to copy the missing field (with everything, index, type, default value etc.) from the master back-end, to the destination back-end?  They are both linked to my front-end application.
Andy BrownDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
>>I was wondering if there was a way to copy the missing field (with everything, index, type, default value etc.)
As far I know we don't have any method. We have to add columns manually using command/UI
Andy BrownDeveloperAuthor Commented:
Hi Pawan - thanks for coming back to me,

Understood, and just to clarify.  I can easily add fields via VBA/SQL to linked back-end tables.  But it's the exact structure of the field that I want to try an replicate.
Pawan KumarDatabase ExpertCommented:
Get the table definition of the current and then replicate for the new fields in another table.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Andy BrownDeveloperAuthor Commented:
You are right, and I have done something like this in the past.  However, I was hoping to get a good/standard method for doing this.

Thanks again.
You can create DDL queries and run them to modify the BE.  HOWEVER, this is a pretty complicated scenario.  I have an application that is sold to the public and occassionally, I need to distribute updates.  FE updates are easy since we just replace the FE.  BE updates are more complicated since we are not on site to do them and they have to be handled by the user admin who is not a technition.  As it happens my app gives the users a choice of SQL Server or ACE as the BE when they install the app.  Since I have to maintain a SQL Server BE regardless, I bought a tool called SQL Examiner.  It compares two instances of a database and generates DDL to make one instance match the other.  I run this on the SQL Server version of the BE and use that directly to update SQL Server installations and I modify the DDL to use it to update the ACE installations.  The generated DDL is the same for both SQL Server and Access.  The difference is the table name references - dbo.tblsomename vs tblsomename so those can be easily fixed with a text editor.  Also since Access cannot run multiple SQL statements in one querydef, I have to split each of the updates into a separate querydef.  It rarely takes more than an hour to generate a complete set of change code.

If you consider your time as money, the $400 is well worth the price.  I even use the tool for apps that are ACE only.  I just have to upsize each BE version and then compare them.

Once the updates are applied, I run the tool a final time to ensure that all updates were done correctly and there are no differences.

I've attached an sql file that shows the script generated by SQL Examiner.  To run it against ACE, you need to separate out the individual DDL statements.  I also attached the .accdb file that the client uses to update their ACE BE.  Use the shift key when you open the app and then look at the code behind the run button on the main form.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
This may not be as cumbersome as you would think.

You can simply "Copy" the new field from one table, ...and "Paste" it into the target table...:
Copy fieldPaste in FiledField exists in both tables
*However*, ...this will not bring in the Data from this field.
A relatively simple way to do that, would be to highlight the entire (source table) field in *datasheet view*, then pasting it into the newly copied (empty) field in the target table.

I am sure someone could take the time to write something custom to do this (in using DAO or ADO), ...but no, ...there is no VBA "Method" for doing this.
The reason being that Access SQL is not exactly the same as other SQL "dialects"
Access fields can contain non-standard (Access only) attributes like: Text Align, validation text, IME Mode, hyperlinks, Multivalued fields, Attachment fields, ...etc

It might be simpler, for now, to copy/paste the field, ...then the copy/paste data...

Lets see what other Experts may post...

Jet and ACE support DDL even though the QBE only supports building DML.  DDL (Data Definition Language) is what we use to create, alter, and drop database objects.  DML (Data Manipulation Language) is what we use to query or modify data.
Andy BrownDeveloperAuthor Commented:
Thank you everyone for your comments.  Pat - that looks like it will do the trick nicely.
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.