BleepingComputer.com: How to import ".mdb" files into SQL Server 2008?

Assuming it can be done (as I've been told):

How do I import ".mdb" (MS Access / OleDb) files (specifically, Northwind sample database files) into SQL Server 2008 (Express with Advanced Features)?

BobLewiston, on Mar 13 2009, 04:34 AM, said:

Have a read here Bob.

Keithuk:

You have referred me to "The connection string reference" @ "http://www.connectionstrings.com". I'm a newbie, so please bear with me: I don't see the relevance of the webpage you have referred me to.

Well if you've never used databases then it starts you off with the connection strings.

Of course you use connection strings to access databases. But what is on the webpage that tells you how to construct connection strings that you referred me to ("http://www.connectionstrings.com") that answers my question "How do I import ".mdb" files into SQL Server?"

BobLewiston, on Mar 15 2009, 12:14 AM, said:

Not always it depends how you are accessing the database. In VB you can use ADO, Adodc, OLE DB, ODBC, RDS, DAO, Jet, JRO, and the old Data control. ADO, DAO, Adodc and the Data control are the common ones used. You only need connection strings with ADO.

BobLewiston, on Mar 15 2009, 12:14 AM, said:

I've never used SQL Server so I can't help you with that one Bob.

Hi Bob,

This should be pretty easy... Depending upon which version of MS Access you are using. Access has an "Upsize Wizard" that will help you move the tables from MS Access to SQL Server. The actual menu commands will vary between versions, i.e. 2007 will be different from 2003, but it's fairly straightforward once you find out where it's located in your version.

In 2003, go to the Tools menu, Select Database Utilities then select Upsizing Wizard. You may have to expand the menu if you have personalized menus turned on. I don't have 2007 installed here to let you know what the menu commands are, but you should be able to find it if you poke around a little bit.

One thing you will definitely want to do is to create an ODBC connection using the ODBC Administrator applet and make sure you create a SYSTEM or MACHINE DSN as opposed to a User DSN.

The other way you can migrate the data would be to export the tables to ASCII format and pull them into SQL Server using the Management Studio Import Database task.

Hope this helps you get started.

Ben E. Brady