Access to SQL Server 1

[wallaceoc80]

I am relatively new to the world of Databases so forgive me if this is a trivial question.

I am working on the development of a Web Application that will be using a SQL Server database. However, the Web app is to replace a currently existing stand-alone MS Access application.

My question is:

Is there a way that I can convert the MS Access database to a SQL Server database?

Also, is there a way that I can move the data from the Access tables to the SQL Server tables one row at a time as opposed to a bulk move?

Thanks in advance for your help.

Much appreciated,

Wallace

 

[MakeItSo]

Hi Wallace

a) Sure: Just klick on Tools-Data Transformation Services->Import Data.
Choose your Access-DB as Source and your desired SQLServer-DB as destination.
b) Not sure, why you would like to do that. If you want to moive only certain, specified rows, you can click on "transform" in the import options and apply a row filter

Hope this helps,
Andy

 

[wallaceoc80]

Thanks Andy,

just a quick question. This Tools-Data Transformation Services- Import data is on SQL Server and not Access?

Thanks again,

wallace

 

[wallaceoc80]

Sorry also forgot to ask how good this conversion process is? Will I have to add in stuff like integrity constraints, etc...?

I will be using the Database in an application that is pretty important and I can't afford for it to be crashing!

Regards,

Wallace

 

[SQLBill]

You might want to take a look at FAQ183-2935. Convering Access DB to SQL Server.

-SQLBill

Posting advice: FAQ481-4875

 

[adamroof]

Hey wallace, just another point, as i did convert Access to SQL, set the default value of any BIT Type in the SQL table to 0 (or 1) for checkboxes, otherwise if you still use Access as a front end, you will get 'Drop Changes' Another user has modified the record Errors. Also keep an eye on your Text fields, usually NText will be better than Varchar, and some monetary/Percentage records will like Float as opposed to Money Types.

Access to SQL(Upsizing) is bad, SQL Importing Access is more stable.