Q
Problem solve Get help with specific problems with your technologies, process and projects.

Moving from Access to SQL Server

We are converting a very complex Access database with multiple native tables, linked network tables, subforms within forms, macros and VBA code, just to name a few of the complexities. We first targeted Oracle but workbench just couldn't seem to handle triggers, subforms or autonums. We are now targeting SQL server (we have defined and set up a cluster). It is also not handling the linked tables and subforms well. Help. Just how does one move out of Access (DB and front end) into another platform (front end still in Access).

These kinds of tasks are a little more complex because we have to take care of the objects that we'll move (or migrate) from Access to SQL Server. SQL Server has some tools and wizards that can help you, like the Enterprise Manager, Query Analyzer and others. SQL Server does not manage forms, so you only can move the other objects:

Table -> You must check the new data types and select the one that matches your original one.
Query -> Those could be converted in queries, views, or even stored procedures or triggers
Your VBA Code -> You can't use it in SQL Server, however you can try to develop stored procedures or triggers to accomplish the same task.

There's software that can help you to accomplish part of the move from Access 2000 to SQL Server 2000 easily. You can download it from this site:
http://office.microsoft.com/downloads/2000/Accsql.aspx

Also there's a document that would be helpful to read:
http://www.microsoft.com/sql/techinfo/deployment/70/accessmigration.asp

 

For More Information

This was last published in February 2002

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close