CODEWITHSUNDEEP
sql-servervbams-access
kudan
kudan

Reputation: 31

VBA Insert whole ADODB.Recordset into table

I am trying to run SQL query on sql server (some DWH) and then insert outcome into access table (using VBA).
I did it by using ADODB.Connection, ADODB.Command and ADODB.Recordset. At this point I have my outcome in Recordset and I Wonder how I can insert it into table without looping it.
I tried: 

If Not (Rs.EOF And Rs.BOF) Then  
    Rs.MoveFirst  
    Do Until Rs.EOF = True  
        DoCmd.RunSQL ("INSERT INTO Table (F1, F2) VALUES ( " & rs![F1] & ", " & rs[F2] & ")" 
        Rs.MoveNext  
    Loop  
End If

But Recordset may have over 100k rows. So it would take ages to insert it by using this method.

Another very fast way is to open a new excel workbook paste it into worksheet and then import it. But I would like to avoid it. Is there any other way ?

---------EDITED-----------
Sorry guys. My bad. I was forcing solution with VBA while linkin it was perfect. THANKS !

Upvotes: 3

Views: 8848

Answers (2)

Gustav
Gustav

Reputation: 55841

I was wondering if there is any as fast way which use Access resources only.

As already mentioned, link the SQL table, then create a simple append query that reads from the linked table and writes to your Access table, and you're done.

Upvotes: 3

Dick Kusleika
Dick Kusleika

Reputation: 33145

I agree with the commenters that you should link if at all possible. But I wanted to see if it could be done. I ended up converting the recordset to a comma delimited file and use TransferText to append it.

Public Sub ImportFromSQLSvr()

    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim sResult As String
    Dim sFile As String, lFile As Long

    Const sIMPORTFILE As String = "TestImport.txt"

    Set cn = New ADODB.Connection
    cn.Open msCONN
    Set rs = cn.Execute("SELECT SiteID, StoreNumber FROM Site")

    'Add a header row, replace tabs with commas
    sResult = rs.GetString
    sResult = "SiteID, StoreNumber" & vbNewLine & sResult
    sResult = Replace(sResult, vbTab, ",")

    'Write to a text file
    lFile = FreeFile
    sFile = Environ("TEMP") & "\" & sIMPORTFILE
    Open sFile For Output As lFile
        Print #lFile, sResult
    Close lFile

    'Append to table dbo_Site
    DoCmd.TransferText acImportDelim, , "dbo_Site", Environ("TEMP") & "\" & sIMPORTFILE, True

    On Error Resume Next
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing

End Sub

If you have any commas in your data, you'll need to do some extra work to properly format the csv.

Upvotes: 2

Related Questions