Access database - split or go SQL back-end?
Posted on 2011-05-04
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
- 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?