D J
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"));
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)
I've got to ask, what made you expect that the relationship be copied if you made an input to the other table?
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?
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
ASKER
two Access tables with a Master/Child relationshipRey,
I'm trying to create this in datasheet view - the only method is to create a recordset?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.