ODBC Oracle link to Access table with defined query

[ClimoC]

Hey ho

I'm trying to move part of an Oracle table-view into a new Access(2010) table.

I tried linking the table with an ODBC connection from Access - but the Oracle table is both too large for Access to handle, and Access also ended up jamming the server and p*****g off the Oracle DBAs.

I have the connection parameters and can pull down the Oracle data with selective SQL statements via ADO/VB to make Recordsets of the data I want - but, without resorting to looping thorugh the RS adding to an Access table, I'd like to just 'dump' the selective data from Oracle into Access tables as and when I see fit.

It feels like this shouldn't be impossible but the issue with googling anything like this is that it mostly returns 'migrating Access TO Oracle' type queries, or refers to the aforementioned ODBC linkage.

Can I use some sort of CREATE TABLE in Access that connects to the Oracle database but only returns the results of an SQL query I give it? Or modify the ODBC connection to only import the data I want?

 

[ClimoC]

I suppose what's doing my head in is that I can connect to Oracle with ADO from VBA just fine, but just can't seem to get it working for a passthrough.

 

[ClimoC]

Maybe I need to rephrase the problem...:

If I can happily use ADO to make a connection and get a recordset returned from some SQL using this:

"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=******)(PORT=*****))(CONNECT_DATA=(SID=******)));User Id=*****;Password=*****"

Into this format? This seems to be what other examples are using - DAO rather than ADO...

LPassThrough.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"

Or am I going about this the wrong way, and need to use 'DSN-LEss'?

 

[ClimoC]

Thanks Christ.

Got it working like this:

   Dim db As Database
   Dim LPassThrough As QueryDef


   Set db = CurrentDb()


   Set LPassThrough = db.CreateQueryDef("qryTemp6")
   
   
   newstr = "ODBC;Driver={Microsoft ODBC for Oracle}; Server=*****; uid=*****; pwd=*****"

LPassThrough.Connect = newstr
   LPassThrough.SQL = "SELECT DISTRIBUTOR, DEAL_CODE from my_view_table WHERE DPT = 'My Department' And schedule_date_raw >= '20130501' and schedule_date_raw < '20130531'"
   LPassThrough.ReturnsRecords = True
   'LPassThrough.Execute
   LPassThrough.Close


    CurrentDb.Execute "INSERT INTO TestTable([DISTRIBUTOR], [DEAL_CODE]) SELECT qryTemp6.[Distributor], qryTemp6.[Deal_code] FROM qryTemp6"

YAAAAAYYY

I can sleep easier this weekend

 

[xenou]

Thanks xenou - but I'm still not able to get passed the ODBC connection. To set up a connection, I have to go 'Get External Data > ODBC Database > Import Source data into new table... [or link to the data source with linked table]'. Providing the connection details for either of these (which access seems to need to be done first) for the passthrough query to use, it begins trying to download the database. I ESC'd out of the action, so in theory the connection settings remain, but they don't. The password field keeps clearing itself out, and there's no 'remember password' option. Once I try and run the query on the connection, I just get back 'invalid connection string' for oracle issues

Glad you got it working. But just to note you weren't doing what I suggested. You did not want to import or link a table. You wanted to create a query and use ODBC to run the query. That's what a pass through query is. When you write a pass through query, the query you write is just plain SQL. In the query properties you provide the ODBC connection string. In many cases this is simplifed by creating a DSN on your machine first. It has nothing to do with using the import buttons you described above.