Invalid Characters or Filename Too Long

goss · #1 (**permalink**) 02-10-12, 18:25

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

healdem · #2 (**permalink**) 02-10-12, 18:49

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.

goss · #3 (**permalink**) 02-10-12, 19:59

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

healdem · #4 (**permalink**) 02-10-12, 20:23

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.

goss · #5 (**permalink**) 02-13-12, 17:54

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

goss · #6 (**permalink**) 02-13-12, 18:36

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

healdem · #7 (**permalink**) 02-13-12, 18:38

Quote:

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.....

Internet Services

Web Development

Digital Marketing

Consumer Tech