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.
LVL 15
David L. HansenCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Frank McCourryV.P. Holland Computers, Inc.Commented:
Use the upsizing wizard.  It's under Database tools.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
With Access 2007, make sure yiou have at least SP2 update installed or you will have issues even with an SQL Server back end. This is very important. I will not even support Office Access 2007 without SP2 or later installed.

I have found that the SSMA for Access is much better that the built-in Access upsizing wizards (in all versions of Access).

Is the Application already split?

Unless you actually need the feature in the new ACE (.accdb) format I keep the front end in the mdb format. You can create a MDE with Access 2007/2010/2013.

I find it critical with a ACE (.accdb) format database to never share the database. This means yu must split the database and each user have their own copy of the front wend.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David L. HansenCEOAuthor Commented:
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?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
See: http://technet.microsoft.com/en-us/library/hh313039.aspx

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.
0
Jim P.Commented:
One of the biggest things is to have a primary index on every single table you convert.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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.
0
David L. HansenCEOAuthor Commented:
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.
0
David L. HansenCEOAuthor Commented:
Thanks.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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.
0
David L. HansenCEOAuthor Commented:
Thanks HiTechCoach :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.