creating access table through pass through query

[ramserp]

Hi Friends,

I have 140000 records in a oracle table. I want to import that data into access table. I tried with pass through query but it is taking more time to import even I tried with recordset object also eithder were didn't work out.

I have written the following code to create a table through pass through query.


Sub PassThrough()
Dim db As Database
Dim strcon As String
Dim strSql As String
Dim insertQry As String
Set db = CurrentDb
strcon = "ODBC;DSN=SB2;Server=AB2001;Uid=admin;Pwd=admin;"
strSql = "select * from SIEBEL.S_ASGN_RULE_ITEM where asgn_rule_id in (select row_id from SIEBEL.S_ASGN_RULE where ASGN_GRP_ID in _
(select row_id from SIEBEL.S_A_GRP where (eff_end_dt > sysdate or eff_end_dt is null)))"
Set qdfPassThrough = db.CreateQueryDef("qdfPassThrough")
qdfPassThrough.Connect = strcon
qdfPassThrough.sql = strSql
insertQry = "select * into S_ASGN_RULE_ITEM from qdfPassThrough"
db.Execute insertQry
db.QueryDefs.Delete "qdfPassThrough"
End Sub

Please kindly help me with simplest way to import bulk data from oracle to access.


Thanks & Regards
Ramesh