Importing multiple access tables into one SQL server table

Question

Ive been recently handed a very old access database to convert over to SQL server. It was done in the 90's by others but I have the .mdb files

The database is very old but has the customers actual formulation data that id like to keep. It is structured in separate tables as follows..

Tables

Formulas

Formula_000

Formula_001

Formula_002

As you can see above there is a header formula table with details such as name, date, version etc like so..

Table [Formulas]

------------------------------------------------

| FORMULA NAME | ID | VERSION | CREATED |

| Formula_000 |  000   |     1     |  01/01/1997

| Formula_001 |  001   |     1     |  01/01/1997

------------------------------------------------

There is a separate accompanying table which holds the ingredients to go with each entry in the Formula table, so there is a table called [Formula_001] with the following columns inside..

Table [Formula_000] (x 100)

material code | % | 

material 1234 | 025 |    

material 5678 | 075 |  

------------------------------------------------

Id prefer not to have all these tables linked by a header table, but I need the data into a new structure. There are hundreds of these tables named Formula_xxx

What I want to do is loop through all the MS access tables in the above database except the header table [formulas] (i can delete this one to make it easier) , and copy them into a new master table, with an extra column with the formula number to identify which table it came from. Example below..

Table [Formula_Ingredients]

material code | % | formulaID | 

material 1234 | 025 |    000

material 5678 | 075 |    000

material 1111 | 025 |    001

material 2222 | 075 |    001

------------------------------------------------

Any ideas guys?

If you create your table as shown above, then you should be able to write some VBA code to spin thru all the 'Formula_xxx' tables and append the rows to your new table. Will that work for your needs? — Wayne G. Dunn
– Wayne G. Dunn, Commented Sep 28, 2017 at 14:56
Thats not a bad idea coming at it that way, I could loop the header table to get the formula table names too, then open them individually and copy to a new manually created table including the table name as the id. Thanks — iXDev
– iXDev, Commented Sep 28, 2017 at 15:24
You should not need to open each table if you use VBA to build SQL to 'Insert Into' (selecting records from 'Formula_xxx') and target is your new table. — Wayne G. Dunn
– Wayne G. Dunn, Commented Sep 28, 2017 at 17:38

rd_nielsen · Accepted Answer · 2017-09-28 15:01:11Z

1

This is reminiscent of a recent task we carried out to import 11,000 Access databases into Postgres. You have multiple tables in one Access file instead of an identical table name in multiple Access files, but the same approach would apply.

We used a general-purpose SQL script processor (disclaimer: that I wrote). It's a Python program; you can get it from https://pypi.org/project/execsql/ or by running pip install execsql. The technique is to put the names of all of the formula tables into a table in the target database, then iterate through that using execsql's COPY metacommand to copy data from the Access database to SQL Server. This is illustrated in Example 13 of the documentation (http://execsql.readthedocs.io/en/latest/examples.html#example-13-import-all-the-csv-files-in-a-directory), though you would want to modify this to copy instead of import.

answered Sep 28, 2017 at 15:01

rd_nielsen

2,4592 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iXDev Over a year ago

Thanks ill check it out

Collectives™ on Stack Overflow

Importing multiple access tables into one SQL server table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related