How to copy contents of many tables to a single table without opening them

[piyabasanti]

Hi, I have about 450 access files ( as001, as002,............, as450) in a folder. Each of the file contains a table named PARTS ( which contains 4 fields). Now I want to copy the contents of these tables from each of this access file into a single Table. can something be done with Macro.? Please help me to write the macro. I can copy paste manullay but it will take a long time. My knowledge level in ACCESS / VB coding is that of Beginers.
Plz help me

[Rabbit]

Hi, I have about 450 access files ( as001, as002,............, as450) in a folder. Each of the file contains a table named PARTS ( which contains 4 fields). Now I want to copy the contents of these tables from each of this access file into a single Table. can something be done with Macro.? Please help me to write the macro. I can copy paste manullay but it will take a long time. My knowledge level in ACCESS / VB coding is that of Beginers.
Plz help me

You'll probably want to start by looking at this tutorial for beginners then.

What you'll want to do is create a Loop that will import the table, append it to a main table, and delete the table.

[kepston]

You can import the tables using VBA modules.
I do something similar with text files on a regular basis.
You can combine the tables in one hit, with a little extra code. . .

Sub Import_data()
 
Dim dlgOpen As FileDialog
Dim vrtFileSelected As Variant
Dim db As Database
Dim sql As String
Dim del As String
 
Set db = CurrentDb
Set dlgOpen = Application.FileDialog( _
    DialogType:=msoFileDialogOpen)
 
sql = "INSERT INTO PARTS SELECT * FROM PARTS1;"
del = "DROP TABLE PARTS1;"
 
' Prompt user for files to import
With dlgOpen
    .AllowMultiSelect = True
    .InitialFileName = "" 'You can put your folder location here or delete line to choose at run time
    .ButtonName = "Import"
    .Title = "Import Tables"
    If .Show = 0 Then
        MsgBox ("No files were selected. Import abandoned.")
        Exit Sub
    Else
        For Each vrtFileSelected In .selecteditems
' The following line does the import. NOTE: This is taken from help files
            DoCmd.TransferDatabase acImport, "Microsoft Access", _  vrtFileSelected, acTable, "PARTS"
            db.Execute sql ' Copy Data from PARTS1 table
            db.Execute del ' Delete PARTS1 table
        Next
    End If
End With
 
' Tidy up
Set dlgOpen = Nothing
Set db=Nothing
End Sub
 

This code assumes you already have the table PARTS in your main database and that it has the same four fields as your 450 others.
I suggest that you prove this with a small selection of files to start with, that's the beauty of the Dialog box, it allows you to choose which files you want.
Please backup first.

I hope that helps. Let me know.

[JConsulting]

a small loop....using a query to move records from the "PARTS" table when its linked...into your "Final" table will work like so

Function ImportDB()
Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Dim Fl As Object
    strfolder = "C:\"
 
    strFile = Dir(strfolder & "\fs*.mdb", vbNormal)
    Do While strFile <> ""
        If strFile <> "." And strFile <> ".." Then
            ' Link the Parts Table
            DoCmd.transferdatabase acLink, "Microsoft Access", strFile, acTable, "Parts", "Parts", False
            ' Run your append query
            CurrentDb.Execute "YourQueryName"  '<--Your Append Query
            ' Remove the linked table so you can do it again
            DoCmd.DeleteObject , acTable, "Parts"
        End If
        strFile = Dir()
    Loop
    Set FSO = Nothing
End Function