Updateable Recordset from SQL Server stored procedures using ADO and variables
Posted on 2004-09-11
I've ran into an issue and hope someone can shed some light on the subject.
Here are the basics
Access 2003 (mdb not adp)
SQL Server 2000
Windows 2003 Advanced Server (OS)
I've created a stored procedure that uses variables to create the pieces of the SQL statement and then the exec command to retrieve the records. I did this because of the 10 different possibilities within the where clause.
Here is what the final line of the stored procedure looks like
exec (@strSELECT + @strFROM + @strWHERE + ' ORDER BY d.DateCheckReceived')
A few other notes about the stored procedue. It contains a left join to a derived table and I aliased all the tables for readability.
The stored procedure works like a champ and is oh so fast. The users would just love it. Except for one problem.
Here is the problem. It's not updateable and I'm trying to figure out why.
I've done this
Set the UniqueTable property of the form to the name of the table that contains the fields that I want to update.
I've added all the PK's to the returned field list for all tables in the stored proc
The recordset is opened wit the following properties set, adopenkeyset, lock optimistic, and I've tried use server cursor location and use client location.
I'm thinking that because I'm using the EXEC to execute the SQL it's being flagged as read-only. Or because my from clause contains a left join to a derived table, it's read-only.
Any takers.......I would like to resolve this tonight.
I've search EE, but haven't found a question that answers my question.
Thanks in advance,