Exporting Excel data into Access

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,414
I am trying to export some data on my worksheet into an Access table.

The two pieces of code below both work.

Rich (BB code):
Public Sub ExportDataIntoAccessSlow()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    
    Const AccessStr As String = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\Access\Test.accdb;Persist Security Info=False;"
    
    ' connect to the Access database
    
    Set cn = New ADODB.Connection
    cn.Open AccessStr
    
    
    ' open a recordset
    
    Set rs = New ADODB.Recordset
    rs.Open "MyTable", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    
    ' all records in a table
    
    r = 2 ' the start row in the worksheet
    
    Do While Len(Range("A" & r).Formula) > 0
    
    
        ' repeat until first empty cell in column A
        
        With rs
        
            .AddNew ' create a new record
            
            
            ' add values to each field in the record
            
            .Fields("Name") = Range("A" & r).Value
            .Fields("Date") = Range("B" & r).Value
            
            ' add more fields if necessary…
            
            .Update ' stores the new record
            
        End With
        
        r = r + 1 ' next row
        
    Loop
    
    rs.Close
    
    Set rs = Nothing
    
    cn.Close
    
    Set cn = Nothing
     
End Sub



and the fast one:

Rich (BB code):
Public Sub ExportDataIntoAccessFast()

    Dim Conn As ADODB.Connection
    Set Conn = New ADODB.Connection
    
    Const AccessStr As String = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\Access\Test.accdb;Persist Security Info=False;"
    Conn.ConnectionString = AccessStr
    Conn.Open
    
    Dim FileName As String
    
    FileName = Application.ActiveWorkbook.FullName
    
    Dim wsName As String
    
    wsName = "[" & Sheet1.Name & "$]"
    
    Dim ssql
    
    ssql = "INSERT INTO MyTable ([Name],[Date])" & _
           "SELECT * " & _
           "FROM [Excel 8.0;HDR=YES;DATABASE=" & FileName & "]." & wsName
    
    Conn.Execute ssql

End Sub




The first one does it row by row and is slow.

My question is: What is the advantage of using the slow method?

Thanks
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
My question is: What is the advantage of using the slow method?
None, that I can think of. Loops are typically slower, so it is usually advantageous to avoid them, if there are other ways of doing it.

Note that there are other ways of doing this without using any VBA, such as linking your Excel workbook to an table in Access, and then doing and Append Query from this linked table to your final table in Access.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,577
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top