Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Location
    Monroe, Wisconsin
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File Name Capture (2000 9.0 SP-3)

    I need to capture the text file name every time I import a file. An example would be for file PIC61420060718.txt, I want to add the PIC61420060718 to a table for user viewing.

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

    Re: File Name Capture (2000 9.0 SP-3)

    Do you mean that you want to add a record to a separate table? And do you want to store only the file name, or also other info, such as the date and time, the username, etc.?

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Location
    Monroe, Wisconsin
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Name Capture (2000 9.0 SP-3)

    I will store the specific data in another table, but I need to see the file name, so they don't import the same data twice, or forget to import it altogether. The file name is always PIC614 with a time stamp added to it.

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

    Re: File Name Capture (2000 9.0 SP-3)

    There is no trigger built into Access that you can use, so you'd have to use VBA code to import the text file and append a record to the log table.
    You can use DoCmd.TransferText to import a text file, and DoCmd.RunSQL to execute the SQL for an append query.
    I have no idea if you are comfortable with writing VBA code in Access; can you give some indication whether you need help with it and if so, how much?

  5. #5
    New Lounger
    Join Date
    Feb 2002
    Location
    Monroe, Wisconsin
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Name Capture (2000 9.0 SP-3)

    Yes, I can do the import and the queries to add the data to that tables. I don't know how to capture the file name and append it to my imported-file-history table.

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

    Re: File Name Capture (2000 9.0 SP-3)

    Is the file you import always PIC614 followed by today's date in yyyymmdd format, or do you want the user to be able to select the file?

  7. #7
    New Lounger
    Join Date
    Feb 2002
    Location
    Monroe, Wisconsin
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Name Capture (2000 9.0 SP-3)

    A file may be generated three or four times a day, so it's always PIC614, then the time stamp, as follows.


    PIC614yyyymmddhhmmss is the total format for the file name.
    PIC61420060719103508
    PIC61420060719115532
    PIC61420060719152758

    The user will select the file to import, but, as there are multiple users, I need to document which files have or have not been imported. If they miss importing the second file that day, large amounts of data could be missing.

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

    Re: File Name Capture (2000 9.0 SP-3)

    Atttached to <post#=304,810>post 304,810</post#>, you'll find an Access 97 database with a module and class module that you can import into your database. They provide an Open dialog that you can use to open a workbook. Code could look like this:

    Private Sub cmdImport_Click()
    Dim strFile As String
    Dim dlg As New CommonDialog
    Dim strSQL As String

    On Error GoTo ErrHandler
    With dlg
    .Filter = "Text files (*.txt)|*.txt"
    If .OpenDialog = True Then
    strFile = .FileName
    ' Complete the TransferText instruction using strFile as file name
    DoCmd.TransferText ...
    ' Extract only the file name
    strFile = Mid(strFile, InStrRev(strFile, "") + 1)
    strSQL = "INSERT INTO tbLog ( Filename ) VALUES( " & _
    Chr(34) & strFile & Chr(34) & " )"
    Else
    MsgBox "Action canceled.", vbExclamation
    End If
    End With

    ExitHandler:
    On Error Resume Next
    Set dlg = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    (This code is for a command button named cmdImport, and it assumes that you have a table tblLog with a text field Filename)

  9. #9
    New Lounger
    Join Date
    Feb 2002
    Location
    Monroe, Wisconsin
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Name Capture (2000 9.0 SP-3)

    Hans,

    Thanks for the help, I got most of it working, still having a little trouble with the SQL statement, but I'm sure I'll get it, thanks again.

    Greg

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

    Re: File Name Capture (2000 9.0 SP-3)

    Good luck. Don't hesitate to ask further questions if necessary.

Posting Permissions

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