Exporting data to Access using VBA (looping issue)

Question

I've created 2 subs which connect to an open Access database (MasterFile_January2021.accdb) and exports the data to this database.

At the moment, it seems to go into an endless loop of exporting the data from Monthly Reporting Tool - 600k+ rows were exported before it crashed compared to 250k expected rows (and I could find duplicates when there were not in the original file).

Sub ExportData()

    Dim strPathFile As String, strFile As String, strPath As String, fileName As String
    Dim strTable As String
    Dim blnHasFieldNames As Boolean
    
    
    blnHasFieldNames = True
    
    strPath = "Z:\Danny Tool Test Folder\"
    
    strTable = "tblDatabase"
    
    strFile = Dir(strPath & "Monthly Reporting Tool.xlsm")
    Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames
    
    Loop
    
End Sub

So would you suggest removing the 'Do While' loop and leaving the rest as is? — user11091170
– user11091170, Commented Mar 18, 2021 at 12:51

HansUp · Accepted Answer · 2021-03-18 13:17:53Z

1

The code calls TransferSpreadsheet from within an infinite loop. The loop never terminates because strFile never changes, so Len(strFile) > 0 is always true.

However you want to import the Excel data once, so don't call TransferSpreadsheet in a loop.

strFile = Dir(strPath & "Monthly Reporting Tool.xlsm")
'Do While Len(strFile) > 0 '*** disable or remove this line ***'
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    strTable, strPathFile, blnHasFieldNames
'Loop '*** disable or remove this line ***'

answered Mar 18, 2021 at 13:17

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Exporting data to Access using VBA (looping issue)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related