Time of link text file (2003)

Topic

New Reply

Hi,

In my access database, I have a table link with a text file that auto generate from another system daily on random time. Is any way that I can disappear the time of last time modify the text file on of of my report in access database?

Please advise.

Thanks

Regards

Reply | Quote

Replies

Did you mean display instead of disappear?

You can use Scripting.FileSystemObject in the On Open event of the report to retrieve info about a file and to set the caption of a label:

Private Sub Report_Open(Cancel As Integer)
Dim strFile As String
Dim dtmModified As Date
strFile = "..." ' substitute the path and filename
dtmModified = CreateObject("Scripting.FileSystemObject").GetFile(strFile).DateLastModified
Me.lblDate.Caption = Format(dtmModified, "mm/dd/yyyy")
End Sub

You can use a different format, of course.

See File Object for the properties of a file, and FileSystemObject Basics for general info on FileSystemObject.

Reply | Quote

Thank you so much, Hans.

This is what I want. I also want add “Up to” before the time. Can I add in the code or I have create another lable? I also want time format shows Year, Month, Day, Hour, Mins, and AM or PM. I change it to “mm/dd/yyyy hh:mm,” but I don’t know how to show AM or PM?

Thanks

Reply | Quote

Try

Me.lblDate.Caption = "Up to " & Format(dtmModified, "mm/dd/yyyy hh:nn AM/PM")

Reply | Quote

Thank you so much, Hans.

Reply | Quote

Hi Hans,

I have 3 different databases that links this same text file. There one same filed in each form of 3 databases and the data source of the field is the links of text file. Therefore, now users can’t open a form at the same time.

What should I do to solve this?

Please advice.

Thanks

Regards

Reply | Quote

Unlike a table in an Access database, a text file is not suitable for use by multiple users at the same time. You could import the text file into each of the databases, so that each database has its own version. Or you could create copies of the text file for each of the databases.

Reply | Quote

Thanks, Hans.

This text file is auto exported from another system everyday with updated data. I link it to each database, so I don’t have to manually copy or import to each database every time.

If I create copies of the text file for each of the databases, can I make it automatic?

Thanks

Reply | Quote

You could use a scheduled task in Windows to duplicate the text file, or you could use VBA code from the databases to do that, for example when the database is opened.

Reply | Quote

Thanks, Hans.

I am perfer use VBA code from the database to do it. If I understand correctly, copy the text file will be import?

Do you have some sample codes for this process?

Thanks

Regards,

Reply | Quote

You can use code in the On Load or On Open event of the startup form of the database.

You could either import the text file into the database using DoCmd.TransferText, or copy the text file to a specific location using FileCopy.

If you search this forum for TransferText, you’ll find several code examples. The same goes for CopyFile.

Reply | Quote

Thanks, Hans.

Which one do you recommend for my purpose, import or copy the text file? Which process do you think will be quicky?

Reply | Quote

Copying the text file might be a bit faster, but unless the file is very big you probably won’t notice any difference.

Reply | Quote

Thanks, Hans.

Reply | Quote

Hi Hans,

I am using the following codes to import the text file to a table and I don’t have any problem at all.

DoCmd.SetWarnings False

‘delete any existing records
DoCmd.RunSQL “DELETE * FROM tbl_PhysiciansList”

‘Import rawData
DoCmd.TransferText acImportDelim, “Import Specification”, “tbl_datalist”, “c:datalist.txt”, False

Is there anyway to run those codes in database B if the import table is in database A?

Thanks

Reply | Quote

You’d have to use Automation to start a second instance of Access, open the other database in that instance and run the import routine there.
But I don’t see the need to import into another database…

Reply | Quote