Error 3622 Open SQL Server Table with Identity Column

[dowlingm815]

I'm having issues attempting to open a Microsoft SQL table, where the same code has been used in the past without any issues. The error is "Error 3622 Open SQL Server Table with Identity Column." Any assistance would be greatly appreciated.

    Dim dbs As DAO.Database
    Dim rst_AllText_Credits As DAO.Recordset
    Dim rst_AllText_SQL_Format_Compare_SQL_Table_Credit_Memo  As DAO.Recordset
    Set dbs = CurrentDb()
    
    Set rst_AllText_Credits = CurrentDb.OpenRecordset("Credit_Memo")
    rst_AllText_Credits.MoveFirst
    
    If rst_AllText_Credits.EOF = False Then
        rst_AllText_Credits.Close
    
    ''' Else
  
    ' make sure the all text credits are within the sql marketplace database credit table
        Call sql_mke_tbl_Credit_Memo_sql_format
        Call sql_Match_Credits_to_SQL
        
        Set rst_AllText_SQL_Format_Compare_SQL_Table_Credit_Memo = CurrentDb.OpenRecordset("sql_Match_Credits_to_SQL")
        rst_AllText_SQL_Format_Compare_SQL_Table_Credit_Memo.MoveFirst
        If rst_AllText_SQL_Format_Compare_SQL_Table_Credit_Memo.EOF = True Then
            'delete all text recs
            '    Call sql_Delete_Credit_Memo_Records
            '    DoCmd.SetWarnings (False)
            '        DoCmd.OpenQuery "sql_Delete_Credit_Memo_Records", acViewNormal, acEdit
            '    DoCmd.SetWarnings (True)
        Else
            ' gets reprocess to sql
        End If
        rst_AllText_SQL_Format_Compare_SQL_Table_Credit_Memo.Close
    End If

[zmbd]

Something sounds glitched in the frontend.
Open a new blank frontend.
Copy all of your objects/code over to the new front end.
Re-create your linked tables (if any).
(^_^)

[jforbes]

I’ve run into this after moving tables out of MS Access and into SQL, then using ODBC to connect to the moved table. Here is a link to Microsoft’s KB Article that describes the error you are getting:

http://support.microsoft.com/kb/190620

It’s saying you need to tweak your openRecordset Statements and your Execute statements to use dbSeeChanges as an option:

6.     Set rst_AllText_Credits = CurrentDb.OpenRecordset("Credit_Memo")

To

6.     Set rst_AllText_Credits = CurrentDb.OpenRecordset("Credit_Memo" , dbOpenDynaset, dbSeeChanges)

Again, you’ll probably need to look for Execute statements and update them also. This is from some code in house, but it was changed from:

dbLocal.Execute sSQL, dbFailOnError

To

dbLocal.Execute sSQL, dbFailOnError + dbSeeChanges

[zmbd]

That was my intial thought; however, as noted there's usually an execute statement involved and I'm seeing that in the posted code and dowlingm85 stated that the code has been working in the past; hence my suggestion to rebuild the frontend.

dowlingm815
Place a STOP command between lines 3 and 4.
Once the debugger starts, [F8] thru the code and let us know when the error triggers.

I am still in the your-front-end-is-tweeked-camp.

Please let us know what works.