Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time of link text file (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Time of link text file (2003)

    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:
    <code>
    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
    </code>
    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.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time of link text file (2003)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Time of link text file (2003)

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

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time of link text file (2003)

    Thank you so much, Hans.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time of link text file (2003)

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Time of link text file (2003)

    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.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time of link text file (2003)

    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Time of link text file (2003)

    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.

  10. #10
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time of link text file (2003)

    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,

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Time of link text file (2003)

    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.

  12. #12
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time of link text file (2003)

    Thanks, Hans.

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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Time of link text file (2003)

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

  14. #14
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time of link text file (2003)

    Thanks, Hans.

  15. #15
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time of link text file (2003)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •