General discussion


Access 2003 General Question

By ttosun ·
I have 2 DB's, db1 and db2. I need 3 columns (i.e. first name, last name, email)from table A on db1 to update 3 columns on table B on db2 automatically. The columns are not named the same way (i.e First_Name on db2, Firstname on db1). Is this possible or am I dreaming? Please be as detailed as possible. Access is my Achilles heel. Thank you.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Toivo Talikka In reply to Access 2003 General Quest ...

Are you sure you are thinking about Access, not Excel? In Excel you can link shells in one worksheet with cells in another worksheet and the update of the values is automatic.

In Access, if you want something like this to happen automatically you will need to do some serious program coding. If all you want is to allow the user to select the data from table A on db1 when the user enters data into table B on db2, you can achieve it with relative ease.

However, instead of storing the same data into two databases, you should just look up the data from the table A in the main database db1 and store the key as a foreign key to the table B in database db2. This is a very important principle in database design, it avoids duplicating the data. If the same data is stored in two places, it can easily get out of synch.

All you need to store in table B in db2 is the key to the row in table A in db1, not the values of each column or field. When you do your reports from table B, you include a linked version of table A and the report generator allows you to select the associated field from table A.

To set up a column to select values from the other database db1, you need to have a linked table in your db2 database, pointing to table A on db1.

You create the linked table in db2 by selecting File - Get External Data - Link Tables. Then select the db1 database and the table A. Once the linked table exists in the db2 database, you can go to table B in Design View, go to Data Type in each column and select Lookup Wizard and design the lookup, using the linked table to get the data from.

If you need to modify something, you go to Tools - Linked Tables Manager.

Collapse -

by Toivo Talikka In reply to

In that case you can just export (File - Export) the contents of the table A in db1 into a comma delimited text file and import (File - Get External Data - Import) the text file into db2, then select table B.

Make a backup copy of the database db2 first, just in case.

Collapse -

by ttosun In reply to Access 2003 General Quest ...

Wow! Thanks for being very detailed. However, your explanation is a little over my head. Let me rephrase my question.

I need to export FirstName, LastName and Email column information from db1 table 1 to FistName, LastName Email columns on db2 table 2. I could do this manually but is there a way to, i guess, copy and paste from one database column to another database column?

Collapse -

by Ldyosng In reply to Access 2003 General Quest ...

Do you need to replace info or simply append? To append, create a query in design view, opening the target table. Select your target field. Next, from the drop down select Query>append query. Make sure to designate that you want to append from another database. Now select the source table. Next, select the appropriate source field (the field name difference doesn't matter) in the body of the query grid. When you hit the exclamation point to run the query, you will get a record count. If it's the right number, say yes, and the deal is done.

Related Discussions

Related Forums