code to scan the import folder and import every file.
it overwrites every file to ImportFile.csv which is linked in the db as a table.
Code:
Public Sub ScanAllFilesInDir(ByVal pvDir)
Dim vFil, vTargT
Dim i As Integer
Dim fso
Dim oFolder, oFile
Dim vSrc
const kTARG = "\\server\FOLDER\ImportFile.csv"
dim vCode, vType
On Error GoTo errImp
If Right(pvDir, 1) <> "" Then pvDir = pvDir & ""
sTbl = "xlFile"
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)
For Each oFile In oFolder.Files
vFil = pvDir & oFile.Name
If InStr(vFil, ".csv") > 0 Then
vSrc = pvDir & oFile.Name
filecopy vSrc, kTARG
i= instr(oFile.name,"_")
vCode = left(ofile.name,i-1) 'code on front of file
vType = mid(ofile.name,i+1) 'services, financials, or keypeople
select case vType
case "Services_csv"
docmd.RunQuery "qaImportServices"
case "financials_csv"
docmd.RunQuery "qaImportFinancial"
case "keypeople_csv"
docmd.RunQuery "qaImportPeople"
end select
End If
Next
Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
MsgBox "Done"
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
Exit Sub
Resume
End Sub