Efficiently Importing Large Records to MS - Access

[amanphilip]

Dear All,

I currently have a small VBA project where it will import data from excel sheet to MS-Access. Excel sheet contains 163 columns and 141k rows. However it takes about 5 minutes to process 1000 rows. Is there anyway i can make my code more efficient to speed up the process of importing? If my calculation is right it will take about 11 hours to complete importing the whole 141k rows. Here's my code below:

Sub exporttodb()
Dim db As Object
Dim rs As Object
Dim conn As Object
Dim strsql As String
Dim accessDB As String
Dim tablename As String
Dim dataarray() As Variant
Dim batchsize As Long
Dim i As Long
Dim j As Long
Dim wbs As Workbook
Dim ws As Worksheet
Dim fd As FileDialog
Dim selws As Worksheet
Dim lastcol As Integer
Dim lastrow As Long
Dim selectedfile As Object
Dim listboxcounter As Integer
Dim currentTotalRecords As Long
Dim currentrowrecord As Long


 
    'set the DB path and table name
    accessDB = ThisWorkbook.Worksheets("References").Range("F1").Value & ThisWorkbook.Worksheets("References").Range("F2").Value
    tablename = "Consolidated Inventory+FFE 2"
  

    'Create Access Database and  recordset objects
    Set db = CreateObject("Access.Application")
    db.opencurrentdatabase accessDB
    Set rs = db.currentdb.openrecordset(tablename)

    'set the connection string for the recordset and delete the existing records.
    Set conn = CreateObject("ADODB.Connection")
    conn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessDB & ";")
    strsql = "DELETE * FROM [" & tablename & "];"
    conn.Execute strsql
  
    'Import the records.
    Set fd = Application.FileDialog(msoFileDialogOpen)
        With fd
            .AllowMultiSelect = False
            .Title = "Select Workbooks"
            .Filters.Clear
            .Filters.Add "Excel Files", "*.xls; *.xlsx; *.xslm; *.xlsb", 1
          
                If .Show - 1 Then
                    For Each selItems In .SelectedItems
                        Set wbs = Workbooks.Open(selItems)
                  
                        For Each ws In wbs.Sheets
                            listboxcounter = listboxcounter + 1
                          
                            'Add the sheetnames in the listbox
                            Sheetsel.ListBox1.AddItem ws.Name
                        Next ws
                            Sheetsel.Show
                    Next selItems
                End If
              
                'Prepare the data for export
                If selectedv <> "" Then
                    Set selws = wbs.Worksheets(selectedv)
                    lastcol = selws.Cells(1, selws.Columns.Count).End(xlToLeft).Column
                    lastrow = selws.Cells(selws.Rows.Count, 2).End(xlUp).Row
                  
                End If
        End With
      
        batchsize = 1000
        currentTotalRecords = rs.RecordCount
      
       'Loop through the data in batchsize
        For currentrowrecord = 2 To lastrow Step batchsize
     
            'resize the array to matcht the data in the current batch
            ReDim dataarray(1 To batchsize, 1 To lastcol)

            'populate the array with data from the source sheet
            For i = 1 To batchsize
                For j = 1 To lastcol
                    dataarray(i, j) = selws.Cells(currentrowrecord + i - 1, j).Value
                Next j
            Next i
          
            'Insert the batch to the access db
            On Error Resume Next
              
                For i = 1 To batchsize
                rs.AddNew
                    For j = 1 To lastcol
                        rs.Fields(j - 1).Value = dataarray(i, j)
                    Next j
                rs.Update
                  
                Next i
          
       Next currentrowrecord
      
 End Sub

 

[Micron]

Can't say where the bottle neck(s) might be, but why do all this automation and recordset processing from Excel when you could link sheets as tables in Access then work from there?

 

[welshgasman]

How about just using TransferSpreadsheet even?

DoCmd.TransferSpreadsheet method (Access)

Office VBA reference topic

learn.microsoft.com

 

[amanphilip]

Can't say where the bottle neck(s) might be, but why do all this automation and recordset processing from Excel when you could link sheets as tables in Access then work from there?

Hi Micron,

Just to give you a background of the current process of the team:

1. A Team Leader will extract data start of day from Access to excel spreadsheet and will send it to the group so they can do their tasks(records management, data validation & etc).
2. at 3pm all members will send their updates to the team lead who extracted the report.
3. Team Lead will do final checks with the data and will consolidate all the records in one spreadsheet
4. Upload the data back to MS-Access.

To save leg work the teamleader prefers to delete all records in MS-Access table then re-upload all 140k row records back to MS-Access.

now to answer your question:
but why do all this automation and recordset processing from Excel when you could link sheets as tables in Access then work from there?

- The team wants to use the MS-Access as the source database of all records rather it in excel.

 

[amanphilip]

How about just using TransferSpreadsheet even?

DoCmd.TransferSpreadsheet method (Access)

Office VBA reference topic

learn.microsoft.com

Hello i came across with this solution however this is not available in Excel VBA if my research was right. Not all members has MS-Access installed in their machines currently.

 

[Micron]

I cannot help but think that the whole process is more difficult/convoluted than it needs to be. Data is going from Access to Excel so that people can do something with it. That "something" is vague, but if it doesn't involve complex calculations/charting/analysis that only Excel can do, then I think someone needs to re-evaluate the process - especially since it seems the edited data ends up back in Access. Sometimes this sort of thing becomes the solution because there is a licensing barrier in that corporations don't want to pay for Access licenses for everybody. That's where free Access runtime comes in. If that is an option, then there's no reason to port back and forth between Access and Excel. Those users do "something" with the data in free Access runtime and maybe avoid the back and forth data management.

 

[amanphilip]

I cannot help but think that the whole process is more difficult/convoluted than it needs to be. Data is going from Access to Excel so that people can do something with it. That "something" is vague, but if it doesn't involve complex calculations/charting/analysis that only Excel can do, then I think someone needs to re-evaluate the process - especially since it seems the edited data ends up back in Access. Sometimes this sort of thing becomes the solution because there is a licensing barrier in that corporations don't want to pay for Access licenses for everybody. That's where free Access runtime comes in. If that is an option, then there's no reason to port back and forth between Access and Excel. Those users do "something" with the data in free Access runtime and maybe avoid the back and forth data management.

I see your point thank you for sharing your thoughts. And yes you are right currently there's a licensing barrier that's why not everyone has MS-Access installed in their machines, resulting to us developing excel vba instead.

I will have them re-evaluate the process and see from there.

Thank you again for your time and help. Appreciate it.

 

[welshgasman]

I used to do something similar for a bank in the UK, however I just extracted the data required from an excel sheet, and the users did all the rest in Access.
As they could use a runtime version of Access, that might be a better way to go?

 

[jackd]

Just scanning this thread and would offer the following for consideration.

-I tend to agree with Micron and Welshgasman--Access might be the better option, but we really don't know enough about the application

-Excel is not a multi user software; if multi simultaneous use/interaction of the data is required

-it seems there is more data transfer involved than necessary

-what is current bottleneck/issue(s) besides large, slow data transfers

-how is your data backed up in case of problems/recovery

-some analysis and review of the business process(es) being automated would help with any potential design changes

-what Ms Access do you currently have --Access runtime is free

We often see posts concerning applications that outgrow an initial spreadsheet approach. Database is different than spreadsheet and requires a different mindset. We also often hear --we're too deep into what we have and can't change. You can analyze, review, prototype and even plan and enact the plan based on priorities.