DDL changes

[adam23]

I just inherited a large Access 2K3 application that is designed with 1
front-end mdb file that links to one of 3 dozen back-end mdbs. Each of the
back end dbs share the same table structure. They sort of function of as a
crude horizontal partition. If they were combined, the backend would be over
a few GBs.

My problem is that I have a few changes that I need to make to the backend
tables. I need to remove an input mask on a table, add an identity key, and
resize a couple fields. I have been putting off making these changes because
they will require me to go into each individual db. I was hoping to come up
with a clever solution but I haven't thought of anything. I also forsee
additional changes down the line to some of these backend tables. It doesn't
seem that DDL is advanced enough in Access to handle these issues. Anyone
have any ideas?

-Adam

 

[DAVID]

I'm not sure about the 'input mask' - you might have
to use VBA DAO for that - but DDL works for adding
and changing vfields.

For changing fields, you may have to add the new
field, copy the values, then delete the old field.
(The Access interface does this invisibly for you).
Also, you typically have to remove constraints and
relationships before you can delete a field, and
you have to add the constraints and relationships
back on to the new field before you use it.

If you are more used to ODBC or SQL Server, you
may prefer to use ADO DDL instead of DAO DDL. They
are slightly different, particularly in the names
of the field types.

We have a large table driven utility that does
this to update database structure on site, but
I also have a small application that just does
this on the fly, so that I could connect to different
copies of the BE and have them automatically update.

(david)

 

[Tim Ferguson]

I need to remove an input mask on a table,

Input masks are not part of the data model; they are only hints for the
form designers. Don't worry about it.

add an identity key, and

It is a _MAJOR_ reconstuction job to remodel a primary key in anything
but a trivial database. You certainly can do this with DDL; look up
CONSTRAINT, ADD CONSTRAINT and DROP CONSTRAINT clauses in the ALTER TABLE
syntax. Much easier to use the Access UI if you can because it'll do it
all for you.

resize a couple fields.

Hmmm... if you really need to, you can. Again the Access UI will do it
much more easily than you can. Still, it's a question of ADD COLUMN the
new one, UPDATE the new column to the old column value, and then DROP
COLUMN the old one. If you want the new name to be the same as the old
name, then you'll probably have to do it all over again.

A dirty trick here is to use DAO to access the field properties directly.

Hope that helps


Tim F