Solved

dbSeeChanges error while using OpenrecordSet

Posted on 2004-08-28
3
416 Views
Last Modified: 2012-08-13
Okay.  After I added the code from http://support.microsoft.com/?kbid=197526 and ommiting what I didn't need.  I now receive the following error:

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

Here is what the code looks like.  I commented out what I didn't need instead of just deleting it.  Also the Table Building is linked from an SQL 2000 backend.

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_Building_NotInList

    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Confirm that the user wants to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        ' If the user chose not to add a customer, set the Response
        ' argument to suppress an error message and undo changes.
        Response = acDataErrContinue
        ' Display a customized message.
        MsgBox "Please try again."
    Else
        ' If the user chose to add a new customer, open a recordset
        ' using the Customers table.
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("Building", dbOpenDynaset)

        ' Ask the user to input a new Customer ID.
        ' Msg = "Please enter a unique 5-character" & vbCr & "Customer ID."
        ' NewID = InputBox(Msg)
        ' Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)
        ' If the NewID already exists, ask for another new unique
        ' CustomerID
        ' Do Until Rs.NoMatch
        '   NewID = InputBox("Customer ID " & NewID & " already exists." & _
        '            vbCr & vbCr & Msg, NewID & " Already Exists")
        '   Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)
        ' Loop
        ' Create a new record.
        Rs.AddNew
        ' Assign the NewID to the CustomerID field.
        ' Rs![CustomerID] = NewID
        ' Assign the NewData argument to the CompanyName field.
        Rs![Building] = NewData
        ' Save the record.
        Rs.Update

        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded

    End If

Exit_Building_NotInList:
       Exit Sub
Err_Building_NotInList:
       ' An unexpected error occurred, display the normal error message.
       MsgBox Err.Description
       ' Set the Response argument to suppress an error message and undo
       ' changes.
       Response = acDataErrContinue

 End Sub
0
Comment
Question by:John Sheehy
[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
  • 2
3 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 11923061
Just change this line:
Set Rs = Db.OpenRecordset("Building", dbOpenDynaset)

to read:
Set Rs = Db.OpenRecordset("Building", dbOpenDynaset, dbSeeChanges)

That should resolve your problem (you need to include the dbSeeChanges when you are opening a recordset from an SQL Server linked table).
0
 

Author Comment

by:John Sheehy
ID: 11923070
That is awsome.  Thanks.

John
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11923106
No problem, glad I could help!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 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