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
3  2595  Rabbit 12,516
Recognized Expert Moderator MVP
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 97
Recognized Expert New Member
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: heromull |
last post by:
We have an asp.net app with about 200 data entry forms. Customers may
enter data into any number of forms. Each form's data is persisted in
a corresponding sql table. When data entry is...
|
by: James Foreman |
last post by:
I've got lots of different tables, each pertaining to a different
marketing campaign.
I have another table that stores the name & schema of each of these
tables, together with other information...
|
by: Tlm |
last post by:
Hello All,
I have a form (FrmA) with a subform (SubFrmB) embedded in it. SubFrmB also
has a subform embedded in it (SubFrmC) The form's recordsource is based on a
table (TblA). SubFrmB's...
|
by: lgbjr |
last post by:
Hello All,
I¡¯m using a context menu associated with some pictureboxes to provide
copy/paste functionality. Copying the image to the clipboard was easy. But
pasting an image from the clipboard...
|
by: Neo Geshel |
last post by:
I am seeking to hand-roll my own blog in ASP.NET 2.0 and SQLExpress
2005. Why? Because I can. Because I will gain experience.
The one thing that has me stumped at square one is inline images....
| | |
by: Steve |
last post by:
I'm trying to code cut, copy, and paste in vb 2005 so that when the user
clicks on a toolbar button, the cut/copy/paste will work with whatever
textbox the cursor is current located in (I have...
|
by: Emin |
last post by:
Dear experts,
If I have a number of related groups of data (e.g., stock prices for
different stocks) is it better to put them into many tables (e.g., one
table per stock) or into one big table...
|
by: Andy_Khosravi |
last post by:
I just recently changed my database that I'm running from a monolithic
DB to a split FE/BE. The front end resides on the client machine and
the BE resides on a network drive. I'm experimenting with...
|
by: TheServant |
last post by:
Hi everybody,
I posted a similar question about 6~12 months ago with no definate answer, so I will give it another go, as the choice has popped up again in my programming life:
If the number of...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |
