VBA/SQL: The fastest way to send data from Excel sheet into Access database

mtweeman · Dec 20th 2013

Hello, I believe that's question for some experienced users. I'm looking for the fastest way of sending data from Excel sheet to Access database. The shape of sheet looks like this:

ID Description
10 kjdsaljdlaksd
11 asddsaj
12 asdads

etc.

Currently I'm using code:

Code

Dim acc As New Access.Application
    
    acc.OpenCurrentDatabase Filepath, , sPassword
    acc.DoCmd.TransferSpreadsheet _
        acImport, _
        acSpreadsheetTypeExcel12Xml, _
        "Statistics", _
        Excel.ActiveWorkbook.FullName, _
        True, _
        "Mistakes$A1:B" & iLastRowMistakes
    acc.CloseCurrentDatabase
    acc.Quit
    Set acc = Nothing

Display More

I know that I can also use SQL mixed with VBA.

Would it be faster? Would I feel the difference? Is there any other way to do this (fastest possible of course)?

mtweeman · Dec 21st 2013

Re: VBA/SQL: The fastest way to send data from Excel sheet into Access database

I did some research and managed to do adding data to Access database using SQL. Here's the code:

Code

Set db = DBEngine.OpenDatabase(Options.Cells(8, 2).Value, False, False, "MS Access;pwd=" & sPassword)    
    For i = 2 To iLastRowMistakes
    
    db.Execute "INSERT INTO Statistics([WP_No], [Number], [Description], [Creator_ID], [Creator_date], " & _
    "[FC_ID], [FC_date], [FC_status], [SC_ID], [SC_date], [SC_status], [Comment], [Mistake_category], [Issue_description]) " & _
    "VALUES " & "(" & Quotes & Mistakes.Cells(i, 1).Value & Quotes & ", " & Quotes & Mistakes.Cells(i, 2).Value & Quotes & _
    ", " & Quotes & Mistakes.Cells(i, 3).Value & Quotes & ", " & Quotes & Mistakes.Cells(i, 4).Value & Quotes & _
    ", " & Quotes & Mistakes.Cells(i, 5).Value & Quotes & ", " & Quotes & Mistakes.Cells(i, 6).Value & Quotes & _
    ", " & Quotes & Mistakes.Cells(i, 7).Value & Quotes & ", " & Quotes & Mistakes.Cells(i, 8).Value & Quotes & _
    ", " & Quotes & Mistakes.Cells(i, 9).Value & Quotes & ", " & Quotes & Mistakes.Cells(i, 10).Value & Quotes & _
    ", " & Quotes & Mistakes.Cells(i, 11).Value & Quotes & ", " & Quotes & Mistakes.Cells(i, 12).Value & Quotes & _
    ", " & Quotes & Mistakes.Cells(i, 13).Value & Quotes & ", " & Quotes & Mistakes.Cells(i, 14).Value & Quotes & ");"




    Next i
    
    db.Close

Display More

I've compared time used for running macro from previous post and this one.

Results:
VBA: 1000 records, running time: 3,07s
SQL: 1000 records, running time: 0,89s

VBA: 10000 records, running time: 6,07s
SQL: 10000 records, running time: 9,98s

As we can see, running time depends on number of records and after crossing some number of them, SQL is just slower.

Question for you: do you know how to add records to database but using whole range?
Current solution uses for loop to achieve desired effect. I've tried to find some informations how to do it, but I miserably failed.

Craig Ottley · Mar 24th 2014

Re: VBA/SQL: The fastest way to send data from Excel sheet into Access database

If you've got access to SSIS that will be the fastest way of getting data into access...

VBA/SQL: The fastest way to send data from Excel sheet into Access database

Participate now!

Share