Link to home
Start Free TrialLog in
Avatar of D J
D JFlag for United States of America

asked on

MS Access Why isn't the relationship ID being copied when an input is made?

When I make an input the relationship ID (dbo_dd_taskinfo.ti_id) is not being copied to my input table ID (Matrix_SQL.TaskInfoID) resulting in input data not displaying.

dbo_dd_taskinfo = linked SQL table (no data input here)
Matrix_SQL = Access table (for data input)

MS Access SQL statement:
SELECT dbo_dd_taskinfo.*, dbo_dd_taskinfo.ti_tpe, dbo_dd_taskinfo.ti_class, Matrix_SQL.Status, Matrix_SQL.[P 676], Matrix_SQL.[P 677], Matrix_SQL.[P 678], Matrix_SQL.[P 679], Matrix_SQL.[P 680], Matrix_SQL.[P 701], Matrix_SQL.[P 702], dbo_dd_taskinfo.ti_task, dbo_dd_taskinfo.ti_num
FROM dbo_dd_taskinfo LEFT JOIN Matrix_SQL ON dbo_dd_taskinfo.ti_id = Matrix_SQL.TaskInfoID
WHERE (((dbo_dd_taskinfo.ti_tpe)="P") AND ((dbo_dd_taskinfo.ti_class)="676") AND ((dbo_dd_taskinfo.ti_task)="S/A" Or (dbo_dd_taskinfo.ti_task)="CI") AND ((dbo_dd_taskinfo.ti_num)>"0100"));
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

I've got to ask, what made you expect that the relationship be copied if you made an input to the other table?
Avatar of D J

ASKER

I'm sure this was working in a previous version of Access. (prior to 2010)
I guess the bigger question is how can I maintain a relationship of 2 tables in this type of environment?
It never worked the way you seem to think it does.  How would the SQL Server "KNOW" about some random Access database on some random computer somewhere?  Your Access app could force the import but it would have to "KNOW" when to do it and what to do and that is entirely in your hands.

What is the purpose of copying linked data?  I have a number of apps that interact with data warehouse databases.  I can't update the SQL tables but I have local tables that I do update and they need to join to the SQL Server tables.  As long as the linked tables have a primary key, I can join to those tables and update my local tables.
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of D J

ASKER

What is the purpose of copying linked data?
Pat,
The linked data is the relationship aspect table1.KeyID to table2.table1KeyID
I understand my method may be incorrect - how can I join the SQL link table to my local Access table?
Avatar of D J

ASKER

two Access tables with a Master/Child relationship
Rey,
I'm trying to create this in datasheet view - the only method is to create a recordset?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial