Updating the back end

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using Access 2003
Are there any standard strategies for updating the back end of a database
from within VBA. The database would be using the runtime version on several
different computers at sites across the country. This would make it
unfeasable to do each manually, and I would want to retain the data existing
in the back end.
Some possible issues to handle would be - adding a field to a table. adding
an empty table to the database. Adding a table which contains data to the
database. Changing the data type of a field (though I don't see this
happening too much).
 
Snoop around this newsgroup from the past two weeks or so. I distinctly
remember seeing some code and post about updating a back end using VBA code.
 
One approach is to create a little "updater.mdb" and ask the client to run
it.

It:
a) Asks the user to locate the back end file, if you don't know where they
may have it. For the FileOpen API call, see:
http://www.mvps.org/access/api/api0001.htm

b) Performs an OpenDatabase() with exclusive access.

c) Executes the changes, and recording a log of the changes locally (in the
updater, not the back end). On error, it rolls back, and shows a report of
the stages that succeeded, and the step that failed.

Re your specific questions:
1. To add a table (with or without data), put the table into the updater,
and copy it into the database. You will then typically need to
CreateRelation() to other tables as well.

2. To change the data type or size of an existing field, in JET 4 (Access
2000 and later), execute an ALTER TABLE statement. For example, this changes
MyField2Change into a Text field of 100 characters:
ALTER TABLE MyTable ALTER COLUMN MyField2Change TEXT(100);
In older versions, you needed to create a new field, copy the data in, and
then delete the old field.


If there are lots of changes that need to be made, an alternative approach
would be to create a new back end structure exactly as desired but without
the data. Then program to:
a) Rename their original as a backup.

b) Copy the new back end to their server.

c) Copy all the data from the backup into the new BE, making sure you copy
the data from the 1-side of all relationships before related data.
 
Thanks Allen, Just the kind of advice I was looking for.

Pertaining to the last alternative of making a new back end and copying the
data from the old back end. I have some auto number fields. Wouldn't the
copying process potentially change these numbers so they don't match the
original field numbers? (I am thinking of append queries from the old to the
new)
 
If you append the autonumber column as well as the other columns into the
new table, the autonumber value will be the same as appended. One word of
caution though (speaking from experience here)...it is a good idea to reset
the Seed for all autonumber fields after doing appends of this nature. If
you don't, there is a good chance your users will get an error about a
duplicate index value.

I usually do an updater type database conversion for major schema changes
(i.e., wholesale copy existing data to new database), and make changes
programatically from within the front end for minor changes (i.e., add or
drop a column, add a table, etc).
 
Back
Top