I’ve got an Access 2000 database that is relatively simple. There are only 3 tables, with table1 and table2 having a one-to-many relationship to two fields in table3. Also noteworthy is that table1 uses an autonumber field as its primary key (which is also the related field in table3). Table2 does not use autonumber for its primary.
This database is for a remote client, so I’m usually working on a local copy of the database without current data. When I?ve finished my work, I clear the tables, send the database to the client, and it?s usually a big hassle walking them through importing the data from the old tables. This is simple with a cut-paste of the data from each table, but the users aren?t very technically inclined.
My plan here was to write a VBA procedure that would import the data from the database into the new one. Does anyone know the “best practice” for importing tables, or the data from the tables, from an identical Access 2000 database so as to preserve the relationships?
I can use the DoCmd.TransferTables with this code to get the tables in:
DoCmd.TransferDatabase transfertype:=acImport, _
databasetype:=”Microsoft Access”, _
databasename:=strDatabaseName, _
objecttype:=acTable, Source:=strTableName, _
destination:=strTableName, structureonly:=False
I run this code multiple times for each table to be imported. What I had originally planned was to rename the original table, import the new ones w/ the same name as the original, then delete the renamed original. However, I failed to take in to account the relationships between the tables, which are lost when I delete the old tables. The new tables and old tables have identical structures, and won?t be changing.
Please feel free to post questions, if I’ve omitted any thing important.