Hi ,

I am trying to import n number of access database.

A1.mdb
A2.mdb
A3.mdb
.
.
.
An.mdb

Each of the above .MDB file has a table name called tblTEST
The table name same across all the mdb files.

But the COLUMNS ARE NOT THE SAME IN EACH TABLE tblTEST (but different .mdb file).


What i want is below,

1. Put all the access file in a folder.

2. copy one mdb file :- its table tblTEST,the STRUCTURE and DATA into sql server at a time.

3. Loop thro all the mdb files in the folder.

I am not sure how to automate this task. To copy the whole structure and data from access to Sql server.

That means i need to create a table with the exact structure as in access in SQL SERVER and then import the data.

Any help is highly appreciated.

Thanks Heaps. This is really important and urgent.

try macro, there are many options in macro such as open database, run query etc. you can also edit it like vb code.

urt

But i need to write a DTS or a Stored procedure to import the data from the MDB files into SQL SERVER.

First you take every table from various mdb files in one mdb file, and then from sql server import all tables from one access file

urt

Hi urt,

Thanks for the suggestion, but...

The Table names are the same in all the mdb files. And more over how do i programatically copy all the tables into sql server.

As the table names are same across all the mdb files but the columns will vary in each of the mdb file.

So copying the tables into the same mdb file will be needed to be done programatically and thats a waste of time.

How many mdb files are there and how many tables to import are there 1 mdb file

urt

There are N mdb files, but the same table name in all the mdb files. And i want to import this particulat table in all the mdb files.

Your create one access file in the same folder where all a1,a2.... files are placed, say final.mdb
create new module and paste following code

Sub ImportTables()
    For i = 1 To 10
        DoCmd.RunSQL ("SELECT * into tblTEST" & Trim(Str(i)) & " from A" & Trim(Str(i)) & ".mdb.area")
    Next
End Sub

replace 10 with no of mdb files in your folder
run this module all tables will be imported directly to your one final database, now you can import this one file in sql server.

urt

hmmm......... yeah now i get what u mean. Except that, as i said this is a matter of time as well, as the data in each table is a few hundred thousand.

But will try this solution. Thanks for the suggestion.

If anything better please let me know. As i will have to look into the time as well.

Sub ImportTables()
    For i = 1 To N
        DoCmd.RunSQL ("SELECT * into tblTEST" & Trim(Str(i)) & " from A" & Trim(Str(i)) & ".mdb.tblTEST")
    Next
End Sub


urt