Access database - split or go SQL back-end?

Posted on 2011-05-04
Medium Priority
Last Modified: 2012-08-13

We have an access database as our main application.  Its currently a single file (not split between FE and BE).  Its accessed by about 4 to 8 concurrent users at any time over a Terminal Server, so we don't have the usual network disconnect/corruption problem (the accdb file sits on the TS and is accessed via RDP/TS).

However, more and more frequently we're running into corruption problems including (most notably) "out of memory" errors when clicking any control (buttons, etc.) that require a compact and repair to fix.  These errors seem to occur the most after any design changes (forms or tables).

Other quick stats:

* DB Size: about 140 MB
* Users: 4 to 8 concurrent (will probably be up to 10 to 12 in a year or so)
* Format: Access 2007/2010 (accessed via Access 2010 "client")
* Terminal Server Platform: Server 2008 R2 x64
* Tables:
   - About 70 total
   - About 20 of these are "data" tables (the rest being used in combo boxes/drop downs)

* Forms: 50+
* Queries: 70+

I was going to start the process of splitting the file into a FE/BE configuration... but then I thought--maybe I should just make the jump to a SQL BE now (we have SQL licensing), since eventually that will be where we end up.

So, the question: Do I split the DB and go to SQL later?  Or should I just jump to SQL now?

Follow up questions, if SQL seems to be the way to go:

1. How well does the access-to-SQL upsizing wizard work?
2. Once the tables are all in SQL, do I have to do all table creation/design then in SQL Management Studio?  (As opposed to creating them from the access FE)
3. Are there issue with datatypes that may be an issue in the conversion?  (I don't necessarily want to create a huge project right now, if it can be avoided).
4. Are relationships stored in SQL, or in Access?

Question by:Uptime Legal Systems
  • 5
  • 3
LVL 19

Accepted Solution

Richard Daneke earned 1000 total points
ID: 35692744
I would first move to FE/BE in Access instead of doing both.  This will let you isolate your current problem.   If you move to SQL, it would appear that you will be opening a lot more questions in how it runs.  The FE/BE split should work better since each instance will have its own FE with which to work and only the shared tables in the BE will be passing record data between sessions.
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 35692760
"I was going to start the process of splitting the file into a FE/BE configuration."
IF ... you decide to stay with Access, then that is your first 'must do' move.

However, if you have the option to move to SQL Server ... and you expect this db to grow MUCH larger in size (it's not that big now) ... that this could be a good idea.  So far however, based on the metrics you presented ... not seeing an issue with Access per se ... all things being equal.

As far as Relationships ... IF ... SQL server becomes your BE OR if you split (please do) ... then the BE is going to contain the Relationships.



Author Comment

by:Uptime Legal Systems
ID: 35692780
I do NOT see it growing in size that much.  (To put it into perspective, It hovered at around 70 MB for a very long time when it was 2003/mdb format.)  I DO expect the concurrent users to increase over time (from the 4-8 now to maybe 8-12 in a year or so).

So... if I'm hearing you right, access BE is still suitable for the foreseeable future?  (It certainly makes things simpler and development quicker.)

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 75
ID: 35692803
Re size matters ... can I assume you have done a Compact and Repair on the db ... very recently ?

Also ... not seeing 12 users as any issue either ... 'depending'

I say ... start with Splitting - easy to do ... and continue ...


Author Comment

by:Uptime Legal Systems
ID: 35692823
Yes, doing regular compact and repairs.  (I'm having to do them with increased frequency as of late.)

Then I will do FE/BE split then.  Thank you for the feedback.
Last question:

Even in a terminal server environment, each user should have their own FE file they access?  (Is that correct?)

LVL 75
ID: 35692884
"Even in a terminal server environment, each user should have their own FE file they access?"
For sure.  Tried and proven most reliable approach.

TS ... is a GOOD choice.


Author Comment

by:Uptime Legal Systems
ID: 35692892
LVL 75
ID: 35692906
Recommend you spend some quality time with the info in the links below ... great stuff I promise:

100 Tips for Faster Microsoft Access Databases:

Ken Getz tips from Access 2002 Developer's Handbook:

Improve performance of an Access database

Microsoft Access Performance FAQ:

LVL 75
ID: 35692911
oops .... AND:

Allen Browne's tips for Microsoft Access


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question