List files to table and include date modified (1 Viewer)

hubelea

Registered User.
Local time
Today, 12:55
Joined
Nov 9, 2011
Messages
33
Help. I'm using Allen Browne's code to list files to a table. I need to include a field that contains the date the file was modified. I've added the field to the Files table and named it fModified, and set the datatype to date/time. Below is the code (which works but doesn't pull in my new fModified field). Can someone show me how to tweak this code to pull in the new field? I'm not a vba expert - but I'm really good at copying and pasting :).

Option Compare Database
Option Explicit

'list files to tables
'http://allenbrowne.com/ser-59alt.html

Dim gCount As Long ' added by Crystal

Sub runListFiles()
'Usage example.
Dim strPath As String _
, strFileSpec As String _
, booIncludeSubfolders As Boolean

strPath = "E:"
strFileSpec = "*.*"
booIncludeSubfolders = True

ListFilesToTable strPath, strFileSpec, booIncludeSubfolders
End Sub

'crystal modified parameter specification for strFileSpec by adding default value
Public Function ListFilesToTable(strPath As String _
, Optional strFileSpec As String = "*.*" _
, Optional bIncludeSubfolders As Boolean _
)
On Error GoTo Err_Handler
'Purpose: List the files in the path.
'Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
'Method: FilDir() adds items to a collection, calling itself recursively for subfolders.

Dim colDirList As New Collection
Dim varitem As Variant
Dim rst As DAO.Recordset

Dim mStartTime As Date _
, mSeconds As Long _
, mMin As Long _
, mMsg As String

mStartTime = Now()
'--------

Call FillDirToTable(colDirList, strPath, strFileSpec, bIncludeSubfolders)

mSeconds = DateDiff("s", mStartTime, Now())

mMin = mSeconds \ 60
If mMin > 0 Then
mMsg = mMin & " min "
mSeconds = mSeconds - (mMin * 60)
Else
mMsg = ""
End If

mMsg = mMsg & mSeconds & " seconds"

MsgBox "Done adding " & Format(gCount, "#,##0") & " files from " & strPath _
& IIf(Len(Trim(strFileSpec)) > 0, " for file specification --> " & strFileSpec, "") _
& vbCrLf & vbCrLf & mMsg, , "Done"

Exit_Handler:
SysCmd acSysCmdClearStatus
'--------

Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "ERROR"

'remove next line after debugged -- added by Crystal
Stop: Resume 'added by Crystal

Resume Exit_Handler
End Function

Private Function FillDirToTable(colDirList As Collection _
, ByVal strFolder As String _
, strFileSpec As String _
, bIncludeSubfolders As Boolean)

'Build up a list of files, and then add add to this list, any additional folders
On Error GoTo Err_Handler

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim strSQL As String

