Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access 2003 - SQL Sever 2005 Issue with IDENTITY field

Posted on 2006-11-16
1
Medium Priority
?
327 Views
Last Modified: 2012-08-13
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
    txtProductList.SetFocus
    If Records.RecordCount > 0 Then
        Records.MoveFirst
        Do
            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") & ")"
            Records.MoveNext
        Loop Until Records.EOF
        txtProductList.Text = Items
    Else
        txtProductList.Text = "No other products are associated with this product."
    End If
    txtProductList.Locked = True

End Sub
0
Comment
Question by:sabrina_spillane
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 17962363
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question