Oracle Recordset

[mykebass]

Our team is attempting to retreive data from an Oracle 9i database via ADO connection and write its contents into an Access table (for further manipulation). At present, we do not have access to create a stored procedure on the server, so that is not an option.

Question is what is the fastest method to accomplish this: linked tables and stored queries via dao or the method shown below (ado connectio). Below is an example of some code which takes about 4 hours due to number of rows returned and inserted into access table:

Private Sub FORM_GET_AR_ITEMS()
X = "SELECT POID_ID0, ACCOUNT_OBJ_iD0, AR_ACCOUNT_OBJ_ID0, BILL_OBJ_ID0, DUE FROM ITEM_T WHERE AR_ACCOUNT_OBJ_iD0 IN (" & ACCTS & ")"

With billingSET
.Source = X
.Open
If .BOF Then
Else
Do Until .EOF
ITID = .Fields(0)
ACID = .Fields(1)
ARACID = .Fields(2)
BILLID = .Fields(3)
DUE = .Fields(4)

SQL = "INSERT INTO [BILLING: ITEMS_BILLED] (CUSTOMER_ACCOUNT,ITEM_POID,AR_ACCOUNT_ID, ACCOUNT_ID, BILL_ID,DUE) " & _
" SELECT """ & CUSTOMER_ACCOUNT & """,""" & ITID & """,""" & ARACID & """,""" & ACID & """,""" & BILLID & """,""" & DUE & """"
DoCmd.RunSQL SQL
.MoveNext
Loop
End If
.Close
End With
End Sub

 

[PHV]

Why not simply an append query from a linked table ?

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

 

[mykebass]

I would not be opposed to that solution, but I'm trying to determine if that method is any faster than the above code?

 

[PHV]

I guess ONE append query should be faster than a myriad of DoCmd.RunSQL

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

 

[mykebass]

You're a bit of a smart-arse, but I did test out your solution and it was much faster. Thanks for the advice.