Transfer ADO dataset to table??

[mike777]

Hello.
Is there an easy way to transfer the contents of an ADO recordset to an Access-native table?
Of course I could loop through the recordset and do "insert into...", but this seems like it might be a little tedious. Maybe not, but I thought there might be some command like copy to....
Thank you for your help.
-Mike Kemp

 

[AkutaSame]

Well, other than your obvious answer, you COULD make a stored procedure to export data from the ADO connection and import them into the Access DB table. Of course, your most simple (and fool-proof) is still to loop through the recordset and insert the data.

 

[mike777]

AkutaSame,
Thanks for the answer.
I'm just used to FoxPro, which has so many cool features like this that it makes everything else look like there's something wrong with the other language.
Thanks again.
-Mike

 

[Swi]

What are you creating the recordset from?

Swi

 

[mike777]

It's a reference cursor from an Oracle procedure.
Here's my code:

    Dim cnADO_native As New ADODB.Connection, sSQL As String, x As Integer
    Dim cnADO As New ADODB.Connection, rstADO As New ADODB.Recordset, cmdADO As New ADODB.Command
    Dim prmADO1 As New ADODB.Parameter, prmADO2 As New ADODB.Parameter, prmADO3 As New ADODB.Parameter
    DoCmd.Hourglass True
    Me.lbl_refresh.Visible = True
    Set cnADO_native = CurrentProject.Connection
    sSQL = "delete * from tbl_LO_List" 'native table
    cnADO_native.Execute sSQL
    Me.lbx_lolist.Requery
    cnADO.CursorLocation = adUseClient
    Set cnADO = Nothing
    cnADO.Open myConnectionString
    cmdADO.ActiveConnection = cnADO
    cmdADO.Properties("PLSQLRSet") = True
    Set prmADO1 = cmdADO.CreateParameter("prmADO1", adBSTR, adParamInput, , "all")
    cmdADO.Parameters.Append prmADO1
    Set prmADO2 = cmdADO.CreateParameter("prmADO2", adNumeric, adParamInput, , 0)
    cmdADO.Parameters.Append prmADO2
    Set prmADO3 = cmdADO.CreateParameter("prmADO3", adNumeric, adParamInput, , 0)
    cmdADO.Parameters.Append prmADO3
    sSQL = "{call pkg_leadmanagement.prc_LOList(?,?,?)}"
    cmdADO.CommandText = sSQL
    rstADO.Open cmdADO.Execute
    Do While Not rstADO.EOF
        sSQL = "insert into tbl_LO_List (id,branch,lo) values ("
        sSQL = sSQL & Str(rstADO.Fields(0)) & ",""" 'ID
        sSQL = sSQL & rstADO.Fields(1) & """,""" 'branch
        sSQL = sSQL & rstADO.Fields(2) & """)" 'lo_name
        cnADO_native.Execute sSQL
        rstADO.MoveNext
        x = x + 1
        Me.lbl_refresh.Caption = "Record number " & Str(x)
        Me.Repaint
    Loop

The unhappy thing about this approach is performance. Access takes a good 5-8 sec to load up this table. I really expected it to be almost instantaneous (again, FoxPro).
Thanks for the continued help.
-Mike Kemp