The ol' Listfiles Timestamp Problem - Again

[cassius]

//Edited to include Error Msg returned//

I can't figure out why this code returns a zero-length string for TempFileName, resulting in error after rs.Update.
Declaring

Const sPath = "D:\Folder\"

works great but when I try to use

Dim sFile As Variant
sPath = Application.CurrentProject.Path

Error Msg
3315 'Field timeline_summaryTimestamp.TempFileName' cannot be zero length string

is returned.

Thanks in advance for your able advice & assistance!


****************************
Public Function Listfiles()
On Error GoTo Err_ListFiles

Dim sPath As Variant
Dim sFile As Variant

sPath = Application.CurrentProject.Path

CurrentDb.Execute "Delete timeline_summaryTimestamp.* from timeline_summaryTimestamp;"
Set rs = CurrentDb.OpenRecordset("timeline_summaryTimestamp")

sFile = Dir(sPath & "TestFile.xlsx")

rs.AddNew
rs!TempFilePath = sPath
rs!TempFileName = sFile
rs!TempFileDate = FileDateTime(sPath & sFile)
rs.Update
sFile = Dir

rs.Close

Set rs = Nothing

Exit_ListFiles:
Exit Function

Err_ListFiles:
MsgBox Err.Number & " " & Err.Description
Resume Exit_ListFiles

End Function
****************************

 

[JANR]

CurrentDb.Execute "Delete timeline_summaryTimestamp.* from timeline_summaryTimestamp;"
Set rs = CurrentDb.OpenRecordset("timeline_summaryTimestamp ")
 
sFile = Dir(sPath & "TestFile.xlsx")
[COLOR=red]Debug.Print sFile[/COLOR]
rs.AddNew
rs!TempFilePath = sPath

If you add the line in red, what is displayed in the immediate window?

Perhaps a missing "\" ? or nothing since the DIR function can't find the file

JR

 

[cassius]

JR - Thanks for the quick reply!
I use MsgBox sFile instead of Debug.Print sFile and get NOTHING.
MsgBox sPath returns the correct path.
If I change the path, I get error File Not Found, so I think
it finds the file but returns a zero length string (for whatever reason . . .)
I have tried all sorts of combinations of "\" as well.

 

[vbaInet]

It's this:

sFile = Dir(sPath & "\TestFile.xlsx")

You should be checking if the file exists first before proceeding with the recordset operations:

NB: Avoid declaring variables as variant. Explicitly declare what datatype it should hold, in your case String. They are certain circumstances where you need to use variants but yours isn't one.

Dim sPath as string

sPath = Application.CurrentProject.Path & "\" & "TestFile.xlsx"

if Dir(sPath) = "" then
    exit function
end if

CurrentDb.Execute "Delete timeline_summaryTimestamp.* from timeline_summaryTimestamp;"
Set rs = CurrentDb.OpenRecordset("timeline_summaryTimestamp")

with rs
    .AddNew
    !TempFilePath = Application.CurrentProject.Path
    !TempFileName = sFile
    !TempFileDate = FileDateTime(sPath & sFile)
    .Update
end with

rs.Close
Set rs = Nothing

Also, saving the path to your db isn't a good idea. Just use CurrentProject.Path everytime you want to call a file.

 

[ghudson]

Your code looks similar to what I have posted in my Browsing sample. If you really want a listing of files in a directory then you do not need the sFile = Dir(sPath & "TestFile.xlsx") line. I see some other parts that do not look right. Check this out and tweak it for your needs and see if it works for you...

Private Function FilesAndDetails()
On Error GoTo Err_FilesAndDetails

    Dim rs As Recordset
    Dim sDir As String
    Dim sPath As String
    
    sPath = CurrentProject.Path & "\"
    
    CurrentDb.Execute "Delete * from tFiles;"
    
    Set rs = CurrentDb.OpenRecordset("tFiles")
    
    sDir = Dir(sPath & "*.*")
    Do Until sDir = ""
        rs.AddNew
        rs!FilePathName = sPath & sDir
        rs!FilePath = sPath
        rs!FileName = sDir
        rs!ModifiedDate = FileDateTime(sPath & sDir)
        rs!FileSize = FileLen(sPath & sDir)
        rs.Update
        sDir = Dir
    Loop
    
    rs.Close
    Set rs = Nothing

    DoCmd.Requery

Exit_FilesAndDetails:
    Exit Function
    
Err_FilesAndDetails:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_FilesAndDetails

End Function

 

[cassius]

So much to learn, so little time . . .

The key to solving my mystery was adding the backslash string to sPath.

sPath = CurrentProject.Path & "\"

Especial thanks to JANR - vbaInet - ghudson!

You have added to my limited knowledge and restored my sanity!

 

[vbaInet]

Glad we could help!