Run-time error 3622

[Hylke]

Hi All,

I have created an access databasa with linked tables on a ODBC database.

I wrote some VB code and I get a runtime error 3622: "You must use the dbSeeChanges option with OpenRecordSet when accessign a SQL server table that has an IDENTITY column".

The code is the following:

Private Sub Command39_Click()
Dim frmDate, frmAmount As String

inputDateSrc = InputBox("Insert the Date of Payment", "Payment Date")

Dim MyDB As DAO.Database, MyRec As DAO.Recordset, MyList As String
Dim PaymentStatus As String
Dim sql As String
DoCmd.SetWarnings False
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset("SELECT * FROM dbo_dublin_Payments WHERE Selected=-1 AND PaymentStatusID<>3 AND PaymentStatusID<>5 AND PaymentStatusID<>8 AND PaymentStatusID<>10")
While Not MyRec.EOF ' Loop trough the table
If MyRec![PaymentStatusID] = 6 Or MyRec![PaymentStatusID] = 9 Then
MsgBox "Payments on hold or on query cannot be selected", vbOKOnly, "Error"
DoCmd.RunSQL "UPDATE dbo_dublin_Payments SET Selected=0 WHERE PrimaryKey=" & MyRec![PrimaryKey]
End If
If MyRec![PaymentStatusID] = 4 Then
'frmAmount = "inputAmount"
inputAmountSrc = InputBox("Insert the Amount of the Payment", "Payment Amount")
'DoCmd.OpenForm frmAmount, acNormal, , , , acDialog
If MyRec![Amount] = Val(inputAmountSrc.Value) Then
PaymentStatus = ",PaymentStatusID=3"
Else
PaymentStatus = ""
End If
DoCmd.RunSQL "UPDATE dbo_dublin_payments SET PaidAmount=" & inputAmountSrc.Value & ",PaymentDate=#" & inputDateSrc.Value & "#" & PaymentStatus & " WHERE PrimaryKey=" & MyRec![PrimaryKey]
End If

If MyRec![PaymentStatusID] = 1 Or MyRec![PaymentStatusID] = 2 Or MyRec![PaymentStatusID] = 7 Then
PaymentStatus = ",PaymentStatusID=3"

sql = "UPDATE dbo_dublin_Payments SET PaidAmount=" & MyRec![Amount] & ",PaymentDate=#" & inputDateSrc.Value & "#" & PaymentStatus & " WHERE PrimaryKey=" & MyRec![PrimaryKey]
DoCmd.RunSQL sql

PaymentStatus = ""
End If

MyRec.MoveNext
Wend
DoCmd.SetWarnings True

' use you code here with the mail list ceated

MyRec.Close
MyDB.Close
Me.Requery
End SubCan somebody please help? It is driving me insane.

Thanks,
Hylke

[Hack]

Welcome to DevX :WAVE:

The error you posted makes no sense. You are using an Access database (which does not support IDENTITY columns) and the error is referencing an SQL Server database, which does support IDENTITY columns.

I'm confused. Are you using Access or SQL Server?

[Hylke]

Hi Hack,

Sorry for the confusion. I am using an SQL database for the tables. In Access, where I have all my linked tables, I created all my queries, forms and reports.

As you can see I am a beginner.

Thanks,
Hylke

[Hack]

Ahhhh....gotcha.

Ok, is this the line of code that is generating the errorSet MyRec = MyDB.OpenRecordset("SELECT * FROM dbo_dublin_Payments WHERE Selected=-1 AND PaymentStatusID<>3 AND PaymentStatusID<>5 AND PaymentStatusID<>8 AND PaymentStatusID<>10")

[Hylke]

Ok, thanks. ...Can you also tell me what should I change?

[Hack]

Well, I'm guessing that is, in fact, the line.

What are the field types for Selected and PaymentStatusID?

[Hylke]

Selected is type BIT (that access change in to a yes/no field) and PaymentStatusID's type is INT (that access changes into a number field.)

[Hack]

In rereading your first post, it would seem as though it doesn't like something you are doing with your IDENTITY field in SQL Server. Which field is that?