SQL Backend recordset error - 3622 and 3251 1

[mystuff]

Hi,

I have code that works fine in Access 2002/2003 with an Access backend. But when I changed the Back-end to SQL, I am getting errors. Here is my original code:

Dim rstSchedule As Recordset
'Add user to schedule
Set db = CurrentDb()
Set rstSchedule = db.OpenRecordset("tblMigrationSchedule", dbOpenDynaset)

With rstSchedule
.AddNew
!WorkstationID = Forms![frmWorkstation]![WorkstationID]
!MigrationDate = calPickADay.Value
.Update
End With

When I changed the backend to SQL, I get this error:

3622 – You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.



Then I changed the code to:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "tblMigrationSchedule", CurrentProject.Connection, , , adCmdTable

With rs
.AddNew
!WorkstationID = Forms![frmWorkstation]![WorkstationID]
!MigrationDate = calPickADay.Value
.Update
End With

But now I am getting the following error message on the .AddNew line:

3251 – Current Recordset does not support updating. This may be a limitation to the provider, or of the selected locktype.




Does anyone have any idea what I need to do to get this code working with an SQL backend?

Thanks in advance!

 

[RoyVidar]

Did you try with dbSeeChanges?

[tt]Set rstSchedule = db.OpenRecordset("tblMigrationSchedule", dbOpenDynaset, dbSeeChanges )[/tt]

ADO, did you try something like this

[tt] Set rs = New ADODB.Recordset
with rs
set .activeconnection = CurrentProject.Connection
.locktype = adlockoptimistic
.cursortype = adopenstatic ' or keyset?
.Open "tblMigrationSchedule", , , , adCmdTable
.addnew ' ... [/tt]

- or try a query?

Roy-Vidar