MS ACCESS 2010 VBA – transfer text Run-Time error 3011

Topic

New Reply

good morning loungers,

I hope everyone had a good new year and all.

I have a bit of a problem when using the transfer text commant and my own specification.

I need to export a file from an access database to a pipe seperated text file. I created a specification, and saved it off as “ExportPipe”. As there is more than one option to exprt, i created a module and passed int he variables. so the string looked like this:

Code:

‘This is the line that creates “Location” 
Me.txtlocation & “” & Right(Me.txtTable, Len(Me.txtTable) – 10) & Month(Me.txtStartDate) & “-” & Year(Me.txtStartDate) & “-” & Replace(Now(), “/”, “-“) & “.txt”

‘TableName is passed into the module


DoCmd.TransferText acExportDelim, ExportPipe, TableName, Location, False

This exports the file to the location i require perfectly… with one exception. It is not a pipe seperated file. Upon looking through a number of guides and forum posts on the internet i realised my mistake, that i hadn’t put ” around the specification name. So i did this:

Code:

DoCmd.TransferText acExportDelim, “ExportPipe”, TableName, Location, False

Whenever i try to run this line now i get the following Error message:

Run-Time error ‘1011’:
The microsoft Access database engine could not find the object ‘AE_v629-2013-08-01-2014 09:29:39#txt’. Make suthe object exists and that you spell the path name correctly. If ‘AE_v629-2013-08-01-2014 09:29:39#txt’ is not a local object, check your network connection or contact the server administrator

While this does write to a networked server, it seems strange that i would consistently have a server blip everytime i clicked the button, so i am not quite sure if i believe the last line of the message, also i have written files in this manor to other folders on this server with no issue (in fact his same database does!).

I have no idea why in the error message the file extension has become #txt when in the code used to create it and the immediate window when running the code it clearly .txt. Is this part of the problem?

I suspect i have been incredibly stupid and the answer is staring me right in the face, if anyone knows what could cause this error please let me know.

Thank you all,
Chris

Reply | Quote

Replies

good morning loungers,

I hope everyone had a good new year and all.

I have a bit of a problem when using the transfer text commant and my own specification.

I need to export a file from an access database to a pipe seperated text file. I created a specification, and saved it off as “ExportPipe”. As there is more than one option to exprt, i created a module and passed int he variables. so the string looked like this:

Code:

‘This is the line that creates “Location” 
Me.txtlocation & “” & Right(Me.txtTable, Len(Me.txtTable) – 10) & Month(Me.txtStartDate) & “-” & Year(Me.txtStartDate) & “-” & Replace(Now(), “/”, “-“) & “.txt”

‘TableName is passed into the module


DoCmd.TransferText acExportDelim, ExportPipe, TableName, Location, False

This exports the file to the location i require perfectly… with one exception. It is not a pipe seperated file. Upon looking through a number of guides and forum posts on the internet i realised my mistake, that i hadn’t put ” around the specification name. So i did this:

Code:

DoCmd.TransferText acExportDelim, “ExportPipe”, TableName, Location, False

Whenever i try to run this line now i get the following Error message:

Run-Time error ‘1011’:
The microsoft Access database engine could not find the object ‘AE_v629-2013-08-01-2014 09:29:39#txt’. Make suthe object exists and that you spell the path name correctly. If ‘AE_v629-2013-08-01-2014 09:29:39#txt’ is not a local object, check your network connection or contact the server administrator

While this does write to a networked server, it seems strange that i would consistently have a server blip everytime i clicked the button, so i am not quite sure if i believe the last line of the message, also i have written files in this manor to other folders on this server with no issue (in fact his same database does!).

I have no idea why in the error message the file extension has become #txt when in the code used to create it and the immediate window when running the code it clearly .txt. Is this part of the problem?

I suspect i have been incredibly stupid and the answer is staring me right in the face, if anyone knows what could cause this error please let me know.

Thank you all,
Chris

I’ve just tested a pipe-delimited spec in code (albeit in Access 2007) and it worked fine to my c: drive so I’m not sure why you get an error.

Just one question: does your code set Location = me.txtLocation somewhere or have you just omitted that for brevity from your code extract above?

Reply | Quote

I did omit that deliberatley, for brevity (great word!)

Like i said, if i dont have the “” around the specification name it will give me a file, just not pipe delimited. Why the “” then causes other errors (or highlights some?) is beyond me!

Reply | Quote