modifying table fields in linked db tables

[nayborbob]

Is it possible to write VB code to change/add/delete
fields in linked tables which reside in a db and make the
changes permanent ?

I have an application where the key data tables are split
from the main db and I link them in.

I was trying to do the following in vb code (in a fix
pushbutton control event procedure designed to bring my
tables up to date w/ new front end upgrade). Basically I
want to move a field from one table to another. Here is
my strategy

1) copy data_tables.mdb to newdata_tables.mdb
2) open newdata_tables.mdb
3) edit 1 table by adding new field to it
4) fill table w/ field data from another table in the db
5) edit the other table to delete the field data copied
from
6) close db
7) rename data_tables.mdb to olddata_tables.mdb
8) rename newdata_tables.mdb to data_tables.mdb
9) relink tables

Here are my problems/concerns

Step 5 - how do I delete a field and make it show up in
the saved db file ? I know how to append fields to a new
table and then append the table to the db, but cannot
find any info on modifying a table and updating the db.

It works in code (deleting field), but when I close the
db and re-open the changes are gone (non persistence ?)!

Steps 6-9 - Not sure how to accomplish this. Since the
data_tables.mdb are automatically linked to the front end
db, I dont know how to close just the linked tables. Do
I need a third mdb file to run this code from while the
other 2 dbs are closed ?

Then how do I rename the mdb files and relink in vb code ?


HELP !

Thanks
Bob

 

[Michel Walsh]

Hi,



Untested, but try to open a database object that points to the linked
one, assuming a mdb, that may looks like:


Dim myDb As Database
Set myDb = DBEngine.Workspaces(0).OpenDatabase _
(Mid(CurrentDb().TableDefs(TableName).Connect, _
11), False, False, "")


and use myDb instead of CurrentDb.



Hoping it may help,
Vanderghast, Access MVP