I have this problem: I'm using a SQL Server 2008R2 backend and MS Access 2000 frontend where some tables are connected via ODBC.
Following Structure (Tables all on SQL-Server):
- Import (not connected to Access)
- Products (connected via ODBC to Access)
- Pricing (connected via ODBC to Access)
I want to fill the Pricing table automatically with some data from Products and Import. This is supposed to run as a SQL Agent job with a T-SQL script.
I want to insert the data from "Products" with following command:
INSERT INTO Pricing (Productnr, Manufacturernr)
(SELECT Productnr, Manufacturernr
FROM Products
WHERE Valid = 1
AND Productnr NOT IN (SELECT Productnr FROM Pricing ));
Right after that the inserted rows are locked for Access, I can't change anything. If I execute sql queries with SQL Server Management Suite or if i start queries as SQL Agent jobs everything works fine.
Why are the rows locked in ms access after the query ran (even if it finished successfully)? and how can I unlock them or make it unlock itself right after the query/job ran?
Thanks
When SQL Server inserts new rows, those new rows are in fact exclusively locked to prevent other transactions from reading or manipulating them - that's by design, and it's a good thing! And it's something you cannot change - you cannot insert without those locks.
You can unlock them by committing the transaction that they're being inserted under - once they're committed to SQL Server, you can access them again normally.
The error message i get says, that the dataset has been changed by another user and if i save it, i would undo the changes of the other user. (and asks me for copying into clipboard).
This is different from "locked", and completely normal.
If you have a ODBC linked table (or form based on the table) open, and change data in the backend, Access doesn't know about the change.
You need to do a full requery (Shift+F9) in Access to reload the data, afterwards all records can be edited again.
Got the solution for my Problem now.
I had to add a timestamp row into the pricing table, so access could recognize the change.
Access loads the data into the front end when the table is first accessed. If something in the backend changes the data, you need Access to refresh it first, before you can edit it from the front end (or see the changes).
Do this by (in Access) by closing and reopening the table, or switching to the table and pressing shift-F9 as Andre suggested, or programmatically using a requery statement. You must requery, not refresh, for it to release the locks and register the changes made in SQL.
