General discussion


Access 2K - Table importing

By CptOmlly ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by OTL In reply to Access 2K - Table importi ...

You need to change your relationships to match your end result. Relating to a record number, never works (someone could delete a record), use account number, named reltional fields. Much experience in relating records to individual names within a field and it works great in all (Dbase 2-4, Access 97- 2000, and even a hybrid Lynix/HTML/CGA platform). In Access must force all caps/lower case and only data from a specific field to keep database integrity.

FYI - OC-48 contains 16 OC-3 or 48 - DS-3 channels, DS3 contains DS1 - 28 channels, DS1 contains 24 voice/data channels (relational hierachy - what do you really track on?). Copper cable can contain up-to 6000 individual circuits (sorry highest I delt with). Utilized 9 databases (Dbase 2) to create records for a 10,000 + community for phone/data services and tracked trouble reports which were implimented directly into UNIX for credits towards bills.

Collapse -

by CptOmlly In reply to

I have no idea what the heck you are talking about. What's this stuff about WAN circuits? I don't know where you got that from my question, because that has absolutely nothing to do with my question.

As to your comment that actuallly somewhat related to my question.... my database works fine as it is now. I am having no problems with the relationships, structure, etc. I am not trying to relate to record number or relate to names within a field. I only have two simple one to many relationships that relates fieldX (unique) in one table to fieldY (not unique) in another table

The ONLY problem I have is trying to figure out how to import the data to the tables. If I do a cut and paste (just the data, not the entire table) from the old table to the new one, everything is great. The problem I have is that I don't want to go to the client's site everytime I update something to do this.

Can anyone help???

Collapse -

by CptOmlly In reply to Access 2K - Table importi ...

Perhaps I wasn't clear in my description of my database. For clarity, I'll provide more details. There are three tables: tblEmployee, tblCertification, and tblAssoc. tblEmployee has an autonumber field as primary key, and other fields for name, department, etc. tblCertification has a certification name as primary, and an integer that represents how long the cert is valid in months. tblAssoc has one to many relationships to both the autonumber field in tblEmployee and the certification name in tblCertification. tblAssoc also has 2 dates to track when the cert was received and when it expired.

Collapse -

by CptOmlly In reply to Access 2K - Table importi ...

This question was closed by the author

Related Discussions

Related Forums