Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    38

    Unanswered: Run-time 3622 error

    We have recently upgraded fron Access 97 to Access XP and upgrading to SQL Server 2000 as the backend.
    I have a form that will allow the user to add a new employer name if not on the list but when the user clicks update I get the following error: Run-Time 3622 "You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column."
    Any help will be greatly appreciated.

    Here is the code:

    Private Sub ComboEmpName_NotInList(NewData As String, Response As Integer)

    Dim db As Database
    Dim rs As Recordset
    Dim Msg As String
    Dim CR As String

    CR = Chr$(13)

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

    ' Confirm that the user wants to add the new Organization.
    Msg = "'" & NewData & "' is not in the Organization/Employer list." & CR & CR
    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 Organization, open a recordset
    ' using the Organizations/Employers table.
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Organizations/Employers")
    ' Let code execution continue if a run-time error occurs.
    On Error Resume Next
    ' Create a new record.
    rs.AddNew
    ' Assign the NewData argument to the OrganizationName field.
    rs![OrganizationName] = NewData
    ' Save the record.
    rs.Update

    If Err Then
    ' If a run-time error occurred while attempting to add a new
    ' record, set the Response argument to suppress an error
    ' message and undo changes.
    Response = acDataErrContinue
    ' Display a customized message.
    MsgBox Error$ & CR & CR & "Please try again.", vbExclamation
    Else
    ' If a run-time error did not occur, set Response argument
    ' to indicate that new data is being added.
    Response = acDataErrAdded
    End If

    End If

    End Sub

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    A couple of things

    A couple of things to note when upgrading from MSAccess 97 to MSAccess XP and SQL Server 2000:

    1. If you have BOTH MSAccess 97 and MSAccess XP on the same machine, make sure when you import a form/report/module, that you IMMEDIATELY debug after importing otherwise you corrupt the form and will have to open the database in a /decompile state. I found that this only happens when both versions are on the same machine but once we re-installed with just XP, we didn't have any problems (try it out on a test db by importing and then changing code without debugging and then debug later - it may save you some headaches).
    2. Are you linking in the SQL Server tables to MSAccess? If so, make sure that the table has a primary key and that you check the "save password" checkbox when linking in the table.
    3. Think about adding in a TimeStamp field in the SQL Server table. This seems to help with some of the problems we were having with tables that had a memo field. I'm not sure why, but this solved some "mystery" type problems.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2003
    Posts
    38
    The machines are only running one version which is Office XP and there are tables linked from SQL server. The field I would like for the user to update/add to the list is actually the primary key in the SQL table (field name is OrganizationName). Are there any other suggestions.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    TimeStamp

    If this code worked fine in 97, I'd try adding the TimeStamp field and refresh the linked tables (if any). It's worth a shot (and I seem to recall that adding the Timestamp field was related to the dbseechanges error). Just call the field anything you want and set the data type to TimeStamp. Otherwise, you can try this code if you want (it uses ADO though which I prefer) - I also noticed that in the DAO code you had it didn't close the recordset anywhere (it's been a while since I've used DAO but I wasn't sure and I don't think that's what's causing the error you got):

    Private Sub ComboEmpName_NotInList(NewData As String, Response As Integer)

    dim rs as adodb.recordset
    dim Msg as string
    dim CR as string

    CR = Chr$(13)

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

    ' Confirm that the user wants to add the new Organization.
    Msg = "'" & NewData & "' is not in the Organization/Employer list." & CR & CR
    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 Organization, open a recordset
    ' using the Organizations/Employers table.

    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from Organizations/Employers"
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic

    On Error Resume Next
    ' Create a new record.
    rs.AddNew
    ' Assign the NewData argument to the OrganizationName field.

    rs!OrganizationName = NewData

    ' Save the record.
    rs.Update
    rs.close
    set rs = nothing

    If Err Then
    ' If a run-time error occurred while attempting to add a new
    ' record, set the Response argument to suppress an error
    ' message and undo changes.
    Response = acDataErrContinue
    ' Display a customized message.
    MsgBox Error$ & CR & CR & "Please try again.", vbExclamation
    Else
    ' If a run-time error did not occur, set Response argument
    ' to indicate that new data is being added.
    Response = acDataErrAdded
    End If

    End If
    Last edited by pkstormy; 01-11-06 at 21:53.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •