+ Reply to Thread
Results 1 to 3 of 3

Weird behavior on Transfertext with VBA

  1. #1
    Registered User
    Join Date
    04-11-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2007
    Posts
    4

    Weird behavior on Transfertext with VBA

    I use docmd.Transfertext to import a CSV with a specification name to a table in ACCESS. I use DAO setting. There are times when I get an error message saying that the "command or action Transfertext isn't available now". Sometimes it works correctly and other times, it doesn't work.
    This is the syntax I use -> DoCmd.TransferText acImportDelim, specs, tablename, sourcefile, True

    I don't understand why it's giving me this error. I google but couldn't find any reference to my problem.

    I am so frustrated. Any help would be greatly appreciated.

  2. #2
    Forum Contributor
    Join Date
    04-23-2009
    Location
    IOWA
    MS-Off Ver
    2010 Professional
    Posts
    270

    Re: Weird behavior on Transfertext with VBA

    Hi,
    Shot in the dark here. Do you have the file open sometimes or another program altering it?

    Other then that, some more questions I will ask.
    Always to the same table or different tables? (Are fields the same, example: text, long, memo etc)
    Same or different "specificationname" used each time?
    Same or different sourcefile used each time? (Are they the same exact format? and info in same order?)
    Are you using any global variables in any of the procedure to define any of the required info? (Errors can wipe out stored variables)

    I only answered this because I hate the situation you are in when it happens to me. It is a very difficult problem to track down without an example database and file.

    Hope this helps,

    Dan

    P.S. I think the error - "The command or action ' ' isn't available now." is commonly thrown when a record is NOT updatable or Locked.
    Last edited by split_atom18; 04-12-2012 at 05:19 PM. Reason: added P.S.
    "I am not a rocket scientist, I am a nuclear engineer." - Split_atom18
    If my advice has been helpful to you, then please help me by clicking on the "Star" and adding to my reputation, Thanks!

  3. #3
    Registered User
    Join Date
    04-11-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Weird behavior on Transfertext with VBA

    Thanks for the response. I ended up taking 2 approaches by writing my own load routine using the SQL insert into but it's taking almost 1 hour to load my files. I optimized by reading the files into memory first but no luck.
    I scratched that and use the transfertxt command by sending it to an Access object. That was the fastest and most efficient way to load my 300K records down to just 3 mins.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1