Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011

    Unanswered: Adding fields to a linked table

    Hello All
    I'm in need of some help in designing a solution to my problem.

    What i want to do is take some data from a ERP system into Access (by way of linked tables and queries) so that I have a view of all the relevant transactiona data.

    I then need to add additional fields onto this data. These fields will be manually populated and need a user friendly front end (e.g. excel) for this task.

    I then want to have a table of data (transactional and manually entered) for each record that I can then link into other tables on the ERP database (probably via access linked tables) and build reports in excel.

    The tools I have are Office (i.e. Access, Excel etc).

    Any help or suggestions would be greatly appreciated.



  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    Here's a solution. In my example, the source table 'dbo_CF_Data' is a linked (attached) table in an Access database (the original data reside on a SQL Server). The Identity column (primary key) for this table is named 'SysCounter' (Long numeric, seen as "AutoNumber" in Access)

    In the same Access database, we create a (local) table: 'Tbl_dbo_CF_Data_Extra' which also have a column named 'SysCounter' of type Number (Long Integer), Indexed: Yes (No Duplicates). A second column 'Additional_Column' will be used to store the additional data for the source table.

    The evident solution to link both tables would be to create a relationship and to enforce referencial integrity. Unfortunately, this is not possible for a linked table. We then need queries to synchronize the rows of both tables (i.e. to always have one and only one row in the local table for each row in the attached table).

    The first query ('Insert_Into_Tbl_dbo_CF_Data_Extra') will be used to create new rows into the local table:
    INSERT INTO Tbl_dbo_CF_Data_Extra ( SysCounter )
         SELECT dbo_CF_DATA.SysCounter
         FROM   dbo_CF_DATA LEFT JOIN 
                Tbl_dbo_CF_Data_Extra ON dbo_CF_DATA.SysCounter=Tbl_dbo_CF_Data_Extra.SysCounter
         WHERE  Tbl_dbo_CF_Data_Extra.SysCounter Is Null;
    The second query ('Delete_From_Tbl_dbo_CF_Data_Extra') will remove the rows in the local table ('Tbl_dbo_CF_Data_Extra') that no longer exist in the source table ('dbo_CF_DATA')
    DELETE FROM Tbl_dbo_CF_Data_Extra
    WHERE Tbl_dbo_CF_Data_Extra.SysCounter IN (
          SELECT Tbl_dbo_CF_Data_Extra.SysCounter
          FROM   Tbl_dbo_CF_Data_Extra LEFT JOIN 
                 dbo_CF_DATA ON Tbl_dbo_CF_Data_Extra.SysCounter = dbo_CF_DATA.SysCounter
           WHERE dbo_CF_DATA.SysCounter Is Null
    Depending on how often rows are added or deleted in the source table, you can chose to run both queries when the database is open ('AutoExec' macro), and/or use a timer to periodically "refresh" the local table.
    Have a nice day!

Tags for this Thread

Posting Permissions

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