Built a simple VB application in MS Access 2007 that manages forms Tables Shipping & Receiving info....would switching the database to SQL be better

....without loosing the interface within Access which everyone is comfortable with. Our issues lie with a comfort of the Access interface. We seem to be having a problem with performance when multiple users are in and we happen to be dumping data from another system into a table in our Access Application.

We thought that maybe we could send the data to a SQL and it would end any problem with performance we just do not want to loose the interface. Can this be done with relative ease. If so how ......?
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?

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

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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Converting an Access FE to VB would cost some $$$, and if the users are comfortable with the Access front-end, it's likely not worth the expense.

You can move the tables to SQL with something like this <overly simplified>:
(1)  Open SQL 2008, preferably R2
(2)  Create a new database
(3)  Mouse over to your SQL database, then Right-click: Tasks: Import Data
(4)  Follow the prompts to import data from your Access app into SQL.
(5)  Open up your Access app, and link your SQL tables into Access.
(6)  Rename your Access tables, rename your linked SQL tables to exactly the same as the Access tables were named, then back up the Access tables somewhere.

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
Converting the backend to SQL Server, in and of itself, may or may not make all your performance problems disappear.  However, it certainly will position you much better for addressing the ones that persist.

You also get to keep the familiar UI and perhaps, more important, the familiar and powerful development environment that access provides. There may be some learning curve on the SQL side, but it's all goodness.

Relative ease????  Compared to any other way of migrating to a more scalable environment switching an Access backend to SQL Server with DAO linked tables is a cake walk. If your Access coding is conventional, only minor coding changes are required.   Autonumbers and some other datatypes don't work quite the same--but, you will soon learn what to watch for and how to adapt the code.

Lastly, to really take advantage of the backend power, you want to push certain kinds of processing there.   Remember, with the Access BE, all processing is done in the Access FE.   Not so, with SQL Server.  For example, if you have a query that joins two tables, then it's usually better to do the join on SQL server and return the result set over the network rather than to return full tables over the network and do the join in the Access FE.  Access does a pretty good job of pushing appropriate work to the backend, but it is not perfect.  For example, if the above mentioned join uses an Access function, then SQL server cannot handle it and the work gets done in Access.  However, if you modify the join to use  an SQL server equivalent, then the work gets done on the backend.

You will likely get your application running on SQL Server in relatively short order. It will take time and experience to maximize your new environment.


Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Here's a couple of good article about moving to SQL Server, written from the Access point of view:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp - see The Best of Both Worlds article

Jeffrey CoachmanMIS LiasonCommented:

<Converting the backend to SQL Server, in and of itself, may or may not make all your performance problems disappear.>
Great point.
For example, simply porting inefficient data structures to SQL will not increase performance

<We seem to be having a problem with performance when multiple users are in>
"Performance" is a very broad term and must be quantified in exact parameters.
There are dozens of factors that will contribute to "Performance" (real or perceived)
So see here as a start:
(Could be anything:poor table design to inefficient VBA coding Networking issues, Form design, query design, ...etc)

<we happen to be dumping data from another system into a table in our Access Application. >
Not sure what this means of how it might effect "performance", but it may be an option to "Link" to this data instead of "dumping" it into your existing Access app.

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
That is all good stuff. mlmcc and I thank you sincerely.
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 SQL Server 2008

From novice to tech pro — start learning today.