• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

Access database - split or go SQL back-end?

Hello.

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?

Thanks!
0
Uptime Legal Systems
Asked:
Uptime Legal Systems
  • 5
  • 3
2 Solutions
 
Richard DanekeTrainerCommented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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.

mx

0
 
Uptime Legal SystemsAuthor Commented:
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.)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 ...

mx
0
 
Uptime Legal SystemsAuthor Commented:
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?)

Thanks.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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.

mx
0
 
Uptime Legal SystemsAuthor Commented:
Thanks!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Recommend you spend some quality time with the info in the links below ... great stuff I promise:

100 Tips for Faster Microsoft Access Databases:
http://www.fmsinc.com/MicrosoftAccess/Performance.html

Ken Getz tips from Access 2002 Developer's Handbook:
http://msdn.microsoft.com/en-us/library/aa188211%28office.10%29.aspx 

Improve performance of an Access database
http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx

Microsoft Access Performance FAQ:
http://www.granite.ab.ca/access/performancefaq.htm

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
oops .... AND:

Allen Browne's tips for Microsoft Access
http://www.allenbrowne.com/tips.html

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now