Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    222
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by ChrisTheobald View Post
    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?

  3. #3
    New Lounger
    Join Date
    Sep 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

Posting Permissions

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