If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DDL changes
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 |
#2
|
|||
|
|||
DDL changes
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) adam23 wrote: 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 |
#3
|
|||
|
|||
DDL changes
=?Utf-8?B?YWRhbTIz?= wrote in
: 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 |
Thread Tools | |
Display Modes | |
|
|