Move Access 2010 to SqlServer 2008R2 (or current cersion) or SQLServer Express or MySQL

I'm sure this has been asked before. I will layout as much as possible to see if It's worth moving my Back End (BE) Access database to one of the SQL choices.

We have 2 databases. Both in Access 2010. The BE contains all the tables and queries. Update, Delete, Add and simple search queries that return data. The Front End (FE) contains only one table with one row (for version control).

The total size of the BE is less than 1 gig. This is a conservative size according to what we currently have in the database and how large it will grow. It will eventually include archiving of tables > 3 years old.
There are 44 tables (currently no temp tables) and about 60 queries. A lot of lookup/Validation tables (about 20) such as ClientMaster, TransationType etc. We will have up to no more than 10 (usually a max of 5 at a time) accessing the system.
The FE houses all the forms, does the call to the queries on the BE which access, deletes, appends etc to the BE data.

One  BE database. and each user will get their own copy of the FE. Currently each user while accessing the same tables to the BE will very unlikely be accessing the same rows. They are assigned specific Unique keys for the data they will be accessing. It is true there will be bulk retrieval of entire tables. The largest table will be no more than 3000 row for these bulk loads. Usually into list boxes, comboboxes, datasheets, etc. The majority of the vba code in the FE will run against the specific Keys and related data for each user.

We have seen even with one user the process can run slow. The BE sits on a server locally(same city) as 80% of the users accessing it. The 20% of the users are currently offsite (in Arizona) still will access the local server.

For the BE I'm not sure of the network share server, model, storage space, cpu, memory etc. We found moving the FE to the local pc (Windows 7, 8 gig memory, 250 gig SATA drives (no SSD yet) improve the process a little. Maybe 10% depending on the user load, query running etc. That issue is that the FE will not be backed up. Even though it really does not house any data it could lockup and have a user have to start the process all over again.

This is a new process written to replace auditing of contracts from a manual process to a automated process. It is very intense VBA code on the FE. Mostly exception and very little table driven. So this is not a existing or finished product. It is being written as we speak.

Did I give enough information to ask the question of keeping the BE in Access. Or what are my options (# of users total and simultaneously, total storage size, total number of tables and the work needed to move the tables and queries to one of the SQL choices.
Obviously cost will be a factor in the decision. Mainly the cost of the BE solution and if there is a lot of work to do the actual move.

As far as I know it's whatever current version of SQLServer available (or we may be able to piggy back on a SQLServer license currently installed. That is highly unlikely. Don't ask why but believe me it is). The limits for SQLServer Express which is free and MySQL which I believe is also free. I know a move away from Access with a SQL option in the very least will involve ODBC configuration on the FE for each user or will the ODBC only be necessary on the BE to the SQL tables? And are these to be all Linked tables?

I realize without looking at  the actual code and data it's hard to give a definite answer but if anyone has done similar moves I'd appreciate hearing what you had to go thru. One thing I like about the move is to user SQL pass-thru so I will be able to write true SQL and eliminate some of the issues I have with having to do a process in multiple queries.

Thanks for your time!
Chuck LoweAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

AndyAinscowFreelance programmer / ConsultantCommented:
I've migrated an Access BE to SQL Server, but the FE was coded in C++
Stability and performance both improved noticeably. The FE required some changes but not many.  I did however have to rewrite a maintenance tool that was part of the suite.  (Both FE and BE were at client organisations - no direct contact from my systems).

As a rule of thumb Access will have performance problems (at least it did have in the past) with more than 5-10 concurrent users.  You don't actually say how many users you have working simultaneously.

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
Chuck LoweAuthor Commented:
Sorry maybe I wasn't clear. I stated we will have a max of 5 at a time. So 5 concurrent users. But also as I stated working on their individual rows by unique key. These 5 will however be "reading " lookup tables with similar rows.

Those that help with the picking of which SQL product to go with for the BE?
AndyAinscowFreelance programmer / ConsultantCommented:
Oops,sorry - you did say usually max 5 users.  In that case I honestly can't say if you will see any performance improvement (and you may obtain more just from optimising inside your current FE/BE if you find parts of your current design/code are inefficient).  Should you experience stability problems then a change probably would be worth it just on those grounds.

I know that isn't really what you wanted to hear.


ps.  The unique key is possibly a bit of a red herring, reading isn't usually a problem, writing is.  Table vs Page vs Row level locking.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
Hi Chuck,

Almost all of the Access applications we build for clients use SQL Server for the BE.  And we've assisted multiple clients in migrated large Access BEs to SQL Server.  You can't just migrate the tables and call it good - you need to optimize the Access FE to use the full power of SQL Server.

In your case, I think there are good reasons to migrate.  Security, stability, back-ups, and speed over WANs are all better with SQL Server.  You also allow the possibility of developing an additional browser front-end if desired.

I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

http://www.JStreetTech.com/downloads

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Cheers,
Armen Stein, Access MVP
J Street Technology
Chuck LoweAuthor Commented:
Project has been abandoned. Please close this question.
AndyAinscowFreelance programmer / ConsultantCommented:
It is up to you to close the question, you asked it.
Review what was asked for and what was told to you.
Chuck LoweAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for ChuckLowe's comment #a41307445

for the following reason:

Closed
AndyAinscowFreelance programmer / ConsultantCommented:
Just abandoning is not a reasonable way to close a question.
AndyAinscowFreelance programmer / ConsultantCommented:
Split, both experts provided input as to why moving away from Access might be good.
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
.NET Programming

From novice to tech pro — start learning today.