Trying to Import Recordsets into Access Tables

[fermon]

I need to import data from an ODBC database (Oracle) for
an Access Front-End Application. The data is compiled from
different tables into a temporary table in Access for
further processing and/or display in the front-end.

The problem I have is response time for the users.
Currently, they have to wait about 1 minute and 30 seconds
for all data to be retrieved, and since it is displayed
graphically, it is hard to show it as it is retrieved.

The way I get the data is like this:

Private Sub btnAgrAnalysis_Click()

Dim cnnLocal As ADODB.Connection
Dim strDate, strQry As String
Dim varDate, varRMS As Date

'Instatntiate and initialize connection
Set cnnLocal = New ADODB.Connection
cnnLocal.Open CurrentProject.Connection
'Delete data on Temporary Volumes Table
cnnLocal.Execute "qryDelAgrVol"

While ((varDate <= varRMS) And (varDate <=
Me.AgrEndDate))
'Form Query String using different variables
strQry = "SELECT Fields FROM Table IN
[ODBC;dsn=MyDsn;UID=admin;PWD=] etc.."
'Append records to Temp table
cnnLocal.Execute "INSERT INTO tblAgrVol " &
strQry
'Change variables to go to next table
Wend

End Sub

I have the impression this is not a pass-through query and
that I could get faster response if I could append a
recordset from a pass-through to the temp Table
(tblAgrVol). However, I haven't been able to figure out
how to do this.

Can I append a recordset from a VBA generated ODBC query
to a local table without having to go record by record?
(That takes about 50 times more time, according to my
tests)

Any help is appreciated,

Fernando

 

[Joe Fallon]

Use SPT query.
Use *Oracle* syntax in the SQL window (not Jet syntax.)

Write the SQL query so all the records are retrieved in one shot. (if
possible.)
In the query window use the menu:
Query
SQL Specific
Pass Through

Set the connection string property of the query to a valid one for your
Oracle DB.

You can write multiple queries and run them back to back.

You can write a regular Access query based on you SPT query which will do
the append for you.
e.g.
Write Query1 as SPT.
Write Query2 as Access query with Query1 as source and make it append to a
table.