Import 800 access .mdb files to SQLServer

[bmacbmac]

Hi!

I have 800 little MDB files that I need to import into SQLServer 2008 R2. I'm really hoping I don't have to do the data import wizard 800 times, but I can't find much of a T-SQL solution.

I'm hoping there is some sort of insert script I can run for each MDB file. Any ideas?

Thanks!

 

[bborissov]

Why not write some Integration services code?

Borislav Borissov
VFP9 SP2, SQL Server

 

[bmacbmac]

Man I have no idea how to do this. I think I may need to hire this out!

 

[dhookom]

I doubt this is a simple process that can be fully automated. I have seen way too many Access database that were horribly structured with no conventions, primary keys, relationships, etc. I would create an inventory of MDB files and knock them off one by one. Have a good set of standards and conventions.

I also wouldn't move them to SQL Server 2008 since it's end of support.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Unless those 800 MDB files are just one table each, same structure, and you wan to dump all of that into one place. That would be easy.

Some people call it a data base, even one table MDB file, you know...


---- Andy

There is a great need for a sarcasm font.

 

[bmacbmac]

Andrzejek - Yes each mdb file has just one table, 5 columns. All have the same structure.

 

[bborissov]

Then check this:

https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver15

Borislav Borissov
VFP9 SP2, SQL Server

 

[Andrzejek]

I am sure there is some fancy (or not) way to do it in SQL Server, but if that would be me, and those Access files are not password protected and reside in the same folder, I would just use a simple VBA (in Excel maybe) to loop thru those MDB files, open first one of them, get all the data from this one table, insert it into a table in SQL Server, close first MDB file, open second one, do the same, and go all the way to 800th file and be done with it. At the end, you have all data in (one table?) SQL Server.


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

bmacbmac,
It seems this "each mdb file has just one table, 5 columns. All have the same structure" would have been important to mention in your first post. I agree somewhat with Andy but would use VBA code in Access. I expect the SQL gurus here would use something like Borislav has suggested.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Olaf Doschke]

It is indeed more important to know the structure/quality of a problem than the quantity of files. You'll not program per record, but per table structure. Number of files also isn't even telling soemthing about the size of them, so it's even not a telling figure about the quantity of data.

OK, I also read each MDB is just 1 table with 5 columns. I assume really all the same. Then all you need is a DIR listing of the 800 files as one input and iterating over them read out the data and bulk load it into SQL Server. The problems will likely be in the details.

Get more concrete, so what is that table structure, any special fields? OLE Objects fields? Blobb= Or just something simple say attendence data? 800 mdb files isn't the main information.

Bye, Olaf.





Olaf Doschke Software Engineering

https://www.doschke.name