I need to export data from an ORACLE database, and I'm using Access VBA. This involves running SQL via ODBC, using a Passthrough query that brings in about 1,300,000 records and takes about 15 minutes to run.
I need to find the best way to do this.
I'll describe what I did, but I'm not sure if it's the best option.
1) I create a Querydef for the passthrough "QRY_Extract"
2) I set the query properties - The query is complex and has several joins. To simplify, I just call it 'OracleTable'
qdfPassThrough.Connect = SConnect1
qdfPassThrough.SQL = "Select fields from OracleTable;"
qdfPassThrough.ReturnsRecords = True
qdfPassThrough.ODBCTimeout = 1500
3) I define a second Query that will be a 'Make Table', exporting the records from the "QRY_Extract" passthrough to a local table in Access. This Query is named "QRY_Local_Temp".
4) Query execution "qdfLocal" that will create LocalTable with 1.300.000
*******************************************
I'd like to know the best procedure for exporting to CSV:
1) Directly from the "QRY_Extract" Passthrough, without having created the temporary local table "LocalTable," which will be deleted at the end of the export.
2) Export from the created temporary table "LocalTable."
3) I'm using DAO; would it be better to use ADO?
Last question:
The purpose of the CSV file is to feed an MS query associated with an Excel table, which uses parameters that extract subsets from the table.
Would it be better to not export to CSV or link the MS Query directly to the Access table?
Thank you in advance for your valuable help.
Thank you very much.
I need to find the best way to do this.
I'll describe what I did, but I'm not sure if it's the best option.
1) I create a Querydef for the passthrough "QRY_Extract"
Set qdfPassThrough = CurrentDb.CreateQueryDef(QRY_Extract)
2) I set the query properties - The query is complex and has several joins. To simplify, I just call it 'OracleTable'
qdfPassThrough.Connect = SConnect1
qdfPassThrough.SQL = "Select fields from OracleTable;"
qdfPassThrough.ReturnsRecords = True
qdfPassThrough.ODBCTimeout = 1500
3) I define a second Query that will be a 'Make Table', exporting the records from the "QRY_Extract" passthrough to a local table in Access. This Query is named "QRY_Local_Temp".
Set qdfLocal = CurrentDb.CreateQueryDef(QRY_Local_Temp)
qdfLocal.SQL = "Select into LocalTable (Fields List) Select (Fields) from QRY_Extract;"
4) Query execution "qdfLocal" that will create LocalTable with 1.300.000
qdfTemp.Execute dbFailOnError
*******************************************
I'd like to know the best procedure for exporting to CSV:
1) Directly from the "QRY_Extract" Passthrough, without having created the temporary local table "LocalTable," which will be deleted at the end of the export.
2) Export from the created temporary table "LocalTable."
3) I'm using DAO; would it be better to use ADO?
Last question:
The purpose of the CSV file is to feed an MS query associated with an Excel table, which uses parameters that extract subsets from the table.
Would it be better to not export to CSV or link the MS Query directly to the Access table?
Thank you in advance for your valuable help.
Thank you very much.
Last edited: