Copying fields from one linked table to another

Andy Brown
Andy Brown used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
>>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 BrownDeveloper

Author

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 Expert
Awarded 2016
Top Expert 2016

Commented:
Get the table definition of the current and then replicate for the new fields in another table.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Andy BrownDeveloper

Author

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.
Distinguished Expert 2017
Commented:
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.
DEA_AuditVer37_DEA_AuditVer38_200912.sql
DEA_Ver37toVer38.zip
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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...
;-)


JeffCoachman
Distinguished Expert 2017

Commented:
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 BrownDeveloper

Author

Commented:
Thank you everyone for your comments.  Pat - that looks like it will do the trick nicely.
Distinguished Expert 2017

Commented:
You're welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial