Results 1 to 4 of 4
  1. #1
    webisti is offline The Wisher
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    115

    Import multiple txt tables

    Hi I am trying to load a vba code in the backbround of a form comand:
    where I want to download many txt files into tables.
    Seems I can do only one at a time.
    for sure my code is missing smth, can smb have a look and tell me what?



    thanks

    Private Sub Command1_Click()
    Dim strPath As String
    Dim strFile As String
    Dim strTable As String
    Dim strSpecification As String
    Dim intImportType As AcTextTransferType
    Dim blnHasFieldNames As Boolean
    strTable = "orders"
    strSpecification = "orders"
    blnHasFieldNames = False
    intImportType = acImportDelim

    strTable = "cust"
    strSpecification = "cust"
    blnHasFieldNames = False
    intImportType = acImportDelim

    strTable = "shops"
    strSpecification = "shops"
    blnHasFieldNames = False
    intImportType = acImportDelim

    ' Let user select a folder
    With Application.FileDialog(4)
    If .Show Then
    strPath = .SelectedItems(1)
    Else
    MsgBox "You didn't select a folder", vbExclamation
    Exit Sub
    End If
    End With
    If Right(strPath, 1) <> "\" Then
    strPath = strPath & "\"
    End If
    DoCmd.OpenForm "frmMessage"
    Forms!frmMessage.Repaint
    ' Loop through the text files
    strFile = Dir(strPath & "*.txt")
    Do While strFile <> ""
    ' Import text file
    DoCmd.TransferText _
    TransferType:=intImportType, _
    SpecificationName:=strSpecification, _
    TableName:=strTable, _
    FileName:=strPath & strFile, _
    HasFieldNames:=blnHasFieldNames
    strFile = Dir
    Loop
    DoCmd.Close acForm, "frmMessage"
    End Sub

    End Sub

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    1. Is it possible that the data is being over-written on each import?
    2. You could try setting up two or three really small text files to import and then put a break point in your loop and see where the code is failing.
    ?
    Let us know if you still have questions.

  3. #3
    webisti is offline The Wisher
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    115
    1. yes the data will be overweitten on each import.
    It does not really matters if they get overwritten,
    2. Can it be because of asking to import each file in a different table and each of them has a different specification type?
    Basically the point is that I am able to import just one table...usually the last one in the code.
    I want to have all of them as stated in the code.
    How can that be done.
    ex. imagine that this code needs to be there 6 times with 6 different table names and six different specifications:
    strTable = "cust"
    strSpecification = "cust"
    blnHasFieldNames = False
    intImportType = acImportDelim

    any thoughts? or samples.

  4. #4
    webisti is offline The Wisher
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    115
    With the help of my friend I could manage it

    I was missing the specification off the file to import
    ' Loop through the text files
    strFile = Dir(strPath & "*.txt")
    Do While strFile <> ""
    If strFile = "cust.txt" Then
    strTable = "cust"
    strSpecification = "cust"
    blnHasFieldNames = False
    intImportType = acImportDelim

    Issue solved.
    works perectly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Automate import into multiple Access tables
    By mcchung52 in forum Import/Export Data
    Replies: 5
    Last Post: 01-26-2012, 05:33 PM
  2. Import multiple files from one location to new tables
    By shmalex007 in forum Import/Export Data
    Replies: 1
    Last Post: 01-05-2012, 03:49 AM
  3. Import Excel Data to Multiple Access Tables
    By colby in forum Import/Export Data
    Replies: 3
    Last Post: 11-04-2011, 12:17 PM
  4. Replies: 3
    Last Post: 08-29-2011, 03:11 PM
  5. Splitting an Excel File into Multiple Access Tables on Import
    By TheWolfster in forum Import/Export Data
    Replies: 4
    Last Post: 04-29-2010, 04:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums