Querying Oracle

[ErinK]

Not sure if this is the correct location. I have a connection to Oracle 10g in VBA. The connection string is correct.

When the sql statement is "Select sysdate from dual" and executed by a recordset.OpenRecordset(): this returns a value as I would expect.


When the sql statement is "Select * from mytable" (which is a table created in my schema which I create and which I can query with THAT query in SQL Developer just fine) and execute by a recordset.OpenRecorset(): 3146 (ODBC--call failed)

I'm using ODBC and the ODBC Version is "Microsoft ODBC for Oracle".

3146 seems to indicate that the driver doesn't support SOMETHING but I don't know what, it's not like these are super-complicated queries here. I can query dual, heck I can even run "select * from user_tables" and get back information.

Thoughts?

Erin

[orange]

It's been a few years since I used Oracle, and Access to Oracle via ODBC.

One thing to watch for is the SQL syntax. Oracle has its own quirks as does Access. Sysdate and Dual mean nothing to Access.
Do you link your Oracle tables from Access? Things like Mid(), Len(),Nz() are Access; substr() toDate() etc are Oracle.
I remember that I had to use Oracle syntax on PassThrough queries.

which is a table created in my schema

--that's in Oracle right? And you link that table from Access and it shows in your Access tables.

[lfpm062010]

It could be the syntax is missing the owner's prefix.

For example, if ORACLE user "ERINK" create a table called "MYTABLE". Then the syntax would be

SELECT * FROM ERINK.MYTABLE;

Not sure that is the problem. I guess it all depends on how the connect string is build and who the user is and if the user has the privilege to read the table.

[ErinK]

So the connection string is thus:

ODBC;Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST =hostname.com)(PORT=1521))(CONNECT_DATA=(SERVICE_N AME=servicename.com)));Uid=uid;Pwd=pwd;

Obviously obscured for security reasons.

I've tried fully qualified names to no avail. I've tried pass-through as well. Same result. I have privileges to read as I've used other select queries in a different application. This appears to be MSAccess specific.

Orange: There are no linked tables. This is a direct ODBC query to the Oracle database returning a resultset.

[lfpm062010]

Can you install the Oracle version of ODBC Driver? I think Oracle 10.2.3 should work.

I also found this. It says it will list all the error messages. If it works. It might give a more Oracle specific error messages.

' DAO Error Handler
Dim MyError As Error
Debug.print Errors.Count
For Each MyError In DBEngine.Errors
With MyError
MsgBox .Number & " " & .Description
End With
Next MyError

[ErinK]

Ok, everyone, I found what the problem was. I do an initial Oracle connection to make sure there aren't any connection issues before attempting to verify AD group membership in another schema and table. Apparently the connection wasn't being closed and re-opened with each subsequent call so that everything was connecting via the INITIAL connection (where those tables don't exist). Once I realized I was hitting two different schemas I reset them to the same schema and it works fine. It was never a problem with the syntax or the connection string (sort of). It was the fact that I was trying to query a table that didn't exist in the current connection.

Thanks, everyone!