Implementing Database Updates

[John Rischmiller]

I've been modifying a multi-user access database for a friends company. The
are minor revisions to the tables (mainly fields added, but extensive
changes/additions to queries and reports. In the meantime the database has
remained in use so my data is now out of date. What is the preferred method
of implementing my changes? I can take the database down for an hour.

Last time, I took my new version, deleted the tables and relationships,
imported the tables, manually modified them and re-built the relationships.
However this seems prone to error. The reverse of importing the modified
queries and reports to replace existing ones in the old database and
manually modifying the tables didn't work, possibly because the development
was on 2002 whereas the original was 2000.

Best regards, John.
mailto:[email protected]

 

[Brendan Reynolds \(MVP\)]

If you split the application into a 'back-end' data MDB containing only
tables and a 'front-end' application MDB containing everything else, with
links to the tables in the data MDB, then you can simply replace the old
application MDB with the new one without overwriting any data.

There are at least two different solutions to the problem of making the
changes to the data MDB. You can use DAO or SQL DDL queries to
programmatically make the changes to the old data MDB, or you can deploy a
new, empty copy of the new data MDB and use a series of append queries to
copy the data from the old data MDB into the new one. I prefer the later
approach, but the first technique can be used successfully as well.

 

[John Rischmiller]

Many thanks, Brendan, for your advice.

Best regards - John

 

[ĬÈÏ]

I've been modifying a multi-user access database for a friends company. The
are minor revisions to the tables (mainly fields added, but extensive
changes/additions to queries and reports. In the meantime the database has
remained in use so my data is now out of date. What is the preferred method
of implementing my changes? I can take the database down for an hour.

Last time, I took my new version, deleted the tables and relationships,
imported the tables, manually modified them and re-built the relationships.
However this seems prone to error. The reverse of importing the modified
queries and reports to replace existing ones in the old database and
manually modifying the tables didn't work, possibly because the development
was on 2002 whereas the original was 2000.

Best regards, John.
mailto:[email protected]