CODEWITHSUNDEEP
vbams-accessrecordset
Phil Anderson
Phil Anderson

Reputation: 21

invalid operation when opening a record set

I am having a problem opening a recordset against a table that is linked from SQLServer. I am running the following VBA and receiving an "invalid operation" error. Thanks for the help.

Private Sub Add_Click()
On Error GoTo Err_Add_Click
Dim myRS As DAO.Recordset
Dim myDB As DAO.Database
Dim myI As Variant, myPO As Variant, myJ As Variant
Dim myWS As Workspace

    Set myWS = DBEngine(0)
     
    Set myDB = CurrentDb
   
    Set myRS = myDB.OpenRecordset("PO_Numbers_tbl", dbOpenTable)
    
    myI = Me![End] - Me![Start] + 1

    myPO = Me![Start]
    For myJ = 1 To myI
        myRS.AddNew
        myRS![PO_Number] = myPO
        myRS.Update
        myPO = myPO + 1
    Next myJ
    
    myRS.Close
    myDB.Close
    Me.Requery
    
Exit_Add_Click:
    Exit Sub

Err_Add_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_Add_Click
    
End Sub

Upvotes: 2

Views: 533

Answers (1)

Applecore
Applecore

Reputation: 4099

The problem is that you are using dbOpenTable as the Recordset type, which is only valid for native Access tables. When using SQL Server tables, try using dbOpenDynaset.

I prefer to leave my linked SQL server tables named "dbo_" so that I know when they are not an Access table.

Regards,

Upvotes: 1

Related Questions