Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Invalid Characters or Filename Too Long

    Hi all,

    Using Excel/Access 2007.

    I am trying to SELECT all records from a table into a .txt file

    The file is ~87 characters long built here:
    Code:
    'Export File
                strExportFile = strDb & "_" & Table.Name & "_Archive_" & Format(Now(), "YYYY.MM.DD_HH.MM.SS")
                If Right$(strExportFile, 4) <> ".txt" Then strExportFile = strExportFile & _
                    ".txt"
    Debug points to the SQL statement
    Code:
     conn.Execute "SELECT * INTO [text;HDR=No;Database=" & strArchivePath & _
                    ";Characterset=65001]." & strExportFile & " FROM " & Table.Name
    I've tried a few variantions on the Format(NOW()) bit, but I always get the same error.

    Any ideas?

    Thanks
    w

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what actually is the conn.execute statement trying to run
    what is the value of strExportFile?

    this may or may not be what you think it is.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Hi Healdem

    strExportFile =

    tmpDatabase.mdb_tmpTable_Archive_2012.02.10.15.43. 10.txt
    More or less.
    I replaced the db and table name the actual string is 87 characters. But this string gives a good representation of the value

    Thanks
    w

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    representation?
    either it is or it ins't the value of strExportFile.

    Im guessing the space between the 43. and 10 is an artifact of posting on this forum

    personally I don't know how many character comprises a valid filename in Access
    nor do I know if the decimal point / full stop is valid
    it it was me I'd stroe these sort of backups in a driectory called say backups/mydatabasename
    that woudl remove the need prefix the file with tmpdatabase.mdb
    defacto its an archive, so delete that
    you dont' need the decimal point separator
    so I'd have something like
    tmptable20120210154310.txt
    or
    tmptable20120210_154310.txt it you wanted to break up the date from the timestamp.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    All,

    Here is my revised code

    Code:
                strExportFile = strDBName & "_" & strTblName & "_Archive_" & Format(Now(), "YYYY.MM.DD_HH.MM.SS")
                If Right$(strExportFile, 4) <> ".csv" Then strExportFile = strExportFile & _
                    ".csv"
    
    'Execute Archival SQL
                Conn.Execute "SELECT * INTO [text;HDR=Yes;Database=" & strArchivePath & _
                ";Characterset=65001]." & strExportFile & " FROM " & strTblName
    It dumps out the specified table from the specified database and creates a csv file in this format:

    DBName_TblName_Archive_YYYY.MM.DD_HH.MM.SS.csv

    thx
    w
    Last edited by goss; 02-13-12 at 18:55. Reason: typo

  6. #6
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Hi all,

    Using Excel/Access 2007.

    What is the maximum filename length of a text file that I can SELECT INTO?
    This works with a filename length of 60 characters, but fails at 89 characters

    Code:
     strExportFile = strDb & "_" & Table.Name & "_Archive_" & Format(Now(), "YYYY.MM.DD_HH.MM.SS")
                If Right$(strExportFile, 4) <> ".csv" Then strExportFile = strExportFile & _
                    ".csv"
    
     conn.Execute "SELECT * INTO [text;HDR=Yes;Database=" & strArchivePath & _
                ";Characterset=65001]." & strExportFile & " FROM " & Table.Name
    I thought a filename could be 255 characters?
    Thanks
    w

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I thought a filename could be 255 characters?
    it probably can be under Windows, but may not be under Access VBA

    it could also be a limit triggered by the length of the file path.

    I still think you are building redundant data into the filename, but its your app.....
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2015
    Posts
    1
    Have you tried the "Long Path Tool" software? It can be of help to the issue.

    Just my 2 cents.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by goss View Post

    I thought a filename could be 255 characters
    Only if the file resides in the root directory! Under Windows that limit includes the entire path as well as the actual filename, including slashes/colons/periods. Here's a very good explanation:

    Naming Windows Files - Restrictions and Limitations

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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