CODEWITHSUNDEEP
asp.netsql-servervb.netms-access
saran
saran

Reputation:

How to import mdb to sql server

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.

Upvotes: 15

Views: 63855

Answers (8)

w5ar
w5ar

Reputation: 71

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

Upvotes: 2

anitha
anitha

Reputation: 1

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

Upvotes: 0

Derek Slager
Derek Slager

Reputation: 13831

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

Upvotes: 1

Bhaskar
Bhaskar

Reputation: 10681

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

Upvotes: 4

WestDiscGolf
WestDiscGolf

Reputation: 4108

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.

Upvotes: 4

Philippe Grondier
Philippe Grondier

Reputation: 11138

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.

Upvotes: 11

jwalkerjr
jwalkerjr

Reputation: 1809

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

Upvotes: 5

Ta01
Ta01

Reputation: 31610

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

Upvotes: 5

Related Questions