Hi Jon,
First, a couple of comments...
1.) ...and put it into a field named "Date"
Date is considered a reserved word. You should avoid using any reserved
words when assigning names to anything in Access (fields, tables, queries,
forms, reports, macros, modules, variables, controls on forms & reports,
etc.). Access MVP Allen Browne maintains an extensive list of reserved words
here:
Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html
He also offers a free utility, called the "Database Issue Checker Utility".
This is a .mdb file, which you can use to check your existing Access
databases for reserved words.
Notes:
It covers fields, tables and queries only (not controls, variable names, etc.)
Your database should be closed when using this utility to test your database.
2.) Dim rs As Recordset
You should explicitly declare recordsets as either DAO or ADO recordset, to
avoid the possibility of run-time error 13: Type Mismatch. More information
here:
ADO and DAO Library References in Access Databases
http://www.accessmvp.com/TWickerath/articles/adodao.htm
3.) DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tbljpgfiles"
DoCmd.SetWarnings True
This line of code accomplishes the same thing, and you don't have to worry
about warnings getting turned back on, in the event that the DoCmd.RunSQL
line of code errors out:
db.Execute "DELETE * FROM tbljpgfiles", dbFailOnError
4.) You should close DAO recordsets, in addition to setting the variable to
nothing, in order to help prevent bloat:
How to prevent database bloat after you use Data Access Objects (DAO)
http://support.microsoft.com/kb/289562
With that in mind, you can use the built-in FileDateTime function to return
the date and time when a file was created or last modified. Here is an edited
version of your procedure. I used the field name "FileLastModified" to record
the date. I also defined a new string variable for the file extension, so
that I wouldn't have to later remove the *.jpg from the strMyPath value.
Sub GetFilenames()
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMyPath As String
Dim strExt As String
Dim strMyFile As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblJPGFiles")
db.Execute "DELETE * FROM tbljpgfiles", dbFailOnError
'type in the real path in the next line
strMyPath = "c:\"
strExt = "*.jpg"
strMyFile = Dir(strMyPath & strExt)
Do While strMyFile <> ""
rs.AddNew
rs("FileName") = strMyFile
rs("FileLastModified") = FileDateTime(strMyPath & strMyFile)
rs.Update
'get the next file name
strMyFile = Dir
Loop
MsgBox rs.RecordCount & " files were logged.", _
vbInformation, "Done..."
ExitProc:
'Cleanup
If Not rs Is Nothing Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure GetFilenames..."
Resume ExitProc
End Sub
Notes:
The strMyPath needs to have a trailing slash included. I'll leave that as a
programming challenge to you, to check for a trailing slash and add one if it
is missing.
You might also use code to allow a user to browse to a folder, instead of
hard-coding the strMyPath value.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
