General discussion

  • Creator
    Topic
  • #2188905

    Access 2003 General Question

    Locked

    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.

All Comments

  • Author
    Replies
    • #3046639

      Reply To: Access 2003 General Question

      by toivo talikka ·

      In reply to Access 2003 General Question

      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.

      • #3045325

        Reply To: Access 2003 General Question

        by toivo talikka ·

        In reply to Reply To: Access 2003 General Question

        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.

    • #3046429

      Reply To: Access 2003 General Question

      by ttosun ·

      In reply to Access 2003 General Question

      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?

    • #3044370

      Reply To: Access 2003 General Question

      by ldyosng ·

      In reply to Access 2003 General Question

      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.

Viewing 2 reply threads