[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

dbSeeChanges error while using OpenrecordSet

Posted on 2004-08-28
3
Medium Priority
?
421 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

656 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