Create or paste recordset into Access table

[Poduska]

I wish to have a process with a Query that pulls data from an Oracle table predicated upon dates specified in a form and places it in an Access table. I have this working with just an Access query now but it has an outer join to a table which already resides in Access where the user can add information to only specific records (information not available within Oracle.)

This worked fine with a limited number of test records but with production 15 million lines the Access Query just never comes back, well in an hour or so perhaps. I know how to run a pass thru query and then add this to an Access make table query but then I lose the ability to utilize my Form to specify dates. The pass thru query to retrieve my records with the dates hard coded in only takes 6 to 7 min.

Is there a way to modify the SQL of a pass thru query via code?

Or better yet, can I take a recordset from Oracle (which I know how to do) and then paste it into Access so I can mess around with my outer joins with much smaller number of records within Access.

I currently have a process which writes a record set to an excel spreadsheet

 xlStartRange.CopyFromRecordset rst

but cannot get it to write to a table. One would think this would be easy but...

Thanks

 

[Eupher]

Hi, Poduska,

Sounds like you need to reference your form fields as parameters in your pass through. Here's a Microsoft KB article that may help:

http://support.microsoft.com/kb/131534

HTH,

Ken S.

 

[PHV]

Is there a way to modify the SQL of a pass thru query via code
You may play with the QueryDef.SQL property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886