CODEWITHSUNDEEP
sql-serverms-accessvbams-access-2010
iDevlop
iDevlop

Reputation: 25252

Error on opening qd.openrecordset

What is wrong in this sample ? It breaks where indicated, even while the tbl name provided is the one of a perfectly working linked table.

Sub showLinked(tbl As String)
'tbl is the name of an existing local linked table (SQL Server)'
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim qd As QueryDef
    Set db = CurrentDb

    With db.TableDefs(tbl)
        Debug.Print .Name, .SourceTableName, .Connect
        Set qd = db.CreateQueryDef("")
        qd.Connect = .Connect
        qd.SQL = "select 1 xxx from " & .SourceTableName
        qd.ReturnsRecords = True
        Set rs = qd.OpenRecordset()     'breaks here: error 3146 - "ODBC--call failed"
        Debug.Print "test connection:", rs.Fields(0)
    End With
End Sub

Upvotes: 1

Views: 231

Answers (1)

iDevlop
iDevlop

Reputation: 25252

Found the culprit: I was testing my function on an Access table called Data_Archive_Transaction and its SourceTableName is Data_Archive.Transaction (not my name, I promise).
Since Transaction is a reserved word, in a SELECT it must be surrounded with brackets: Data_Archive.[Transaction].
I tried with another table with a more normal name and it worked fine.

Upvotes: 1

Related Questions