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.

Answer 1

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

Answer 2

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

Answer 3

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.

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.

Answer 5

Hi Friends, I am having a serious trouble. I dont know, where and how to post the question. If it is a wrong place, kindly suggest me the link. I have searched the net like anything, but still could not solve my problem. probably my search condition was wrong.

Anyway, let me explain my problem. I have an existing application which is using MDB file as a back-end. The size of the database is increasing heavily. Now I have been asked to design an web-application taking this MDB file as back-end. As I doubt, MDB will be very slow on the net, so I have decided to migrate the database to SQL Server2005. But the owner of the existing system doesnot want to change the existing setup. They just want to carry their MDB database only. So now what I want is, export the database to SQL Server2005(which I have already done using SSIS) and then establish a link or something between the two databases such that whenever any change occurs in the MDB file, that should reflect in the SQL database also.

To solve this, I have used the following techniques-

1. Import data to SQL Server from Access using SSIS Package.
2. Create a job in SQL server agent to run this package everyday.

But the could not be solved. when the job is executed, it is having trouble. After much of research, I got to know that, since all the tables already exist in the database, so it is unable to create the same (since my package was a simple one, which was to export data from MDB and import it to SQL)

I hope, I have explained the problem properly. Now kindly give me some solution for this. I am running out of time.

Answer 6

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

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

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