I have been able to write query definations in Code for local tables but I would like to be able to do it with a connection through ODBC. For some reason, I cannot link the table (it does not show up in my list of available tables) but through code, I have been able to create an ADO recordset with some SQL. The issue is, I cannot figure out how to turn that ADO recordset using an Oracle ODBC Connection into a runable query from the front end to view the result. Does anyone have any ideas? Thanks!

Does it need to be updatable? If not, use whatever connection string you used in code in a pass-through query. (Yeah, I know it says "SQL Specific" on the Query menu when you go to convert a query into a pass-through query, but that's not SQL as in SQL Server. <g>)

Thanks, I have not done a pass through query before, or if I have a did not know but I will read up and try it out. The tables I am accessing are Read Only. Thanks

You can also structure a Query WITH the ODBC connection string in the SQL Statement ... for example:

{Note the use of the dbl single quotes after the IN clause}

SELECT tblSomeTable.*
FROM tblSomeTable
IN ''
[ODBC;DRIVER=SQL Server;SERVER=myservername;UID=masteruser;PWD=thepassword;DATABASE=MxPData;Netwo
rk=DBNMPNTW]

Granted .. the above is from a SQL Server ODBC connection string, BUT you should be able to put your Oracle connection string in there as well...

Something like:

SELECT tblSomeTable.*
FROM tblSomeTable
IN ''
[ODBC;DRIVER=Microsoft ODBC for Oracle;SERVER=mynewserver;UID=mynewuid;PWD=mynewpwd;DBQ=mynewdb]
..

By doing it this way, you have the capability of creating a Read/Write data source... IF you have Read/Write to the tables.

I was able to create a passthrough query in the front end with the access query builder but have been unsuccessful doing it in code. On the front end. When I click run, it prompts me to search for my ODBC database but on the back end. I am unable to get it to work. any ideas? I must have something wrong with the syntax. The real reason behind this is that for some reason all of my tables do not show up when I try to link a table. Is there something I can do or change that would allow me to see all the tables?