Applying changes easily in Access Database

0
votes

I have got a backup of a live database (A copy of an ACCDB format Access database) in which I've worked, added new fields to existing tables and whole new tables.

How do I get these changes and apply that fast in the running database?

In MS SQL Server, I'd right-click > Script Table As > Alter To, save the query and run it wherever I desire, is there an as easy way as that to do it in an Access Database ?

Details:
It's an ACCDB MS-Access database created on Access 2007, copied and edited in Access 2007, in which I need to get some "alter" scripts to run on the other database so that it has all the new columns and tables I've created on my copy.

3
sqlms-access
I've been developing profession in Access since 1996, and I've never once written a script to alter the structure of an Access/Jet/ACE back end in production use. I just open the back end and make the changes manually. Please explain why you think you need to script it. Keep in mind that it takes several times as much time to write and test your script as it would to make the changes by hand, using the Access UI. - David-W-Fenton
In fact I've used the access UI, and it surely wasn't that hard (copying the tables, and the new fields) but it took ten times longer to apply and check the changes than it would with sql (which I'm experienced to), where I'd just double click the .SQL files with the alter and create tables and press F5. And that extra time could have been better used better. It went all OK. Thank you! - Marcelo

3 Answers

0
votes

For new tables, just import them from one database into the other. In the "External Data" section of the ribbon, choose the Access icon above "Import". That choice starts an import wizard to allow you to select which objects you want imported. You will have a choice to import just the table structure, or both structure and data.

Remou is right that you can use DDL ALTER TABLE statements to add new columns. However, DDL might not support every feature you want for your new columns. And if you want not just the empty columns added, but also also any data from those new columns, you will probably need to run UPDATE statements to get it into your new columns.

As far as "Script Table As", see if OmBelt's Export Table to SQL tool for MS Access can do what you want.

Edit: Allen Browne has sample ALTER TABLE statements. See CreateFieldDDL and the following one, CreateFieldDDL2.

0
votes

You can run DDL in Access. I think it would be easiest to run the SQL with VBA, in this case.

0
votes

There is a product called DbWeigher that can compare Access database schemas and synchronize them. You can get a free trial (30 days). DbWeigher will write a script of all schema differences and write it out as DDL. The script is thorough and includes relationships, indexes, validation rules, allow zero length, etc.

A free tool from the same developer, DBWConsole, will let you execute a DDL script against any Access database. If you wrote your own DDL scripts this would be an easy way to apply the changes to your live database. It even handles some DDL that I don't know how to process in VBA (so it must be magic). DBWConsole is included if you downloaded the trial version of DBWeigher. Be aware that you can't make schema changes to a table in a shared Access database if anyone has the table open.

DbWeigher creates a script of all differences between the two files. It can be a lot to manually parse through if you just want a few of the changes. I built a parser for DbWeigher script files so they could be filtered by table, to extract just the parts I wanted. I contacted the DbWeigher author about it but never heard back. It's safe to say that I have no affiliation with this developer.