Access DB Upgrade Rollout / DB Structural Sync Ideas

[damientaylorcreata]

Hi Guys,



We currently manage a large number of MS Access databases which are distributed across the country. These databases (in theory) all have the same data structure, however the data is specific to the location for where they are installed. What we are needing to do is figure out the best way on upgrading each of the databases when we make strutural changes to the master DB.

It would need to somehow need to perform a comparison between the local DB with the remote master DB. If the struture is different, it would then need to somehow update the local DB structure. It would somehow need to workout if the change affects the data within the tables. For instance if the upgrade involved adding referential integrity to a table, it would need to somehow detect orphans and link them to a catchall type parent record.

Has anybody had experience with this? Any suggestions for possible sollutions?

Look forward to hearing your thoughts?

Thanks,
Damien

[qa12dx]

what u want to do, i dont think is possible, cause that would involve some serious programming and cost wise it would be cheaper to move to a different system.

why dont you get a webdisk. put the tables there, and have local machines access the data db from the web. i think u could do that easily...

[ssanfu]

You could possible do it, but it would be a BIG job - lots of code. The big problem is modifying Access over the internet. ANY disruption in connection could (and probably would) corrupt your database. Maybe a VPN would work; maybe a terminal service (Citrix?) would work. You would need to do extensive testing.

You can make changes to a dB structure using DDL, but first you would need to check if the candidate PK field has duplicates or Nulls. Hopefully the table PK is an autonumber type field and not a text field.

No, actually the first thing would be to create a backup. You do have a FE/BE setup... right??? And a backup???
Then, after that pesky connection problem, you would need to kick everyone out and open the BE in exclusive mode.
Next, all the check/tests/verifications.
Only then you would be able to execute the DDL code. (cross your fingers and pray to the database gods)
And more testing......

I think it would be much safer and faster to FTP the BE to a local computer do the changes, then FTP the dB back to its home.
Still would need a way to kick everyone out & lock the local dB to keep anyone from making changes during the mods (to avoid losing data)....

I am very thankful that I do not have to do this.. What a project!!!

I wish you lots of luck. Hope it works out..

[damientaylorcreata]

Thanks for your replies! Yes, I would download the master DB to the local. Lock out the users, perform a backup and then go into the sync process. However assuming that both access DB's are stored in the same local directory, a full backup is performed and users are kicked out, are you saying that there really isn't any solutions that we compare both DB's from a strutural perspetive and perform the appropriate adjustments automatically. Do we really need to manually create the DDL ourselves.

In which case maybe we need to store each change from now on, as DDL and build a script for each change. Once we are ready to deploy the changes we create a script that can be applied to each of the local DB's? Are there any Windows type scripts that you can parse DDL code to on the command line that will update the local DB's?


Thanks Guys!

Damien

[ssanfu]

we compare both DB's from a strutural perspetive

I have a couple of programs (.exe) that will compare structures between MDBs. They are for Acc200 -2003. Don't know if they will work post A2003.
Here is a link for code to compare two tables. (there are other examples on the net...)

- Rogers access Library -
Compare Two Tables (intermediate) http://www.rogersaccesslibrary.com/forum/topic376.html

and perform the appropriate adjustments automatically. Do we really need to manually create the DDL ourselves.

If you have the differences, you should be able to create VBA code to create the DDL code to make the changes.

Same site, there are several examples of how to write DDL code:

* SQL Loader (advanced): http://www.rogersaccesslibrary.com/forum/topic265.html
* SQL DAO Loader (advanced): http://www.rogersaccesslibrary.com/forum/topic266.html

Data Definition Language: SQL vs DAO http://www.rogersaccesslibrary.com/f..._topic570.html
Recreate Table In SQL (advanced): http://www.rogersaccesslibrary.com/forum/topic289.html
Upsize Table 2 SQL Server 2 (advanced): http://www.rogersaccesslibrary.com/forum/topic221.html

- From Allen Browne -
DDL Code Examples: http://allenbrowne.com/func-DDL.html