Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2009
    Posts
    47

    Unanswered: Importing Text files into Access

    I have imported some Text files into Access wanted to know if there is a way to make the process automated. Like in Excel you can have a macro do it. How can I perform this in Access

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Check out TransferText in VBA help (also available in macros).
    Paul

  3. #3
    Join Date
    Feb 2009
    Posts
    47
    is there a mximum of queries that you can have a macro run I got a system resource error for mine. I have a total of 19 queries to run some are table queries while others are delete queries.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There's a neat import example in the code bank which has the import process automated (and uses the browse button for you to select which file to import.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Almost every time I have done an automated text import, it has been with the DoCmd.TransferText action (followed by recordset processing) in VBA.

    There is no limit I am aware of regarding the number of queries a macro or VBA can run.

    Try your hand at VBA coding. Once you get DoCmd. under control you'll never look back to rotten macros.


    [rant]I got another database job yesterday -- a takeover -- the developer left and I got called as the replacement. Guess what it's riddled with? Macros. Dirty rotten stinking macros. I have to click every object in every form and every report to find out where the 39479283 macros are being called from. Ridiculous. Microsoft should never have made them; or at least there should be a way to find out all the places a where a macro is being called from.

    I am going to start an I hate Access Macros religion.

    Of course, Word and Excel are different -- their macros are VBA -- just exactly what should have happened with Access [/rant].
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Hi StarTrekker!

    I 1000% agree with you and I'll be one of the first living apostle of the new faith

    Have a nice day!

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by StarTrekker
    [rant]I got another database job yesterday -- a takeover -- the developer left and I got called as the replacement. Guess what it's riddled with? Macros. Dirty rotten stinking macros. I have to click every object in every form and every report to find out where the 39479283 macros are being called from. Ridiculous. Microsoft should never have made them; or at least there should be a way to find out all the places a where a macro is being called from.
    In total agreement!
    Me.Geek = True

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Won't a tool like SpeedFerret or the free V-Tools find everyplace a macro is called from?
    Paul

  9. #9
    Join Date
    Feb 2009
    Posts
    47
    Can any of u guys look at my Column Shift post and see if you cna point me in the right direction.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by pbaldy
    Won't a tool like SpeedFerret or the free V-Tools find everyplace a macro is called from?
    Hmm... unaware of these, I will check them both out, it might save me some horrible hours. Thanks I still hate macros though

    Quote Originally Posted by UNCC-EE
    Can any of u guys look at my Column Shift post and see if you cna point me in the right direction.
    Ummm.... are you referring to another topic?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Feb 2009
    Posts
    47
    StarTrekker the DoCmd is it used in the SQL statement or is this in VB. I was trying to use it but like u said it is VB so where do i got for that is it new Query or New macro...
    pkstormy said something about an example in "code bank" where is that located.

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Feb 2009
    Posts
    47
    Dude that code scared the s hit out of me it is huge. So which module should I be looking at to replicate somthing like this and when I tried to use the Sample to import I gave me 4 msg boxes asking
    1. Your routine here...
    2. Importing form C:\........
    3. Importing to Table "File name"
    4. data imported..
    While the Status bar still showed "Stand by, Importing data"
    is there any way to omit these Msg Boxes.

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I think what's scaring the "s" out of you is all the code to do the "browse" button function. Again - use the SampleImportingRoutineNEW example. Don't let all the "browse" coding/functions throw you off. Basically, if you ignore all the browse, browseshell, etc.. coding, the command to actually import the file is a simple: Docmd.transfer... command (see the code behind the cmdImport_Click event.)

    This is a "quick" example to show you how to do it. You'll want to modify it to fit your needs as this is NOT designed around your specific requirements (again, it's only to show you how to easily "BROWSE" to a file and then import that file into MSAccess). You can ignore the popup messages by adding a comment (ie. ') to the beginning of that line of code.

    If you want to automate an import process (around SPECIFIC excel/text filenames in a specific location), you can again, ignore the browse coding and just use the docmd.transfer.... to transfer your text files into MSAccess. You will need to expand upon the code to do that. For example, you may have 10 docmd.transfer.... code lines each line with a parameter pointing to the actual text file name to import (ie. TextFile1.txt, TextFile2.txt, etc..). You'd then automate the process with a button on the form.

    So you may have lines of code like this when the user clicks whatever button on the form...

    DoCmd.TransferText acImportDelim, , "MyTableName1", "c:\FolderX\TextFile1.txt", True
    DoCmd.TransferText acImportDelim, , "MyTableName2", "c:\FolderX\TextFile2.txt", True
    DoCmd.TransferText acImportDelim, , "MyTableName3", "c:\FolderX\TextFile3.txt", True

    again though - see the help on the Transfertext command and what parameters to use in the command as it depends on how your text files are configured (ie. with a header row or without a header row.)

    I personally don't like to "hard-code" in the location of where the text files are located (and hence the browse button coding.) It sounds like you want to import X number of text files automatically from a location which doesn't change (and the filename is always the same). In that case, it's a simple matter of creating a bunch of docmd.transfer... commands as above with the parameter in this command of pointing to each file name to import (and then adding a button on the form to automatically execute all the docmd.transfers.)

    Use the mdb as an example to learn from, tweak and utilize versus trying to impliment it (as it is) into a live production. If you don't want (or need) to browse to the location of where your text file is, you can ignore this example completely. Again, docmd.transfertext is the command you're focusing on. This example assumes that you want/need to "browse" to locate the files to import. (note also that there are other different "browse" type coding. This is one method. Some of the other browse methods for a file coding are more complex. There are other examples in the code bank to do a "browse."

    Basically though, if you don't need to browse for the filename, you don't need all the coding in the example - sorry if it's overkill for what you need. Otherwise, if you do need to browse for the filename, you can do it by manually importing the file or using code such as in the example.
    Last edited by pkstormy; 03-30-09 at 14:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by UNCC-EE
    StarTrekker the DoCmd is it used in the SQL statement or is this in VB. I was trying to use it but like u said it is VB so where do i got for that is it new Query or New macro...
    pkstormy said something about an example in "code bank" where is that located.
    DoCmd is definitely VB. In any VBA module, you can type DoCmd. (including the dot) and what shows up is all the actions that you can select with macros... and more. What I was saying was that once you realise how to write your own code like this, you will not go back to macros again!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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