MS-Access ODBC Connection to Oracle for SQL

Question

I'm trying to connect to an Oracle Database with Access 2003.

I want to use an ODBC connection and I want to set it up so that the user doesn't need to enter a password.

One of the problems I am having though is my sql query uses INTERFACE.Products and Access sees the period and thinks I'm trying to opening a file. IE Interface.MDB, when thats a part of my sql query.

Option Compare Database

Function OracleConnect() As Boolean

    Dim ws As Workspace
    Dim db As Database
    Dim LConnect As String
    Dim myQuery As String
    Dim myRS As Recordset


    On Error GoTo Err_Execute

    LConnect = "ODBC;DSN=Oracle;UID=user;PWD=password;"

    'Point to the current workspace
    Set ws = DBEngine.Workspaces(0)

    'Connect to Oracle
    Set db = ws.OpenDatabase("", False, True, LConnect)

    myQuery = "Select * from INTERFACE.Products"

    Set rst = db.OpenRecordset(myQuery)


    rst.Close
    db.Close


    Exit Function

Err_Execute:
    MsgBox MsgBox("Error Number:  " & Err.Number & " Message: " & Err.Description)

End Function

Answer 1

Could you try something like:

Set rst = db.OpenRecordset(mQuery, dbOpenSnapshot, dbSQLPassThrough)

This would just send the query verbatim to the Oracle database.

In the Access documentation for OpenRecordset you'll find this note:

If you open a Recordset in a Microsoft Access workspace and you don't specify a type, OpenRecordset creates a table-type Recordset.

This may be the source of the error you're getting.

As a rule, I would suggest to always be explicit in your parameters to OpenRecordset: the default behaviour isn't always consistent and it can generate strange errors.

I'm not sure about your design, but one way to make things a bit easier for you may be to simply create linked tables in Access.
In that case, you would be able to write queries against your INTERFACE.Products Oracle table as if if were a local Products table.
It would also save you from having to manage the connections yourself.