VBA Passing parameter in query to ADO connection '1004': Application-defined or object-defined error

Question

It seems that in the command text, I am not able to use a parameter as a table name so I am now trying to hard code it into the VBA the query but I am running into the '1004': Application-defined or object-defined error. Attached is a screenshot of the command text I am using but doesn't seem to work. The code below I added is causing the error: The Connection Properties pop up box

With ActiveWorkbook.Connections("Query1").OLEDBConnection
        .BackgroundQuery = True
        .CommandType = adCmdText
        .CommandText = "SELECT * FROM [DBO].[Refresh_" & UserName & "] ORDER BY [Item No];"
End With

I would appreciate any help anyone can give me in regards to the query within the "Connections" properties box or the vba code. Either would work.

Thanks in advance, Paul

Answer 1

Depending on how you set up the connection, it will either have an ODBCConnection, or an OLEDBConnection property (but not both).

If you break down the code a little you can put a watch on the connection and see what properties are populated:

Sub Tester()
    
    Dim conn As WorkbookConnection 'put a Watch on this and step through....
    
    Set conn = ThisWorkbook.Connections("TestQuery")
    
    With conn.ODBCConnection
            .BackgroundQuery = True
            .CommandType = xlCmdSql    '<<<<  not adCmdText
            .CommandText = "select * from ops$rs3.hts_scientist"
    End With

End Sub

Watch window: