How to import mdb to sql server

Question

I have a vb application. Now I have developed that same vb application in Asp.net. In vb I had used MSAccess database. In asp.net I am using Sql server. Now I want to Move or copy the MSaccess database data into Sql server.

Mouthwash

Answer 1

Open your MS Access database, go to tools/database utilities/upsizing wizard. You're done.

Just follow the wizard's steps, make sure you have administrative rights on the SQL server, check all your steps with the integrated help for detailled table/indexes/rules upsizing.

Some database will not upsize easily, due to specific Access/Jet configuration that cannot be imported into SQL. This company has a small module to check this kind of issue and might also propose its own upsizing wizard here.

Allyn

Answer 2

Microsoft Access, if I recall has a Sql upsizing wizard, atleast 2k7 does, previous versions might have it also.

Birkner

Answer 3

You can also use SQL Server directly to import an Access MDB file into a SQL Server database. In SQL Server 2000, this was done using DTS. In SQL Server 2005/2008, this is done with SSIS.

Have a look here for a tutorial:

http://www.accelebrate.com/sql_training/ssis_tutorial.htm

Turaco

Answer 4

If you've got an existing ODBC connection to the MS Access DB, then you can create a sql 2005 database, right click and choose Import (in Management Studio) and run through the import wizard pointing the datasource at the MS Access data connection.

Hope this helps.

Immune

In fact, just looked again and there is an option to point directly at an MS Access file. Not tried this though. Happy importing :-) – Immune

Answer 5

You can use the Import wizard in the SQL server. Select the source as the MDB and the destination as your SQL server.

Vesta

This is the simplest solution. You can import the tables in an existing database. – Tiffaneytiffani

Answer 6

In 2022 it's quite hard to match the local version of Access that has the "upsizing wizard" or a similar feature. Also SQL Server Management Studios import/export through SSIS packages sometimes have trouble recognizing column types. I've came across Microsoft SQL Server Migration Assistant for Access which looks like a stand-alone version of upsizing wizard with a lot of features and verbosity. https://www.microsoft.com/en-us/download/details.aspx?id=54255

Greengrocery

you're out here doing the gods' work in 2022. – Tankard

it's 2024 and this answer saves my day! – Colorful

Answer 7

The upsizing wizard in Access 2007 didn't work for me with SQL Server 2008 Express SP1, but this tool (after an amazingly annoying installation process) worked quite well:

http://www.microsoft.com/downloads/details.aspx?FamilyID=133b59c2-c89c-4641-bebb-6d04476ec1ba&DisplayLang=en

Minim

link dead; goes to generic MS Download page. – Mandatory

Answer 8

i think the probs is with data in tables ,not the table. All the tables might have primary keys. so the next run , due to primary key violation the package is getting failed. before each dump, delete the enteries in the existing sql server by using "Execute sql task " in ctrl flow.

if (table exits ) then delete * from table

Leveret

This answer looks to me like it ought to be a comment. Of course, with only 1 point of reputation, you can't post comments. Which answer was this in reference to? – Maverick