MS Access Application - Convert data storage from Access to SQL Server

sql-server sql-server-2005 ms-access vba ms-access-2007
Ronnie Overby picture Ronnie Overby · Feb 18, 2009 · Viewed 8.8k times · Source

Bear in mind here, I am not an Access guru. I am proficient with SQL Server and .Net framework. Here is my situation:

A very large MS Access 2007 application was built for my company by a contractor.

The application has been split into two tiers BY ACCESS; there is a front end portion that holds all of the Ms Access forms, and then on the back end part, which are access tables, queries, etc., that is stored on a computer on the network.

Well, of course, there is a need to convert the data storage portion to SQL Server 2005 while keeping all of these GUI forms which were built in Ms Access. This is where I come in.

I have read a little, and have found that you can link the forms or maybe even the access tables to SQL Server tables, but I am still very unsure on what exactly can be done and how to do it.

Has anyone done this? Please comment on any capabilities, limitations, considerations about such an undertaking. Thanks!

Answer

Renaud Bompuis picture Renaud Bompuis · Feb 18, 2009

Do not use the upsizing wizard from Access:

It will do a lot for you:

  • move your data from Access to SQL Server
  • automatically link the tables back into Access
  • give you lots of information about potential issues due to differences in the two databases
  • keeps track of the changes so you can keep the two synchronised over time until your migration is complete.

I wrote a blog entry about it recently.

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More