Unanswered: Access 2000, SQL Server, transactions and recordsets
I have a Access 97 database with a SQL Server backend. It uses the following code, which works. However, when the database is converted to Access 2000, the folowing code does not work.
The code is on a bound form, using a stored Access query, which is returning am Integer, Date/Time and Boolean fields. If I open the query manually, I can add records. The error occurs on the AddNew method, with 'The action was cancelled by an associated object' message.
The problem remains if I use Me.Recordset rather then Me.RecordsetClone. I have even tried putting the table in Access with no luck. I do not know how significant the VBA changes are from Access 97 to Access 2000, and have not really dealt iwth SQL Server backends and transactions before.
Your quick response and help would be very much appreciated.
Dim rst As Recordset
Dim wsp As Workspace
Set wsp = DBEngine.Workspaces(0)
Set rst = Me.RecordsetClone
The default recordset for 97 is DAO, 2000 uses ADO. Check your references to ensure you're using the right library. Alternately, try prefacing your recordset call with DAO. If it doesn't work, you have a reference issue.