Hi All,
I've been lurking these forums for a very long time, but I finally have an issue that I cannot resolve with what is already available. I've been programming access VBA for about 14 years, but I simply have no clue with this one.
As the title suggests, I have an Access frontend with a SQL Server backend. This database has about 50 tables and has been working reliably for approximately 2 years. There are three main tables. UnitLot, which contains information about physical space, Starts, which has information about who is building the home and when, and OrderProcess, which defines which home designer will design the home and tracks their progress through the home ordering process.
Previously, before refreshing tables, I was able to do an inner join on these three tables and do small data changes. Whenever I try to change data now, I get the "This Recordset is Not Updateable" message. Likewise, If I try to use VBA to open a recordset and change data with these 3 tables, I get the same issue. Interestingly enough, I am able to write an UPDATE query that joins these 3 tables and use information from the UnitLot table in the WHERE clause to update data in the OrderProcess table. This makes me believe this may be an issue with Access, not SQL server.
What I've tried so far:
1.) Refreshed/relinked tables.
2.) Verified primary and foreign keys in SQL server.
3.) Verified Access identifies the primary keys as such.
4.) Defined the table relationships in the Access relationship manager.
5.) Removed a calculated field in the UnitLot table.
6.) Compact/Repair.
Other observations:
1.) Access recognized both relationships as 1 to 1.
2.) All tables are in the same datasource.
3.) I am able to update these tables when only 2 are involved in the query.
4.) Update queries work, so I don't think this is permissions related.
Is there anything else I am missing? It seems to me like this could require a change in the SQL server because of some limitation of Access that I am unaware of.
Thanks,
Ryan
I've been lurking these forums for a very long time, but I finally have an issue that I cannot resolve with what is already available. I've been programming access VBA for about 14 years, but I simply have no clue with this one.
As the title suggests, I have an Access frontend with a SQL Server backend. This database has about 50 tables and has been working reliably for approximately 2 years. There are three main tables. UnitLot, which contains information about physical space, Starts, which has information about who is building the home and when, and OrderProcess, which defines which home designer will design the home and tracks their progress through the home ordering process.
Previously, before refreshing tables, I was able to do an inner join on these three tables and do small data changes. Whenever I try to change data now, I get the "This Recordset is Not Updateable" message. Likewise, If I try to use VBA to open a recordset and change data with these 3 tables, I get the same issue. Interestingly enough, I am able to write an UPDATE query that joins these 3 tables and use information from the UnitLot table in the WHERE clause to update data in the OrderProcess table. This makes me believe this may be an issue with Access, not SQL server.
What I've tried so far:
1.) Refreshed/relinked tables.
2.) Verified primary and foreign keys in SQL server.
3.) Verified Access identifies the primary keys as such.
4.) Defined the table relationships in the Access relationship manager.
5.) Removed a calculated field in the UnitLot table.
6.) Compact/Repair.
Other observations:
1.) Access recognized both relationships as 1 to 1.
2.) All tables are in the same datasource.
3.) I am able to update these tables when only 2 are involved in the query.
4.) Update queries work, so I don't think this is permissions related.
Is there anything else I am missing? It seems to me like this could require a change in the SQL server because of some limitation of Access that I am unaware of.
Thanks,
Ryan