I want to change an Access DB to be the front-end only; the backend will become SQL Server.
I have an old Access Database which was imported recently into Access 2007. Ever since this happened it has become more and more prone to good data suddenly becoming corrupt (looks like Chinese characters) and reports just not running. Not to mention searches that work one day then are broken the next (and stay broken).
This being the case, I'd like to see what it takes to move the data to SQL Server permanently, and alter the vba code (if this is necessary) to accommodate it.
I'm looking for general guidelines and advice from individuals who have already done this sort of migration.
This being the case, I'd like to see what it takes to move the data to SQL Server permanently, and alter the vba code (if this is necessary) to accommodate it.
I'm looking for general guidelines and advice from individuals who have already done this sort of migration.
Frank McCourry
Use the upsizing wizard. It's under Database tools.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. That covers the tables being moved. How about the vba code? What are the most common hurdles to overcome? Will it likely be an extensive rewrite of code?
See: http://technet.microsoft.com/en-us/library/hh313039.aspx
Not usually. I find a majority of the code will work. The main thing I have to update is the VBA code that works with recordsets to include additional parameters.
I do have queries that will no longer work properly or get very slow.
It is possible that your application can become slower with an SQL server back end if ther front end was not designed properly. i see this with applications taht were not already split and working properly that way.
Do you use any DAO or ADO recordsets?
Note: You do not have to convert your DAO code to ADO.
See:
Beginning Sql Server Development
Beginners Guide To ODBC
FWIW: I split ever Access database into a front end and back end. I do it from the start.
Will it likely be an extensive rewrite of code?
Not usually. I find a majority of the code will work. The main thing I have to update is the VBA code that works with recordsets to include additional parameters.
I do have queries that will no longer work properly or get very slow.
It is possible that your application can become slower with an SQL server back end if ther front end was not designed properly. i see this with applications taht were not already split and working properly that way.
Do you use any DAO or ADO recordsets?
Note: You do not have to convert your DAO code to ADO.
See:
Beginning Sql Server Development
Beginners Guide To ODBC
I want to change an Access DB to be the front-end only;You should always do that with any shared Access application. No matter what back end you use.
FWIW: I split ever Access database into a front end and back end. I do it from the start.
One of the biggest things is to have a primary index on every single table you convert.
Great point jimpen.
I wish Access would not let you create a table without a primary key.
The SSMA for Access also points out issues like no primary key.
I also add a timestamp field.
I wish Access would not let you create a table without a primary key.
The SSMA for Access also points out issues like no primary key.
I also add a timestamp field.
ASKER
Thanks you guys I really appreciate it.
As far as the SP2 update. Do I need to make sure that each workstation has it installed, or just make sure that the development station alone has it (mine)?
Also, it uses DAO only.
As far as the SP2 update. Do I need to make sure that each workstation has it installed, or just make sure that the development station alone has it (mine)?
Also, it uses DAO only.
ASKER
Thanks.
As far as the SP2 update. Do I need to make sure that each workstation has it installed, or just make sure that the development station alone has it (mine)?Every PC that is running Access 2007 will need the update for Access to operate properly.
ASKER
Thanks HiTechCoach :)