MS Access Front End with SQL Server Back End

[dgunner71]

Hello all,

I'm not sure if this question belongs in the SQL Server forum or here but I would like to get some opinions on using SQL Server 2008 for the back end of an MS Access 2013 Front End Database.

Number of users are up to 6 concurrent (generally 3).
The current size of the db (after 5 years) is 152,808KB (yes, KB - lol).

I'm considering SQL Server (vs. a standard Split Access db) for several reasons:

1. At some point, I may wish to allow clients to access their respective work orders in the system via the internet;
   
2. SQL Server can support access from a wide-range of front-end software/platforms (this will support #1);
   
3. Stability - the current Access database has numerous memory errors (that are undoubtedly caused by poor design) - I understand SQL Server is significantly more stable and less prone to corruptions (which, thankfully are not a big problem right now);
   
4. I'm told that the SQL Server back end can grow almost infinitely (or at least significantly larger than the 2GB Access limit)
   
5. Finally, I'm told that in addition to providing faster queries SQL Server is just a better platform and the way to go.

I wanted to get some validation on these points.

Also - I was looking for some advice, tips, and cautions on using SQL Server as the back end for an MS Access Front end as I have yet to 'walk down this path'.

Kindest regards.

Gunner

[Seth Schrock]

I do this with a couple of my databases and it works wonderfully. There are a few differences that you have to watch out for in how you code your recordsets and also how you deal with yes/no fields ("bit" in SQL Server), otherwise it feels the same to developer. It does provide more capabilities for querying information about the database itself using System Views which can come in handy at times, but I don't use them very often for my databases.

[dgunner71]

Thanks for your insight, Seth. That is what I was really looking for (someone who's done it and had success).

I also understand that I should stay away from 'allow multiple selections' (new in 2010/13).

Kind regards,

Gunner

[Seth Schrock]

Even though Access allows it, I would always recommend staying away from multi-value fields as it is considered poor database design. There are situations where it seems like it would be nice, but there is always a way to do it using a related table.

[dgunner71]

Thanks, Seth. This is quite tempting (I have several fields that were just made for multi-value fields) but I understand that the concept is in early stages of development so that no other programs will recognize the field just yet.

Thanks again,

Gunner

[zmbd]

The lookup-field and multi-value-field in Access will not scale up.
Memo Fields can cause you issues.
YES/NO fields can cause issues.
There are a lot of token words, spaces, and symbols that people like to use in Access field names that do not transfer well when scaling up to the SQL/Oracle type databases.
The better your database is normalized the easier the transfer.
et...

[jimatqsi]

dgunner71,
Number 1 is not a reason to choose SQL over Access, as your web page/app can deal with either. A couple questions:
1) Is your 152MB size after compact and repair?
2) Is your DB already split into a front-end/back-end? If not, your "numerous memory errors" may be quickly and easily remedied.

Some things become harder to do when you use SQL for the data warehouse. Some things can be done in SQL in light speed compared to Access, but don't expect all that speed to show up on the Access side (there will be some improvement). A lot is lost in the ODBC connection. Different drivers can make big differences in that.

152MB and 3-6 users are not big numbers. Your operating environment does not demand moving to SQL by any means. But you would learn a lot and there would be some operating benefits, offset by some pains. The balance of benefit/pain is not obviously one way or the other for your situation.

Jim

[GKJR]

I have also been considering learning how to develop in SQL Server. My current application isn't anywhere near the Access bottleneck, but I am considering building some other applications that would be. Right now my company's database is less than 10 MB compacted (back end) and about three concurrent users. In some of my research I learned that about 200 MB or 20 concurrent users would be a reasonable point to up-size to SQL Server. I bought a book and it seems like it is a completely different beast than Access - steep learning curve.

[dgunner71]

jimatsqi,

Sorry for the delay -

Regarding allow clients to access records via the internet, my concern was with multiple people accessing the db at the same time. However, my intention (at least for now) would be viewing only so this might work just fine.

You are correct - 152MB is the size after compacting.

Yes, my system is already split into front end and back end and each system user has their own respective front end.

Thanks again for your advice.

I've already laid out the design for the project (tables & relationships) and I have the conceptual UI designed. I got side-tracked with another more pressing project these past few weeks so I'm expecting to jump back into this first week in April.

Thanks again!

Gunner

[dgunner71]

GKJR -

In my experience, 20 users in access (if all simultaneous) might be a bit too many. It can handle it in theory but in practice it may not be the right tool.

I too have spent some time reading up on SQL Server and it does seems like a lot to learn but so did access the first time we picked it up so I'm hopeful.

Good luck!