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?

 

[xenou]

Quite unsure but in general it sounds like:

1) Create a pass-through query that gets the data you want
2) Create a make table query that creates a table from #1 so you can view it as "data dump"

 

[ClimoC]

Quite unsure but in general it sounds like:

1) Create a pass-through query that gets the data you want
2) Create a make table query that creates a table from #1 so you can view it as "data dump"

Thanks xenou - a bit more digging this afternoon makes it sounds like I do need a pass-through query, though most examples I keep finding are Access-Access or T-SQL-T-SQL

But it seems I'm being tripped up by 'you can't carry out this action at the present time.' without any good reason! It just bugs on the 'OpenQuery' line

Dim strFilespec As String
    Dim strConn As String
    Dim sqlStr As String
    
    Dim AccApp As New Access.Application
    Dim ACCDB As Database
    Dim qdfPassthrough As DAO.QueryDef
    
    Set AccConn = New ADODB.Connection
    
    AccApp.Visible = True
    
    Set ACCDB = AccApp.DBEngine.CreateDatabase("C:\users\climoc\desktop\TestDump102.accdb", dbLangGeneral)
    
    AccApp.DBEngine.OpenDatabase ("C:\users\climoc\desktop\TestDump102.accdb")
    
    Set qdfPassthrough = ACCDB.CreateQueryDef("qrySQLPass")


    newconn = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=******)(PORT=*****))(CONNECT_DATA=(SID=******)));User Id=*****;Password=*****"
    
    qdfPassthrough.Connect = "ODBC;" & newconn
    qdfPassthrough.Sql = "SELECT * FROM my_table WHERE department = 'SKD1' AND date_raw = '20140214'"
    qdfPassthrough.ReturnsRecords = False
    qdfPassthrough.Close
    
    AccApp.RefreshDatabaseWindow
    
    AccApp.DoCmd.OpenQuery "qrySQLPass", acViewNormal
    AccApp.DoCmd.Maximize

Any thoughts what I've done wrong?

 

[xenou]

Hi,
Can't you just make a real pass through query? I don't see why you are using VBA here.
How to create an SQL pass-through query in Access


ξ

 

[ClimoC]

Hi,
Can't you just make a real pass through query? I don't see why you are using VBA here.
How to create an SQL pass-through query in Access


ξ

As I understand it, you have to create the Odbc connection first - which tried to then bring down all 10,000,000 rows - and then build the query off that. Plus, this procedure would be called from a vb6 application built in Visual Studio, and will need to have variables entered from userforms to define the query (the dates and other things) that's builds the database.

 

[xenou]

I don't believe creating an ODBC connection brings all the rows. Why would it? It would simple send the query and bring back the query results. One of the odd things about your posted code is that you set the query property ReturnsRecords = false. I have never used that property but I would presume it means you don't get any records back so it doesn't seem appropriate for a Select query.

However, if this is a VB6 application then I guess you can't use Access anyway - so the next question is why are you doing this in Access? I would prefer to use ADO if I was writing a VB6 application.

 

[ClimoC]

I don't believe creating an ODBC connection brings all the rows. Why would it? It would simple send the query and bring back the query results. One of the odd things about your posted code is that you set the query property ReturnsRecords = false. I have never used that property but I would presume it means you don't get any records back so it doesn't seem appropriate for a Select query.

However, if this is a VB6 application then I guess you can't use Access anyway - so the next question is why are you doing this in Access? I would prefer to use ADO if I was writing a VB6 application.

I use excel/access all the time with ADO. I would at later stages use Ado, but the process above I'm trying to sort is to download snapshots of the oracle table in its then state, datestamp the table it would create, then from userforms the user will pick the two dates they want, it'll then return two recordsets, and I can then do comparisons and generate outputs into userforms and reports from that.

but I have to get the databases built first. There's neither the resource nor the cost to implement the changes needed to do this all in oracle (nor the desired outputs options that my vb/cab can provide)

 

[xenou]

I would at least still try a pass through query. You shouldn't need recordsets or vba for that. You just write your query and get the data you want. It won't pull into the entire table - just the data you query for. However for testing purposes I would recommend:

1) use a very simple query
2) don't use SELECT * -- instead use specific field names (this is good practice always, actually).
3) ideally for a test just to see that the connection works, use a query that selects a single field from a relatively simple table. Avoid dates as these are difficult to pass between disparate sytems like Access and Oracle. A good query to use on an oracle database is just SELECT dummy FROM Dual; Since Oracle can store binary objects and who knows what else you just want to start out with simple data types that you know are compatible - text fields and number fields.

 

[ClimoC]

I would at least still try a pass through query. You shouldn't need recordsets or vba for that. You just write your query and get the data you want. It won't pull into the entire table - just the data you query for. However for testing purposes I would recommend:

1) use a very simple query
2) don't use SELECT * -- instead use specific field names (this is good practice always, actually).
3) ideally for a test just to see that the connection works, use a query that selects a single field from a relatively simple table. Avoid dates as these are difficult to pass between disparate sytems like Access and Oracle. A good query to use on an oracle database is just SELECT dummy FROM Dual; Since Oracle can store binary objects and who knows what else you just want to start out with simple data types that you know are compatible - text fields and number fields.

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

 

[ClimoC]

Though the VBA I was originally posting, seemed to be creating the database and the 'qrySQLPass' - but won't run it.