Solved

dbSeeChanges error while using OpenrecordSet

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

776 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