Access 2003 - SQL Sever 2005 Issue with IDENTITY field

Hi All,

Below is the layout of one of my functions and I am just after connecting my FrontEnd Access database with back end SQl Sever Database and I get the following error when I try to run a particalar command on my main form.

Error: Run-time error '3622' - "You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Sever that has an IDENTITY column"

I check the main table it was pulling the informaton from tblCompany and the CompanyID does have IDENTITY as a datatype.

Here is my function - the error occurs when i set record to a recordset(query).

Has anyone got any ideas? Thanks again in advance for all the help.

Private Sub Form_Current()

    Dim query As String
    Dim Records As Object
    Dim Items As String

    query = "SELECT crpProducts.crpProduct_ID, crpProducts.crpProduct_Description, tblCompany.CompanyName, crpLicenseMethods.crpLicenseMethod_Description " & _
      "FROM tblCompany INNER JOIN crpProducts ON tblCompany.CompanyID = crpProducts.crpProduct_CompanyID, crpLicenseMethods " & _
      "WHERE crpProducts.crpProduct_AssociatedProductID=" & crpProduct_ID & " " & _
      "AND tblCompany.CompanyID = crpProducts.crpProduct_CompanyID " & _
      "AND crpLicenseMethods.crpLicenseMethod_ID = crpProducts.crpProduct_crpLicenseMethodID " & _
      "AND crpProducts.crpProduct_crpLicenseMethodID <> 1 " & _
      "ORDER BY tblCompany.CompanyName,crpProducts.crpProduct_Description;"
    Set Records = CurrentDb.OpenRecordset(query)

    txtProductList.Locked = False
    If Records.RecordCount > 0 Then
            If Not Items = "" Then Items = Items & Chr(13) & Chr(10) & Chr(10)
            Items = Items & Records("CompanyName") & Chr(13) & Chr(10) & _
            Space(5) & Records("crpProduct_Description") & Chr(13) & Chr(10) & _
            Space(5) & "(" & Records("crpLicenseMethod_Description") & ")"
        Loop Until Records.EOF
        txtProductList.Text = Items
        txtProductList.Text = "No other products are associated with this product."
    End If
    txtProductList.Locked = True

End Sub
Who is Participating?
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
It's asking you to open the recordset thusly

Set Records = CurrentDb.OpenRecordset(query, dbOpenDynaset, dbSeeChanges)
'----------------------------------------------------------^^^^^ might just need to be dbOpenSnapsot as you don't make edits
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.