Exporting Excel data into Access

[tiredofit]

I am trying to export some data on my worksheet into an Access table.

The two pieces of code below both work.

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:

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

 

[Joe4]

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.

 

[tiredofit]

Thanks