Updating data in an Access database on the web

November 3, 2005 7:40 AM

How do I import data into a table in an Access database in such a way that the new table will retain all of the relationships currently extant in the database? Is there a way to make a new table and overwrite the old table (say the table is called 'users') and preserve the relationships?
posted by zephyrbill to Computers & Internet (6 answers total)

The straight forward way to do this is to treat it as a two part process. Step 1 is to extract the SQL statements used in the original database to create the table and govern the relationships of data elements therein. MySQL, PostgresSQL, Access, and other popular relational databases have various utility commands for doing just this. You then use, with editing, the Create SQL statements to create a new copy of the table with the new name, data element names, etc. If you are working exclusively in MS Access, see here for the copy and paste version of this method (Copy Structure Only), which is probably what you really want...:-) Step 2 is to then extract the data from the original table, and populate the new table with it. Again, within Access itself, Copy and Paste Special are your friends.
posted by paulsc at 8:04 AM on November 3, 2005

Sorry. "see here" above should be see here [link added]
posted by paulsc at 8:06 AM on November 3, 2005

paulsc is describing how to move a table from one database to another.

i'm not sure that's what you asked. but to me what you asked makes no sense, because you seem to be asking "how can i change a table so that it is the same as it was before it was changed".
posted by andrew cooke at 8:07 AM on November 3, 2005

Import your new data into a temporary table.

Clear the data in the existing table, but don't delete the table - as this will destroy all your relationships.

Run an append query to put all of the new data into the empty table from the temporary one.

Clear your temporary table (just good housekeeping; not really necessary)

Put all of the above into a macro if you're going to do this regularly, so that the whole process becomes somewhat easier to perform!

Rinse, repeat, wipe hands, profit (and whatever other cliches are relevant!)
posted by Chunder at 8:07 AM on November 3, 2005

As for your second question, well, that depends...

If any data elements in the original table are used or referenced in other tables, and if your database enforces referential integrity, no. An example of this would be if your current "users" table has something like a UserID field, which is a unique primary key, and is then used in other tables as a foreign key to unambiguously link the info in the "users" table to entries in the other tables for user related information.

Another reason the answer will be "no" in Access is if a primary key in your "users" table is autonumbering.
posted by paulsc at 8:19 AM on November 3, 2005

What Chunder said.
posted by grateful at 9:32 AM on November 3, 2005