How to update/upgrade a mdb?

[].Cherub.[]

When I do a mdb for my customer and want to have him a newer version of
database how can I do it?
It is possible to update (upgrade) a .mdb file with a new version of
database?
How can I get data from current mdb to new mdb?
Anyone have nice tips??

Thanks!

 

[Albert D. Kallal]

A very common problem, and there is a very common solution.

You can do is simply send your client a new front end, and link to the
existing data.

In fact, you should have used the above setup BEFORE you send the mdb.
However, there is nothing stopping you from sending a new version of the
front end for them to use.

Check out:

http://www.granite.ab.ca/access/splitapp.htm

http://www.microsoft.com/accessdev/articles/bapp97/chapters/ba15_3.htm

 

[Paul Overway]

Note that even if the database is split, if there are changes in the backend
file, the developer needs to check the version of that file and add any new
fields, tables, relationships, indexes, etc.. programmatically. Or provide
a converter that will import data from the existing BE file to a new one.

I set a database property in the BE file that has the version number...if
the file is incompatible with the FE, I notify the user or make the changes
necessary to the BE.

 

[John Vinson]

When I do a mdb for my customer and want to have him a newer version of
database how can I do it?
It is possible to update (upgrade) a .mdb file with a new version of
database?
How can I get data from current mdb to new mdb?
Anyone have nice tips??

Thanks!

Split the database, using Tools... Database Utilities... Database
Splitter Wizard.

This gives you two .mdb files: one, the 'backend', containing only the
Tables (which should be pretty much static in design at this point);
ant the other, 'frontend', containing all your forms, reports, queries
etc. but no table data, with links to the backend.

If the user has these two databases, you can just send them a new
frontend; they can just replace their frontend with the new one,
refresh the links if necessary (ideally you'll have VBA code in the
frontend to do so), and go on with the new forms, reports, etc.

If you have two indpendently updated databases containing tables,
especially if you've entered data into your tables which conflicts
with data in their tables, but you need both... lay in a good stock of
midnight oil. You'll need to VERY CAREFULLY run multiple Update and
Append queries to migrate the data. If your tables have only "junk"
data, you can delete your tables, and use File... Get External Data...
Import to import all of their updated tables; reestablish all your
table relationships, and test, test, test.

 

[lj]

This is exactly the problem I am facing; however, it is compounded by the
fact that the structure of one of the backend tables needs to change (client
thought of new information that needs to be noted, hence one extra field
needs to be added to the table).

So how do I have him migrate his data into the new table structure and then
'reestablish the relationships' without his having to use the Database
window and the relationships window? (This is NOT a person I would want
messing with those--definitely not a Power User...)

Hoping you can give me some detailed answers... thanks.

ljr

 

[Brendan Reynolds]

There are at least two different approaches to this. One is that you can
programmatically make the changes to the existing database, using either DDL
queries or DAO. (You could use ADOX, but if you're dealing with Access and
JET, frankly you'd be crazy - or just poorly advised! to do so.) The
other is that you can send out an empty copy of the new database and
programmatically link the tables from the old database, run a series of
append queries to append the data from the old tables into the new tables,
and finally delete the links to the old tables.

There should be no need to 're-establish the relationships', as you will not
break them in the first place. You just have to be careful to append the
data in the correct order. If referential integrity rules specify that a
field in table A must match a field in table B, you need to append the data
to table B before you append to table A.

It's difficult to be much more detailed than that, because the details, of
course, differ from one database to the next.

 

[lj]

Thanks for the suggestions....

lj

There are at least two different approaches to this. One is that you can
programmatically make the changes to the existing database, using either DDL
queries or DAO. (You could use ADOX, but if you're dealing with Access and
JET, frankly you'd be crazy - or just poorly advised! to do so.) The
other is that you can send out an empty copy of the new database and
programmatically link the tables from the old database, run a series of
append queries to append the data from the old tables into the new tables,
and finally delete the links to the old tables.

There should be no need to 're-establish the relationships', as you will not
break them in the first place. You just have to be careful to append the
data in the correct order. If referential integrity rules specify that a
field in table A must match a field in table B, you need to append the data
to table B before you append to table A.

It's difficult to be much more detailed than that, because the details, of
course, differ from one database to the next.