Results 1 to 3 of 3

Thread: Exporting Excel data into Access
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Exporting Excel data into Access

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

    The two pieces of code below both work.

    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:

    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 by tiredofit; Sep 9th, 2019 at 06:02 AM.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,715
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Exporting Excel data into Access

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exporting Excel data into Access

    Thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •