Access vs SqlServer for a Simple Database

Question

I am currently developing a very simple database that tracks people and the company certifications they hold. This db will need to store letters of certification as PDF files. I was asked to develop this in MS Access, but I think it would be better to build it in SQLServer and use Windows Forms to build the interface. This app will need to be accessible from a public location like a share drive.

My question is, would it be better to do this in SQLServer like I think, or am I full of it and my boss is right on the money? Or are we both wrong?

Answer 1

An application built on SQL Server will be more robust and support more users than one built on MS Access.

An application built on MS Access can be easier to debug because there is a lack of a server, lack of significant multi-user support, and the entire database sits in a single file on disk that is easily copied around.

Given that, in almost every case, I choose SQL Server over MS Access.

Answer 2

If the application is, as you said, a very simple database, that's what access is precisely for, creating simple databases. You can write both the database and the application forms within the same environment and users won't need to get anything installed.

Be careful though with concurrent access to your application. If you go for the access solution, multiple users won't be able to use the application at the same time. If you want this to happen, you will need the database and the application being apart. This doesn't mean that the DB needs to be SQL server, you can still use Access as your database if you don't require the power of a more complex engine.

EDIT: Just read on a comment that you are planning to have 10 users and less than 1000 records. FORGET about SQL server, you will be wasting your money. No matter if you decide to go for a simple all-access solution or for a distributed web application or desktop app with remote storage, Access is hundreds of times more powerful that what you need. Even for the "toy-ish" engine that access is, you are not using a 1% of it.

Answer 3

Hard to say without understand the environment. It could be that all users have Access, but no desktops have .NET Framework (although this would be hard to believe). Maybe all their applications are on Access and that's all the developers and users there know.

Given all that nonsense tho, You ought to use SQL Server Express 2008 and Visual Studio 2008 Express. Hope I don't get you fired. :)

Answer 4

This is an extremely difficult question, without an understanding of the scale of the application. In my opinion, I wouldn't touch Access with a 33.5 ft pole.

Benefits of Access:

1. No dedicated machine necessary
2. No problems with portability

Benefits of SQL Server

1. Better SQL compliance
2. Better management control

Are you planning to store PDF files in the DB? If so, why?

I choose SQL Server.

Answer 5

If it is and will always be small and simple, why not store it in an XML file or use a .net DataSet saved as xml? If it is too big for XML, I'd at least look to SQL Server express.

Answer 6

Argh, do the world a favour & use SQL server. Express editions are free, and plenty powerfull enough for your requirements. You can even continue to do the form/report/UI design in Access if you so desire, it can hook up to SQL Server very easily and transparently.

Pros of SQL over access: concurrency, scalablity, reliabiliy, less future developers trying to hunt you down and kill you.

I would almost never use access for a db if I could avoid it, now that SQL Express exists.

Answer 7

A good alternative to Access which I use a lot is SQL Server Compact (SqlCe). This is a completely different product than SQL Server Express/Standard/etc. It is an in-process database like Access, it does not run as a separate process or service.

• It is free
• Full ACID support
  • Supports multiple connections
  • Full transactional support
  • Referential integrity (including cascading updates and deletes)
  • Locking
• T-SQL syntax and SQL Server data types (same API as SQL Server)
• Small footprint (~2 MB)
• Easy deployment (supports ClickOnce, MSI, XCopy, etc)
• Database is contained in a single file you can move around
• Supports ADO.NET, LINQ to SQL, LINQ to Entities.

Answer 8

If I were you I'd go with what some of the others said and use SQL Express:

http://www.microsoft.com/express/sql/default.aspx

You get all the benefits of a true SQL environment FOR FREE! You can even use the SQL Management Studio as well as leverage Reporting Services (if you download the advanced pack). Download link below:

http://www.microsoft.com/express/sql/download/

Which edition of SQL Server 2008 Express is right for you? SQL Server 2008 Express is available in the following 3 editions (each is available from the Install Wizard):

SQL Server 2008 Express with Tools SQL Server database engine - create, store, update and retrieve your data SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases SQL Server 2008 Express with Advanced Services SQL Server database engine - create, store, update and retrieve your data SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases Full-text Search - powerful, high-speed engine for searching text-intensive data Reporting Services - integrated report creation and design environment to create reports SQL Server 2008 Express (Runtime Only) SQL Server database engine - create, store, update and retrieve your data SQL Server 2008 Management Studio Express (SSMSE) Free, easy-to-use graphical management tool for configuring, managing, and administering SQL Server 2008 Express. It can also manage multiple instances of the SQL Server Database Engine created by any edition of SQL Server 2008 including Workgroup, Web, Standard and Enterprise.

Note: This separate download is designed for customers who have previously installed SQL Server 2008 Express (Runtime Only). If you are looking to download SQL Server Express and SSMSE for the first time, please download the SQL Server 2008 Express with Tools from the Install Wizard.

Access, to me, is really a hobbyist tool or for REALLY basic local projects at this point that just hasn't been phased out yet. I'd definitely look to using SQL Express, plus it makes it that much easier to upgrade if you get beyond the 4 GB database barrier in the future.

Answer 9

Go with what you have the best skills in - Access is great for a variety of projects andit can scale really well if you know what you're doing. Conversely, a bad programmer could do an app in Win Forms and SQL Server and produce a badly performing monster.

I personally would go with SQL Express and a Win Forms or ASP.Net front-end as experience shows that these small projects can easily develop over time in to much bigger applications than was originally planned and a well structured database built on SQL Server can scale up more readily to more users if needed.