+ Reply to Thread
Results 1 to 8 of 8

Update and Append Entire table in Access

  1. #1
    Registered User
    Join Date
    05-08-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Update and Append Entire table in Access

    hello
    I’m looking for help updating a local table in access using a linked table in the same DB called ALLELEMENTS linked from Excel. Really the DB works fine using the linked table because I’m mainly use it as a dashboard and for reporting but I also need to add info to the records so I made a copy of ALLELEMENTS and added the fields I needed.

    I can’t figure out how to –
    -Get the local table that includes a couple of new fields updated with new the new data (new records and updated sales figures, dates etc.) from the linked table.
    - Can I make a relationship between the linked table and the local?
    I only have 3 tables In my database
    tbl ALLELEMENTS (linked)
    tbl ALLELEMENTS2 –same as ALLELEMENTS with a notes field
    tblSALESREP

    all the forms and reports will be based off of queries from ALLELEMENTS2

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Update and Append Entire table in Access

    Why have two tables with the same information? This is contrary to normalization rules for RDBMS. If I were doing this, I would abandon the local table, make sure the notes field is in the linked table (or create a notes table and join it to the ALLELEMENTS linked table on a common field) and use the linked table for queries, forms and reports. This is cleaner and no chance of missing anything between the tables.

    If you need to understand normalization, here is a link that will help in designing databases.

    http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-08-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Update and Append Entire table in Access

    Ok so I should keep my linked table, Make a 2nd table for the notes where the primary key is the same as the Primary key in the linked table. Join the tables with a relationship.
    If I join a linked table to a local, will the new and updated data from the linked sheet follow into to local? I update the excel data almost every day.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Update and Append Entire table in Access

    Your linked table appears in Access just as if it were a local table. Any relationships that table has with other tables in the database will work as if the linked table were a local table in the database. In you notes table, you will need a primary key for each record and a foreign key which will be used to join the notes table to your excel linked table.

    The link I provide you will explain about primary keys and foreign keys. It is a very important aspect of database relationships.

  5. #5
    Registered User
    Join Date
    05-08-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Update and Append Entire table in Access

    Ok that makes sense.
    One more question - The linked table updates everyday with new records. Will I be able to run append query to add the records to the notes table

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Update and Append Entire table in Access

    Why not. Are the notes not in the excel table that is linking? Where are the notes maintained now?

  7. #7
    Registered User
    Join Date
    05-08-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Update and Append Entire table in Access

    The notes aren't in the linked excel table, so they aren't in the linked access table. I don't want to add the notes into the linked excel table because its very large and I want to use the DB for easy data entry. The notes will have to be its own sheet. That will be the Order#, Order Name (fields from the linked table) and the new notes field plus a couple others. When new records are added to the linked table, I will need to get the new Order#, Order Name into the "notes" table, I need to know how to do this. Also the Order#, Order Name will never change.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Update and Append Entire table in Access

    Suggest you build a form that is bound to the linked table. Build a subform that is bound to the Notes table. Insert the subform into the main form, joining them on a common field in the tables. Enter you Notes data into the subform on the main form.

    http://office.microsoft.com/en-us/ac...101872705.aspx

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Access VBA or SQL to add all records from a table to another table (Append)
    By ewong in forum Access Programming / VBA / Macros
    Replies: 3
    Last Post: 09-14-2012, 06:49 PM
  2. Update & Append Access Table Using Excel Macro - ADO
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2010, 04:49 AM
  3. Problems Populating Null Table-Field Values With an Update or Append Query
    By NSTurk725 in forum Access Tables & Databases
    Replies: 3
    Last Post: 06-11-2009, 02:47 AM
  4. How do I Append excel data to a Access database table in a excel macro?
    By Pete in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2005, 06:20 PM
  5. [SOLVED] How do I Append excel data to a Access database table in a excel macro?
    By Pete in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2005, 06:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1