Error 3011, TransferText acExportFixed and table specification

[MaxGeek]

I'm modifying a existing Access App someone else made to add additional fields to the application.

The application imports a spreadsheet and formats the fields into an text export file.

One line of code is giving me problems:
DoCmd.TransferText acExportFixed, "T_INFO_Text Export Specification", TableName, "C:\Temp\" & TableName & ".txt"

I get error 3011 on this line and it says Microsoft Office Access database engine could not find object 'myfile#txt' (TableName = myfile)

I found this article on the subject which has helped with another error I was getting before, but I'm still stuck on this error.

http://www.blueclaw-db.com/export-specifications.htm

 

[Remou]

What is the value of tablename? Please post a little more code.

 

[MaxGeek]

Here is code snippet
*REQUESTNO is passed in as a 9 digit character

Dim TableName As String
Dim strRequestno As String

TableName = "C" & REQUESTNO

RetValue = SysCmd(acSysCmdSetStatus, "Drop existing export table...")
DoCmd.DeleteObject acTable, TableName

RetValue = SysCmd(acSysCmdSetStatus, "Create temporary export table...")

sql = "SELECT ID, DESC, CUST INTO "" & TableName & " FROM T_INFO_Text WHERE REQUESTNO = '" & REQUESTNO & "'"

dbs.Execute (sql)

DoCmd.TransferText acExportFixed, "T_INFO_Text Export Specification", TableName, "C:\Temp\" & TableName & ".txt"

Copy_File (TableName)

 

[PHV]

Replace this:
sql = "SELECT ID, DESC, CUST INTO "" & TableName & " FROM T_INFO_Text WHERE REQUESTNO = '" & REQUESTNO & "'"
with this:
sql = "SELECT ID, DESC, CUST INTO " & TableName & " FROM T_INFO_Text WHERE REQUESTNO = '" & REQUESTNO & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MaxGeek]

Thanks, but thats actually a editing error on my part. I simplified my code to reduce a bunch of fields for posting and accidently left an extra double quote.

The application is erroring on
DoCmd.TransferText acExportFixed, "T_INFO_Text Export Specification", TableName, "C:\Temp\" & TableName & ".txt"

If I comment out this line, I don't get an error.

 

[MaxGeek]

Also the SQL dbs.Execute(sql) seems to correctly create and populate the specified table.

 

[Remou]

Try this:

dbs.Execute (sql), dbFailOnError

 

[MaxGeek]

I get the same error. It says:

Error Number: 3011 Message: The Microsoft Office Access database engine could not find the object 'C041581438#txt'. Make sure the object exists and that you spell its name and the path name correctly.

 

[Remou]

This is your problem: 'C041581438#txt'

Access is fussy about extensions, you must use an acceptable extension or tamper with the registry, so:

C041581438.txt

 

[MaxGeek]

I am using .txt, atleast I'm trying to.

DoCmd.TransferText acExportFixed, "T_INFO_Text Export Specification", TableName, "C:\Temp\" & TableName & ".txt"

I've also tried
DoCmd.TransferText acExportFixed, "T_INFO_Text Export Specification", TableName, "C:\Temp\filename.txt"

and for whatever reason excel reports the error as the filename#txt versions .txt.

 

[MaxGeek]

I mean versus not version in my last post.

 

[Remou]

You say 'excel reports the error as ..' is this a misprint? Have you tried substituting the name of an actual table for TableName? Are you sure the table has been created?

As an aside, why are you creating a table, rather than modifying a query (creating numerous table will cause Access to grow unacceptably)?

 

[MaxGeek]

Yeah I meant access. Yes I have tried substituting the name of the actual table. It still gives the same error. I can confirm the table was created because it shows up in my table list and when I look at it, it has the correct data in it.

Some else had a similar problem, but I couldn't find a resolution

http://www.eggheadcafe.com/software/aspnet/30536015/transfer-text-error-3011.aspx

I'm not sure why the application is built this way, but I am merely trying to add to it. Size isn't an issue as its used by individuals locally.

 

[Remou]

Try running DoCmd.TransferText from the immediate window or a scratch Sub. Just a straight transfer, with no specification, the name of an existing table and a full path to a text file in a standard directory.