Results 1 to 4 of 4
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Import multiple files (2002)

    I have 75 DBF files that I'd like to import into a new database, all into one table. Is there a way to do that all at once? Each DBF file has the same field names and data types. I know that I couild import each DBF, and then do an Append Query of Table 2 through 75 into Table 1, but that's quite time comsuming. I don't know very much about macros or VBA. Thanks!
    JimmyW
    Helena, MT

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import multiple files (2002)

    Place all 75 DBase files in the same folder; this folder should not contain other DBase files.
    Open your Access database.
    Import one DBF file manually, then delete all records from the imported table. Alternatively, you can create an empty table with the same field names and data types as the DBase files.
    Activate the Modules tab of the database window.
    Click New.
    Copy the following code into the module window:
    <code>
    Sub ImportDBF()
    Const strPath = "C:TestDBase"
    Const strTable = "tblImport"
    Dim strFile As String
    Dim strSQL As String
    strFile = Dir(strPath & "*.dbf")
    Do While Not strFile = ""
    strSQL = "INSERT INTO [" & strTable & "] SELECT * FROM " & _
    Left(strFile, Len(strFile) - 4) & " IN " & Chr(34) & strPath & Chr(34) & _
    " " & Chr(34) & "dBASE IV;" & Chr(34)
    CurrentDb.Execute strSQL
    strFile = Dir
    Loop
    End Sub
    </code>
    Replace C:TestDBase with the complete path of the folder; make sure to include a trailing backslash.
    Replace tblImport with the name of the empty table mentioned above.
    Make sure that the insertion point is in this procedure.
    Press F5 to run the code.
    If you don't get an error message, switch back to Access to view the table.

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Import multiple files (2002)

    Thanks very much, Hans! It worked perfectly. I'm not sure what you meant by "Make sure that the insertion point is in this procedure," but I F5'ed after editing the path and table name, and found my table with 500K records. You saved me an awful lot of time!
    JimmyW
    Helena, MT

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import multiple files (2002)

    Glad it worked. What I meant was that the blinking vertical bar that indicates where you are typing should be somewhere between Sub and End Sub. If it is, the procedure will run immediately, otherwise F5 will ask which code you want to execute.

Posting Permissions

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