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:
Code:
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')
Code:
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.
dBforums
>
Adding fields to a linked table
12-21-11, 06:21
Linear Mode
