Solved

dbSeeChanges error while using OpenrecordSet

Posted on 2004-08-28
3
413 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
  • 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now