How do I import a file into MS Access when the filename has a timestamp appended to the filename.?

Question

I currently have a procedure that loops thru a recordset to determine
what files need to be loaded to my database. The naming convention of
the files has always been accounts.txt, namelist.txt, etc. Within the
procedure I also validate that the file loaded to the server was loaded
within the current day. Everything has run smooth for quite some time
using this method.

Now the vendor has told me he has choice, but to the send the file to
me like accountmmddyyhhmmss.txt. How can I load these files within my
procedure now that a time-stamp has been appended to the file name ? My procedure knows the filename only as account.txt not
accountmmddyyhhmmss.txt.

Thanks in advance !

It is written in MS Access/VB 6.0.

Note the file sits out on the LAN as account011506024800.txt. I now need to load the file, but my procedure is looking to load account.txt. How do I change the procedure to indicate that file account011506024800.txt should be loaded. I will never know the exact time of the file so perhaps I just need to use a wildcard character ?

Answer 1

It looks like you are going to have to "find" the file, but it depends on what your "procedure" is written in.

Are you using Visual Basic 6.0? Visual Studio .Net? MS-Access with VBA? A macro?

Please post your code, if any.


------

Yuck. That is a nasty problem!!! Your vendor came up with an ugly naming scheme.

Dim MyFilePath as String, MyFileName as String, FileNameTimeStamp as String
Dim MyDate as Date, TestDate as Date

MyDate = DateValue("1/1/2000")

MyFilePath = "C:\"
MyFileName = Dir(MyFilePath & "account*.txt", vbHidden)

Do While MyFileName <> ""
If MyFileName <> "." And MyFileName <> ".." and Len(MyFileName) >= 19 Then
FileNameTimeStamp = Mid(MyFileName,8,12)
TestDate = DateValue( Mid(MyFileName,1,2) & "/" & Mid(MyFileName,3,2) & "/20" & Mid(MyFileName,5,2) & " " & Mid(MyFileName,7,2) & ":" & Mid(MyFileName,9,2) & ":" & Mid(MyFileName,11,2) )
If TestDate > MyDate Then
MyDate = TestDate
End If
End If
MyFileName = Dir
Loop

Dim YouWantThisFile as String
YouWantThisFile = "account" & Format(MyDate,"MMddyyhhmmss") & ".txt"

Answer 2

Can you tell the procedure to use a wildcard or look for a partial match, like account*.txt? Or is it possible to manually rename the file before the procedure runs?

Answer 3

You rename it in the program. I don't recall the exact syntax but something like:

file.copy("accounts.txt", "accounts" & NOW & ".txt"

where the first part of the argument is the file to be copied and the second is what to name the new copy. You might need to format the NOW part to conform to your mmddyyhhmmss format.

Answer 4

it will be very demanding for everybody to respond to your question in case you do not specify the operating equipment you're operating in. Why not basically set the call to the creation date at the same time as the record is created...