Linked Tables - Modify Structure 1

[RichardBarth]

I have an application where all the forms/queries/reports/modules are in one .mdb ("main") and the tables are in another ("table") .mdb. Of course, the tables are linked in the "main" .mdb.

This application has been distributed to many locations. I need to make a change in the software. Well, that's pretty easy - just distribute a new "main" mdb and automatically relink the tables when it starts.

However, I need to add some fields to the linked tables!! How can I add code into the startup of the "main" mdb which will modify the structure (add fields) to some of the tables in the "table" mdb???

Any help is greatly appreciated.

Thanks.

Richard

 

[Golom]

Adding fields is usually something like

ALTER TABLE SomeTable ADD COLUMN NewColumn Text(50)

You can just build whatever ALTER TABLE statements that you need and then run them in your startup procedure. Be sure to trap the errors because this will pop-up something like

[red]Field NewColumn already exists in table SomeTable.[/red]

If you run it a second time.

Alternatively, check for the existence of the fields that you want to add and run the ALTER TABLE code only when you don't find them.

 

[RichardBarth]

Thanks, that works. What a simple answer.

Actually, I had thought about Alter Table but erroneously assumed that it wouldn't work based on the fact that I click on Design for a linked table I cannot change any of the structure. I guess I shouldn't have "assumed.