By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,009 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

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

P: 1
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
May 7 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,349
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.
May 7 '07 #2

Expert
P: 97
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. . .

Expand|Select|Wrap|Line Numbers
  1. Sub Import_data()
  2.  
  3. Dim dlgOpen As FileDialog
  4. Dim vrtFileSelected As Variant
  5. Dim db As Database
  6. Dim sql As String
  7. Dim del As String
  8.  
  9. Set db = CurrentDb
  10. Set dlgOpen = Application.FileDialog( _
  11.     DialogType:=msoFileDialogOpen)
  12.  
  13. sql = "INSERT INTO PARTS SELECT * FROM PARTS1;"
  14. del = "DROP TABLE PARTS1;"
  15.  
  16. ' Prompt user for files to import
  17. With dlgOpen
  18.     .AllowMultiSelect = True
  19.     .InitialFileName = "" 'You can put your folder location here or delete line to choose at run time
  20.     .ButtonName = "Import"
  21.     .Title = "Import Tables"
  22.     If .Show = 0 Then
  23.         MsgBox ("No files were selected. Import abandoned.")
  24.         Exit Sub
  25.     Else
  26.         For Each vrtFileSelected In .selecteditems
  27. ' The following line does the import. NOTE: This is taken from help files
  28.             DoCmd.TransferDatabase acImport, "Microsoft Access", _  vrtFileSelected, acTable, "PARTS"
  29.             db.Execute sql ' Copy Data from PARTS1 table
  30.             db.Execute del ' Delete PARTS1 table
  31.         Next
  32.     End If
  33. End With
  34.  
  35. ' Tidy up
  36. Set dlgOpen = Nothing
  37. Set db=Nothing
  38. End Sub
  39.  
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.
May 7 '07 #3

JConsulting
Expert 100+
P: 603
a small loop....using a query to move records from the "PARTS" table when its linked...into your "Final" table will work like so
Expand|Select|Wrap|Line Numbers
  1. Function ImportDB()
  2. Dim FSO As Object
  3.     Set FSO = CreateObject("Scripting.FileSystemObject")
  4.     Dim Fl As Object
  5.     strfolder = "C:\"
  6.  
  7.     strFile = Dir(strfolder & "\fs*.mdb", vbNormal)
  8.     Do While strFile <> ""
  9.         If strFile <> "." And strFile <> ".." Then
  10.             ' Link the Parts Table
  11.             DoCmd.transferdatabase acLink, "Microsoft Access", strFile, acTable, "Parts", "Parts", False
  12.             ' Run your append query
  13.             CurrentDb.Execute "YourQueryName"  '<--Your Append Query
  14.             ' Remove the linked table so you can do it again
  15.             DoCmd.DeleteObject , acTable, "Parts"
  16.         End If
  17.         strFile = Dir()
  18.     Loop
  19.     Set FSO = Nothing
  20. End Function
May 7 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.