Thanks, ranman256. This method allowed me to use the network drive full path, rather than having to use a mapped drive letter, which could've made it difficult for other people to use who don't have it mapped to the same letter, and I was able to use the code you provided, with some modifications:
Code:
'submit the starting folder
'---------------
'Changed to function, since was causing error with Exit Function later
Public Function ScanFilesInDir(ByVal pvDir)
'---------------
Dim vFil, vTargT
Dim vDate As Date
Dim i As Integer
Dim fso As Object
Dim oFolder, oFile
Dim vSrc
Dim colFiles As New Collection
'Added SQL variabel since I'm using to addend to a table.
Dim sQL As String
On Error GoTo errImp
If IsNull(pvDir) Then Exit Function
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)
For Each oFile In oFolder.Files
vFil = oFile.Name 'pvDir & oFile.Name
'Added date last modified variable
vDate = oFile.DateLastModified
vSrc = pvDir & oFile.Name
'do something with the file
'Used the sSQL string to insert into the table
sSQL = "INSERT INTO [tbl-Clinicals] (filename,docdate) VALUES(""" & vFil & """, """ & vDate & """)"
DoCmd.RunSQL (sSQL)
Next
Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
Exit Function
errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
Exit Function
Resume
End Function
The above got me a lot further than previous. At this point the only problems are the following:
For a large batch of new files, I have to approve each append query
How do I skip files already added to the table?
For the first, is there some way to create a temporary recordset, so I could add each to that, then append all at once?
For the second, I tried to use the following, but ran into issues with where to place the End If:
Code:
If DCOUNT("[filename]", "tbl-Clinicals", oFile.Name) > 0 Then Resume Next