Dear All,
I am new to this forum and hoping, after many attempts to get the code right, to find someone with the required expertise.
I have a form with a button and 'on click' vba code for importing dbase files filtered by a parameter query. The routine to import works well, but only imports the dbf file(s) in the first directory path it comes to, even if there is more than one. The query (with date dialog parameter gathered from the form) filters and selects dbf files that exist in multiple folders. Each directory path is different and may contain many dbf files for import. I have a delete query that removes the data imported once it has been used. The following code works well but only imports the first dbf file in the first directory that the query selects, not the dbf files in the other directory paths selected by the query. How can I modify the code to get it to check all directory paths in the record set? Many thanks in advance for any help you can give.
Here is the code:
Private Sub cmdImport_Click()
On Error GoTo ErrHandler
Dim oFSystem As Object
Dim oFolder As Object
Dim oFile As Object
Dim sFile As String
Dim sFolderPath As String
Dim SQL As String
Dim i As Integer
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("qryDBFilesForImport")
sFolderPath = rst("DBPath")
sFile = rst("DBFile")
Set oFSystem = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSystem.getfolder(sFolderPath)
Set oFile = oFSystem.getfile(sFile)
Do
Do Until sFolderPath = ""
For Each oFile In oFolder.files
If Right(oFile.Name, 4) = ".dbf" And oFile = sFile Then
SQL = "Insert into [tblDBFImportTemp]" _
& " Select """ & Left(oFile.Name, 7) & """ as [Key],*" _
& " from " & Left(oFile.Name, Len(oFile.Name) - 4) _
& " IN """ & sFolderPath & """ ""dBASE 5.0;"""
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
i = i + 1
End If
Next
MsgBox i & "files imported", vbInformation, "Monitoring Database"
Exit Sub
ErrHandler:
MsgBox Err.Description
Loop
Loop
End Sub
I am new to this forum and hoping, after many attempts to get the code right, to find someone with the required expertise.
I have a form with a button and 'on click' vba code for importing dbase files filtered by a parameter query. The routine to import works well, but only imports the dbf file(s) in the first directory path it comes to, even if there is more than one. The query (with date dialog parameter gathered from the form) filters and selects dbf files that exist in multiple folders. Each directory path is different and may contain many dbf files for import. I have a delete query that removes the data imported once it has been used. The following code works well but only imports the first dbf file in the first directory that the query selects, not the dbf files in the other directory paths selected by the query. How can I modify the code to get it to check all directory paths in the record set? Many thanks in advance for any help you can give.
Here is the code:
Private Sub cmdImport_Click()
On Error GoTo ErrHandler
Dim oFSystem As Object
Dim oFolder As Object
Dim oFile As Object
Dim sFile As String
Dim sFolderPath As String
Dim SQL As String
Dim i As Integer
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("qryDBFilesForImport")
sFolderPath = rst("DBPath")
sFile = rst("DBFile")
Set oFSystem = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSystem.getfolder(sFolderPath)
Set oFile = oFSystem.getfile(sFile)
Do
Do Until sFolderPath = ""
For Each oFile In oFolder.files
If Right(oFile.Name, 4) = ".dbf" And oFile = sFile Then
SQL = "Insert into [tblDBFImportTemp]" _
& " Select """ & Left(oFile.Name, 7) & """ as [Key],*" _
& " from " & Left(oFile.Name, Len(oFile.Name) - 4) _
& " IN """ & sFolderPath & """ ""dBASE 5.0;"""
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
i = i + 1
End If
Next
MsgBox i & "files imported", vbInformation, "Monitoring Database"
Exit Sub
ErrHandler:
MsgBox Err.Description
Loop
Loop
End Sub