'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
gCount = gCount + 1
SysCmd acSysCmdSetStatus, gCount
strSQL = "INSERT INTO Files " _
& " (FName, FPath) " _
& " SELECT """ & strTemp & """" _
& ", """ & strFolder & """;"
CurrentDb.Execute strSQL
colDirList.Add strFolder & strTemp
strTemp = Dir
Loop

If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
Next vFolderName
End If

Exit_Handler:

Exit Function

Err_Handler:
strSQL = "INSERT INTO Files " _
& " (FName, FPath) " _
& " SELECT "" ~~~ ERROR ~~~""" _
& ", """ & strFolder & """;"
CurrentDb.Execute strSQL

Resume Exit_Handler
End Function

Public Function TrailingSlash(varIn As Variant) As String
If Len(varIn) > 0& Then
If Right(varIn, 1&) = "" Then
TrailingSlash = varIn
Else
TrailingSlash = varIn & ""
End If
End If
End Function
 

Solo712

Registered User.
Local time
Today, 13:55
Joined
Oct 19, 2012
Messages
776
Help. I'm using Allen Browne's code to list files to a table. I need to include a field that contains the date the file was modified. I've added the field to the Files table and named it fModified, and set the datatype to date/time. Below is the code (which works but doesn't pull in my new fModified field). Can someone show me how to tweak this code to pull in the new field? I'm not a vba expert - but I'm really good at copying and pasting :).

Option Compare Database
Option Explicit

'list files to tables
'http://allenbrowne.com/ser-59alt.html

Dim gCount As Long ' added by Crystal

Sub runListFiles()
'Usage example.
Dim strPath As String _
, strFileSpec As String _
, booIncludeSubfolders As Boolean

strPath = "E:"
strFileSpec = "*.*"
booIncludeSubfolders = True

ListFilesToTable strPath, strFileSpec, booIncludeSubfolders
End Sub

'crystal modified parameter specification for strFileSpec by adding default value
Public Function ListFilesToTable(strPath As String _
, Optional strFileSpec As String = "*.*" _
, Optional bIncludeSubfolders As Boolean _
)
On Error GoTo Err_Handler
'Purpose: List the files in the path.
'Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
'Method: FilDir() adds items to a collection, calling itself recursively for subfolders.

Dim colDirList As New Collection
Dim varitem As Variant
Dim rst As DAO.Recordset

Dim mStartTime As Date _
, mSeconds As Long _
, mMin As Long _
, mMsg As String

mStartTime = Now()
'--------

Call FillDirToTable(colDirList, strPath, strFileSpec, bIncludeSubfolders)

mSeconds = DateDiff("s", mStartTime, Now())

mMin = mSeconds \ 60
If mMin > 0 Then
mMsg = mMin & " min "
mSeconds = mSeconds - (mMin * 60)
Else
mMsg = ""
End If

mMsg = mMsg & mSeconds & " seconds"

MsgBox "Done adding " & Format(gCount, "#,##0") & " files from " & strPath _
& IIf(Len(Trim(strFileSpec)) > 0, " for file specification --> " & strFileSpec, "") _
& vbCrLf & vbCrLf & mMsg, , "Done"

Exit_Handler:
SysCmd acSysCmdClearStatus
'--------

Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "ERROR"

'remove next line after debugged -- added by Crystal
Stop: Resume 'added by Crystal

Resume Exit_Handler
End Function

Private Function FillDirToTable(colDirList As Collection _
, ByVal strFolder As String _
, strFileSpec As String _
, bIncludeSubfolders As Boolean)

'Build up a list of files, and then add add to this list, any additional folders
On Error GoTo Err_Handler

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim strSQL As String

'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
gCount = gCount + 1
SysCmd acSysCmdSetStatus, gCount
strSQL = "INSERT INTO Files " _
& " (FName, FPath) " _
& " SELECT """ & strTemp & """" _
& ", """ & strFolder & """;"

CurrentDb.Execute strSQL
colDirList.Add strFolder & strTemp
strTemp = Dir
Loop

If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
Next vFolderName
End If

Exit_Handler:

Exit Function

Err_Handler:
strSQL = "INSERT INTO Files " _
& " (FName, FPath) " _
& " SELECT "" ~~~ ERROR ~~~""" _
& ", """ & strFolder & """;"""

CurrentDb.Execute strSQL
Resume Exit_Handler
End Function

Public Function TrailingSlash(varIn As Variant) As String
If Len(varIn) > 0& Then
If Right(varIn, 1&) = "" Then
TrailingSlash = varIn
Else
TrailingSlash = varIn & ""
End If
End If
End Function

Hi,
assuming you added the column to the Files table, you just need to add the date modified property to the SQL text in the highlighted sections in this fashion:
Code:
 strSQL = "INSERT INTO Files " _
