Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Access VBA SQL - Endless Looop
I'm using the code below and it's in a never ending loop. Thoughts
Public Function Import_System_Access_Reports()
Dim strFolder As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strFile As String
Dim strTable As String
Dim strExtension As String
Dim lngFileType As Long
Dim strSQL As String
Dim strFullFileName As String
Dim varPieces As Variant
With Application.FileDialog(4) ' msoFileDialogFolderPicker
If .Show Then
strFolder = .SelectedItems(1)
Else
MsgBox "No folder specified!", vbCritical
Exit Function
End If
End With
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If
strFile = Dir(strFolder & "*.xls*")
Do While strFile <> ""
lngPos = InStrRev(strFile, ".")
strTable = "RawData" '<- this could be a constant instead of a variable
Set db = CurrentDb()
' make the UPDATE a parameter query ...
strSQL = "UPDATE [" & strTable & "] SET FileName=[pFileName]" & vbCrLf & _
"WHERE FileName Is Null OR FileName='';"
Set qdf = db.CreateQueryDef(vbNullString, strSQL)
strFile = Dir(strFolder & "*.xls*")
Do While Len(strFile) > 0
varPieces = Split(strFile, ".")
strExtension = varPieces(UBound(varPieces))
Select Case strExtension
Case "xls"
lngFileType = acSpreadsheetTypeExcel9
Case "xlsx", "xlsm"
lngFileType = acSpreadsheetTypeExcel12Xml
Case "xlsb"
lngFileType = acSpreadsheetTypeExcel12
End Select
strFullFileName = strFolder & strFile
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=lngFileType, _
TableName:=strTable, _
Filename:=strFullFileName, _
HasFieldNames:=True ' or False if no headers
' supply the parameter value for the UPDATE and execute it ...
qdf.Parameters("pFileName").Value = strFile
qdf.Execute dbFailOnError
Loop
'Move to the next file
strFile = Dir
Loop
'Clean up
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
'rstTable.Close
Set rstTable = Nothing
End Function
Who is Participating?
Dir() will move to the next file, but within your loop, you are using
Dir(strFolder & "*.xls*")
which resets the Dir command back to the first file fitting the pattern.
Maybe you need to move the Dir(strFolder & "*.xls*") outside of the loop?
Dir(strFolder & "*.xls*")
which resets the Dir command back to the first file fitting the pattern.
Maybe you need to move the Dir(strFolder & "*.xls*") outside of the loop?
That code has a number of problems, one of which is you have 2 Dir() commands and loops getting files nested inside each other. That doesn't work, you can't nest those and have them work correctly. What are you trying to do in the code?
strFile = Dir(strFolder & "*.xls*")
»bp
strFile = Dir(strFolder & "*.xls*")
»bp
Also initialize the query before you enter the loop. It's only required once and especially calling CurrentDb is pretty slow. I would also move the entire TransferSpreadsheet with the Select to its own method to increase readbility and for better error handling.
There were also some declarations missing and some not necessary. I would do it like that:
btw, Len(Trim(FileName & "")) = 0 is normally a better test to catch also strings with spaces only.
Option Compare Database
Option Explicit
Private Const TABLE_NAME As String = "RawData"
Public Sub Test()
Dim Folder As String
With Application.FileDialog(4) ' msoFileDialogFolderPicker
If .Show Then
Folder = .SelectedItems(1)
ImportSystemAccessReports Folder
Else
MsgBox "No folder specified!", vbCritical
End If
End With
End Sub
Private Function ImportFile(AFile As String) As Boolean
On Local Error GoTo LocalError
Dim FilenameParts() As String
Dim Extension As String
Dim SpreadsheetType As Long
FilenameParts() = Split(AFile, ".")
Extension = FilenameParts(UBound(FilenameParts))
Select Case Extension
Case "xls"
SpreadsheetType = acSpreadsheetTypeExcel9
Case "xlsx", "xlsm"
SpreadsheetType = acSpreadsheetTypeExcel12Xml
Case "xlsb"
SpreadsheetType = acSpreadsheetTypeExcel12
Case Else
Exit Function
End Select
DoCmd.TransferSpreadsheet acImport, SpreadsheetType, TABLE_NAME, AFile, True
ImportFile = True
Exit Function
LocalError:
MsgBox "ImportFile:" & Err.Number & " - " & Err.Description
End Function
Public Function ImportSystemAccessReports(AFolder As String)
Const SQL_UPDATE As String = _
"UPDATE {TableName} " & _
"SET FileName = [pFileName] " & _
"WHERE Len(Trim(FileName & '')) = 0;"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim File As String
Set db = CurrentDb()
Set qdf = db.CreateQueryDef(vbNullString, Replace(SQL_UPDATE, "{TableName}", TABLE_NAME))
If Right(AFolder, 1) <> "\" Then
AFolder = AFolder & "\"
End If
File = Dir(AFolder & "*.xls*")
Do While File <> ""
If ImportFile(AFolder & File) Then
qdf.Parameters("pFileName").Value = File
qdf.Execute dbFailOnError
End If
File = Dir
Loop
Set qdf = Nothing
Set db = Nothing
End Function
btw, Len(Trim(FileName & "")) = 0 is normally a better test to catch also strings with spaces only.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
Open in new window
»bp