I have Access database, How can I upgrade it to SQL Server database?

Question

I have a good experience in Using and Programming MS Access database and I am practicing it everyday.
but it is a little bit slow because it is shared on the LAN with multiple users. As I know from a course I tooke about SQL sever database it is very fast . what are the basic steps to upgrade MS Access database to SQL server database?

Answer 1

Good answers, but no one has addressed the front end.



Only your data can migrate over to SQL. Your forms, queries, reports, macros, and modules cannot migrate.



You can however use Access Project for your front end.



Queries get replaced by Stored procedures, which are all SQL Code. There is no nice GUI to help with the design. The good news is that you can take the SQL code for a query and make a stored procedure after some modifications.



All your forms and reports will be OK in Project, but the code has to be ADO, not DAO. If you are using ADO Code now, then you are going to be able to debug it more easily than if you are using DAO now. It can be frustrating.



Good Luck and have fun.

Answer 2

What you are trying to do is use access as front end to an SQL DB (assume SQL Server 2005). One method is to use ODBC to access the SQL server. Search the net for tutorials on access and sql server. or look in the code. It is a lot easier to keep the db in access unless you have a really big db project.

Answer 3

There is no upgrade path for the applications. Are you asking how to convert the data from Access to SQL? So you would need to buy and install SQL Server and then import the data from MS Access.

Answer 4

Access and SQL server are two entirely different products. You cannot upgrade Access to SQL. If you want SQL, you will need to buy the full version. Once you have SQL server installed, you just import the Access tables.

Answer 5

SQL server has a tool which will migrate Access databases up. Otherwise just dump the files to flat files and use SQL server's tools to reload them.

Answer 6

If it is a one time thing I would reccomend using the upgrade wizard the poster above me suggested. Here is a good link : http://sqljunkies.com/Article/BD909D1E-3EA1-4323-889E-5E3A5415B977.scuk



If it is a constant reoccuring task or conversion of types need to be done, I would suggest you familiarize yourself with Data Transformation Services. This is especially good for transforming data into data types that are better suited for SQL Server.



Here is a site for you to check out DTS : http://www.sqldts.com

Be aware that DTS is not for the faint of heart and could easily fill many thick books. But, on the other hand it is very flexible and powerful. Oh - and its also fun to draw out the diagrams, hehe!



Good luck and have fun!