Import Multiple access mdb's

moishy

Registered User.
Local time
Today, 23:13
Joined
Dec 14, 2009
Messages
264
Hello all,

I have a need to import the tables from some 30 or so mdb's to my mdb app.

I have no idea how to do it. The way I would like it work would be:

1. browse for access files (enable multiple selection)
2. show the results in a list box
3. have an option to remove a file from the list
4. import table (one table per mdb) from all files remaining in list box
5. add a field with name of file of origin to each record

I hope this will not cause too much trouble.

Thanks in advance.
 
Couple of questions about structure before we start on the importing business - how come there are so many databases? Does the data in them change regularly?

The reason I ask is that I immediately thought of having the tables themselves stored centrally and having links to the tables in your other DBs...
 
The files contain a list of the organizations contacts, the secretary had created a separate file for each city, the data will not change, all we have to do is a onetime import to import the contacts to the mdb that we're designing.[FONT=&quot][/FONT]
 
OK then - probably the best thing to do is read up on the Dir command in VBA, have the user enter the path of the DB and loop through the contents of the folder, adding MDB files to an array, which you can then use as the rowsource of the listbox:
Code:
Public Sub ListBox_Files(Userpath As String, ctlListBox As ListBox)

Dim File_List, File_Rowsource As String

'Strip trailing \ from Userpath

If Right(Userpath, 1) = "\" Then
Userpath = Left(Userpath, Len(Userpath) - 1)
End If

File_List = Dir(Userpath & "\*.mdb")
File_Rowsource = "'" & File_List & "';'"

Do While Len(File_List) > 0
File_List = Dir()
File_Rowsource = File_Rowsource & File_List & "';'"
Loop

ctlListBox.RowSource = File_Rowsource

End Sub
That's the code to populate a listbox based upon a path entered in a text box on a form. The listbox's row source type has to be value list, otherwise it won't work... Call it in VBA like this:
Code:
call listbox_files([textboxname].value,listboxname)
I'll have to come back to this, busy morning....
 
If this is a one-off, why not simply use the import wizard? You'll likely spend more time on coding/debugging throw-away code than the actual job would take.
 
I don't want to use the import wizard because:
a. I can only import one file at a time and I have over 30 files to import.
b. The user who will actually be doing it isn't computer savvy.
 
JamesMcS:

If I use your method of populating the listbox, how can I loop through the rows and import the tables.​
 

Users who are viewing this thread

Back
Top Bottom