& " (FName, FModified, FPath) " _
& " SELECT """ & strTemp & """" _
& ", & ", """ & strTemp.DateLastModified & """ _
& ", """ & strFolder & """;"

and in the error handler
Code:
strSQL = "INSERT INTO Files " _
    & " (FName, FModified, FPath) " _
    & " SELECT ""  ~~~ ERROR ~~~""" _ 
    & ""  ~~~ ERROR ~~~""" _
    & ", """ & strFolder & """;"
Best,
Jiri
 

hubelea

Registered User.
Local time
Today, 12:55
Joined
Nov 9, 2011
Messages
33
Thank you! I copied and pasted and got a compile error?
 

hubelea

Registered User.
Local time
Today, 12:55
Joined
Nov 9, 2011
Messages
33
Should have been more specific - compile - syntax error?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:55
Joined
Oct 29, 2018
Messages
18,788
Hi,

Pardon me for jumping in, but could you try using this instead?

FileDateTime(strFolder & strTemp)

Hope it helps...
 
Last edited:

hubelea

Registered User.
Local time
Today, 12:55
Joined
Nov 9, 2011
Messages
33
Sorry, the code that was highlighted by the error was the new code that was added. Appears to be this line:

& ", & ", """ & strTemp.DateLastModified & """ _
 

hubelea

Registered User.
Local time
Today, 12:55
Joined
Nov 9, 2011
Messages
33
Hi,

Pardon me for jumping in, but could you try using this instead?

FileDateTime(strFolder & strTemp)

Hope it helps...


Thanks, theDBguy! Where does this code go? What does it replace? Thank you!
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:55
Joined
Sep 21, 2011
Messages
10,339
Would not the variable need to have # surrounding it.?

Certainly the FileDateTime returns a Date value, type 7 ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:55
Joined
Oct 29, 2018
Messages
18,788
Thanks, theDBguy! Where does this code go? What does it replace? Thank you!

Hi,

I was saying to replace strTemp.DateLastModified with the one I posted.
 

hubelea

Registered User.
Local time
Today, 12:55
Joined
Nov 9, 2011
Messages
33
OK, I may have gotten (with lots of your help) the first section of code corrected, it now looks like this:

strSQL = "INSERT INTO Files " _
& " (FName, FModified, FPath) " _
& " SELECT """ & strTemp & """" _
& " & " & """ & FileDateTime(strFolder & strTemp) & """ _
& ", """ & strFolder & """;"

The code now runs all the way to the next section that we added, in the error handling, and I'm getting a syntax error there. Its not pointing to me which line....

Here is what I have:

Err_Handler:
strSQL = "INSERT INTO Files " _
& " (FName, FModified, FPath) " _
& " SELECT "" ~~~ ERROR ~~~""" _
& "" ~~~ ERROR ~~~""" _
& ", """ & strFolder & """;"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:55
Joined
Oct 29, 2018
Messages
18,788
Hi,

Try changing it to this:

Code:
strSQL=“ INSERT INTO Files “ _
  & “ (FName, FModified, FPath) “ _
  & “ VALUES(‘~Error~’,’~Error~’,’” _
  & strFolder & “‘)”
Sent from phone...

PS. My phone uses smart quotes, so make sure you type the above manually into your database. Don’t copy and paste.
 

hubelea

Registered User.
Local time
Today, 12:55
Joined
Nov 9, 2011
Messages
33
Thank you. The last suggested change, error handler syntax changed to this:

Err_Handler:
strSQL = "INSERT INTO Files " _
& " (FName, Fmodified, FPath) " _
& " VALUES('~ERROR','~Error~','" _
& strFolder & "')"
CurrentDb.Execute strSQL

This runs the code with no errors, but only brings in 12 files to the table (there are thousands), with ~ERROR in the fName fields, nothing in the FModified fields, and what appears to be correct info in the fpath field (although without the fname field its difficult to be sure...)

Any other suggestions, please? (Gosh it doesn't seem like making this change should be so hard, no wonder why I am no good at coding!)
 

Solo712

Registered User.
Local time
Today, 13:55
Joined
Oct 19, 2012
Messages
776
Hello hubelea,

My apologies for the earlier post. I felt sure I was looking at an object. At any rate, I went back and fixed the routine. Took a while to figure out but there was a problem with the code posted in the OP. (Was that the original code by Allen?) The errors were generated by the trailing slash routine....At any, rate here is the files report generator with the date stamp added....

Code:
Sub runListFiles()
'Usage example.
Dim strPath As String _
, strFileSpec As String _
, booIncludeSubfolders As Boolean

strPath = "C:\Users\mary\"
strFileSpec = "*.*"
booIncludeSubfolders = True
'CurrentDb.Execute "DELETE from Files"

ListFilesToTable strPath, strFileSpec, booIncludeSubfolders
End Sub

'crystal modified parameter specification for strFileSpec by adding default value
Public Function ListFilesToTable(strPath As String _
, Optional strFileSpec As String = "*.*" _
, Optional bIncludeSubfolders As Boolean _
)
'---------------------------------------------------------------------------------
On Error GoTo Err_Handler
'Purpose: List the files in the path.
'Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
'Method: FilDir() adds items to a collection, calling itself recursively for subfolders.

Dim colDirList As New Collection
Dim varitem As Variant
Dim rst As DAO.Recordset

Dim mStartTime As Date _
, mSeconds As Long _
, mMin As Long _
, mMsg As String

mStartTime = Now()
'--------

Call FillDirToTable(colDirList, strPath, strFileSpec, bIncludeSubfolders)

mSeconds = DateDiff("s", mStartTime, Now())

mMin = mSeconds \ 60
If mMin > 0 Then
mMsg = mMin & " min "
mSeconds = mSeconds - (mMin * 60)
Else
mMsg = ""
End If

mMsg = mMsg & mSeconds & " seconds"

MsgBox "Done adding " & Format(gCount, "#,##0") & " files from " & strPath _
& IIf(Len(Trim(strFileSpec)) > 0, " for file specification --> " & strFileSpec, "") _
& vbCrLf & vbCrLf & mMsg, , "Done"

Exit_Handler:
SysCmd acSysCmdClearStatus
'--------

Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "ERROR"

'remove next line after debugged -- added by Crystal
Stop: Resume 'added by Crystal

Resume Exit_Handler
End Function

Private Function FillDirToTable(colDirList As Collection _
, ByVal strFolder As String _
, strFileSpec As String _
, bIncludeSubfolders As Boolean)
'-----------------------------------------------------------

'Build up a list of files, and then add add to this list, any additional folders
On Error GoTo Err_Handler

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim strSQL As String, fDateTime As Date

'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
gCount = gCount + 1
SysCmd acSysCmdSetStatus, gCount

strSQL = "INSERT INTO Files " _
& " (FName, FDateCreated, FPath) " _
& " VALUES('" & strTemp & "', '" _
& FileDateTime(strFolder & strTemp) & "', '" _
& strFolder & "')"
CurrentDb.Execute strSQL
colDirList.Add strFolder & strTemp
strTemp = Dir
Loop

If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
Next vFolderName
End If

Exit_Handler:

Exit Function

Err_Handler:
strSQL = " INSERT INTO Files " _
  & " (FName, FDateCreated, FPath) " _
  & " VALUES('~Error~','~Error~','" _
  & strFolder & "')"
CurrentDb.Execute strSQL

Resume Exit_Handler
End Function
Public Function TrailingSlash(ss As Variant) As String
    If Len(ss) > 0 Then
      If Right(ss, 1) <> "\" Then
        TrailingSlash = ss & "\"
      Else
        TrailingSlash = ss & ""
      End If
    End If
End Function

I hope this works !

Best,
Jiri
 

hubelea

Registered User.
Local time
Today, 12:55
Joined
Nov 9, 2011
Messages
33
Hello hubelea,

My apologies for the earlier post. I felt sure I was looking at an object. At any rate, I went back and fixed the routine. Took a while to figure out but there was a problem with the code posted in the OP. (Was that the original code by Allen?) The errors were generated by the trailing slash routine....At any, rate here is the files report generator with the date stamp added....

Code:
Sub runListFiles()
'Usage example.
Dim strPath As String _
, strFileSpec As String _
, booIncludeSubfolders As Boolean

strPath = "C:\Users\mary\"
strFileSpec = "*.*"
booIncludeSubfolders = True
'CurrentDb.Execute "DELETE from Files"

ListFilesToTable strPath, strFileSpec, booIncludeSubfolders
End Sub

'crystal modified parameter specification for strFileSpec by adding default value
Public Function ListFilesToTable(strPath As String _
, Optional strFileSpec As String = "*.*" _
, Optional bIncludeSubfolders As Boolean _
)
'---------------------------------------------------------------------------------
On Error GoTo Err_Handler
'Purpose: List the files in the path.
'Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
'Method: FilDir() adds items to a collection, calling itself recursively for subfolders.

Dim colDirList As New Collection
Dim varitem As Variant
Dim rst As DAO.Recordset

Dim mStartTime As Date _
, mSeconds As Long _
, mMin As Long _
, mMsg As String

mStartTime = Now()
'--------

Call FillDirToTable(colDirList, strPath, strFileSpec, bIncludeSubfolders)

mSeconds = DateDiff("s", mStartTime, Now())

mMin = mSeconds \ 60
If mMin > 0 Then
mMsg = mMin & " min "
mSeconds = mSeconds - (mMin * 60)
Else
mMsg = ""
End If

mMsg = mMsg & mSeconds & " seconds"

MsgBox "Done adding " & Format(gCount, "#,##0") & " files from " & strPath _
& IIf(Len(Trim(strFileSpec)) > 0, " for file specification --> " & strFileSpec, "") _
& vbCrLf & vbCrLf & mMsg, , "Done"

Exit_Handler:
SysCmd acSysCmdClearStatus
'--------

Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "ERROR"

'remove next line after debugged -- added by Crystal
Stop: Resume 'added by Crystal

Resume Exit_Handler
End Function

Private Function FillDirToTable(colDirList As Collection _
, ByVal strFolder As String _
, strFileSpec As String _
, bIncludeSubfolders As Boolean)
'-----------------------------------------------------------

'Build up a list of files, and then add add to this list, any additional folders
On Error GoTo Err_Handler

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim strSQL As String, fDateTime As Date

'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
gCount = gCount + 1
SysCmd acSysCmdSetStatus, gCount

strSQL = "INSERT INTO Files " _
& " (FName, FDateCreated, FPath) " _
& " VALUES('" & strTemp & "', '" _
& FileDateTime(strFolder & strTemp) & "', '" _
& strFolder & "')"
CurrentDb.Execute strSQL
colDirList.Add strFolder & strTemp
strTemp = Dir
Loop

If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
Next vFolderName
End If

Exit_Handler:

Exit Function

Err_Handler:
strSQL = " INSERT INTO Files " _
  & " (FName, FDateCreated, FPath) " _
  & " VALUES('~Error~','~Error~','" _
  & strFolder & "')"
CurrentDb.Execute strSQL

Resume Exit_Handler
End Function
Public Function TrailingSlash(ss As Variant) As String
    If Len(ss) > 0 Then
      If Right(ss, 1) <> "\" Then
        TrailingSlash = ss & "\"
      Else
        TrailingSlash = ss & ""
      End If
    End If
End Function

I hope this works !

Best,
Jiri
Solo712 you are a superhero! It is working....but unfortunately in fits and starts. I think this is my fault - it runs, pulls some files, then stops. On the table, it appears to stop with a filename descr of ~error~. After some investigation, I think its stopping when it encounters a file name with an apostrophe in it. My bad. Is there any way around this within code, or am I faced with going through 27,000 files and renaming them without apostrophes?
 

Solo712

Registered User.
Local time
Today, 13:55
Joined
Oct 19, 2012
Messages
776
Solo712 you are a superhero! It is working....but unfortunately in fits and starts. I think this is my fault - it runs, pulls some files, then stops. On the table, it appears to stop with a filename descr of ~error~. After some investigation, I think its stopping when it encounters a file name with an apostrophe in it. My bad. Is there any way around this within code, or am I faced with going through 27,000 files and renaming them without apostrophes?

Ok, you may want to try this old trick to see if the apostrophes are your problem

change the line
strTemp = Dir(strFolder & strFileSpec)
to this
strTemp = Replace(Dir(strFolder & strFileSpec), "'", "''")

Best,
Jiri
 

hubelea

Registered User.
Local time
Today, 12:55
Joined
Nov 9, 2011
Messages
33
Ok, you may want to try this old trick to see if the apostrophes are your problem

change the line
strTemp = Dir(strFolder & strFileSpec)
to this
strTemp = Replace(Dir(strFolder & strFileSpec), "'", "''")

Best,
Jiri


Made that change. It is still stopping at errors, and it still seems to be files or folders with apostrophe's.
 

Solo712

Registered User.
Local time
Today, 13:55
Joined
Oct 19, 2012
Messages
776
Made that change. It is still stopping at errors, and it still seems to be files or folders with apostrophe's.

Strange but true...I tested it and it was stopping at runtime 3075 errors if there were files or folders with apostrophes. I tried Browne's original code with the SELECT clause but it wasn't able to read those files either....it just marked them as errors....though there were no interruptions.

Code:
strSQL = "INSERT INTO Files " _
& " (FName, FDateCreated, FPath) " _
& " SELECT """ & strTemp & """" _
& ", """ & FileDateTime(strFolder & strTemp) & """" _
& ", """ & strFolder & """;"

This is about the best I could do. Good luck with it.

Best,
Jiri
 

isladogs

CID VIP
Local time
Today, 18:55
Joined
Jan 14, 2017
Messages
16,074
In similar situations, I replace apostrophes with a pipe symbol: | or ¦.
Then reverse the change afterwards.

This is a good reason to avoid non alphanumeric characters in file and folder names
 

hubelea

Registered User.
Local time
Today, 12:55
Joined
Nov 9, 2011
Messages
33
Strange but true...I tested it and it was stopping at runtime 3075 errors if there were files or folders with apostrophes. I tried Browne's original code with the SELECT clause but it wasn't able to read those files either....it just marked them as errors....though there were no interruptions.

Code:
strSQL = "INSERT INTO Files " _
& " (FName, FDateCreated, FPath) " _
& " SELECT """ & strTemp & """" _
& ", """ & FileDateTime(strFolder & strTemp) & """" _
& ", """ & strFolder & """;"

This is about the best I could do. Good luck with it.

Best,
Jiri

I wound up going through all of my files and removing the apostrophes. Needed to be done anyway. Jiri - the code you provided did the trick, it was my file naming errors that slowed me down. THANK YOU SO MUCH for all of your help!
 

Users who are viewing this thread

Top Bottom