Question Access V SQL

[Charles Fish]

I am attempting to put together a database for the engineering department in the company I work for but I am being advised that Access does not deal well with multiple queries and the best database building tool would be SQL. I would like to know if this is fact the case. As a comparison of the two platforms what are the pros and cons of each and which would be the best application to use?

 

[gemma-the-husky]

access is probably easier to learn. it will hanlde databases up to 2Gb in size, which repesents hundred of thousands of records. The number of concurrent users is not so high as SQL

SQL offers more security and handles more data, if you reach the limit of access

Personally, unless there are strong grounds not to use an Access backend, I would stay with access.

 

[Charles Fish]

Thanks for this.

 

[Banana]

First of all, it should be realized that comparing Access to SQL Server is comparing apples to oranges. The problem here is that Access is more of an IDE that comes with a database engine whereas SQL Server is just a database engine. Therefore, you don't have to have to use Jet/ACE (the database engine); you can use Access to build forms with SQL Server data (or even with MySQL, PostgreSQL, Oracle, DB/2... whatever has ODBC support).

See if this helps explains why the comparison doesn't work.

To deal with the multi-user and concurrency specifically, as long you design your applications with server-client architecture in mind, it'll scale fairly well and makes it easy for you to move from Jet/ACE to SQL Server but without changing your Access front-end. Maybe right now, there's no good reason to use SQL Server since it may be overkill but it doesn't mean it won't ever be upsized and it's in your interest to make it easy as possible by incorporating this in your application design from start.

To get some ideas of some considerations required for working with SQL Server (or other ODBC backend of your choices)

Beginning SQL Development
Beginner's Guide to ODBC

Hope that helps.

 

[Charles Fish]

Ok, it is evident then that i am no expert in this. It has been stated by my boss that Access has problems with processing multiple quieries. With 2007, is this the case? On trying to build an application I want something that easy to change alter add to etc. Is access the best way forward particularly bearing in mind that I already have access 2007 and the cost is only time on my part.

 

[Banana]

I think we need to understand what actually is meant by "processing multiple queries". As far as I am aware, Access is capable of processing several queries in a series, executing a queries with several joins to other queries & tables, and can work with as many as millions records (though with that large number, optimization is very important), and with a split database, can support concurrent users executing queries at same times, though the specific numbers can vary. Some reports practical maximum of 30 users while other has reported 100+, but when Access is using different backend such as SQL Server, the maximum numbers of users is only limited by the server's hardware.

 

[Lightwave]

Does your boss have any evidence for this statement?

Is he an experienced database designer?

Are you trying to do anything particulary complicated?

eg
Real time calculation of multiple fields.

If the answer to these questions are all no I would probably say Access.

If your designing a database for an administration procedure or stock control with little or no live links to information I very much doubt that you will hit the buffers of access anytime soon. (unless you try and put pictures in it)

The limits are at file size 2gb and for concurrency number of users in the system at any one time and like banana says SQL server is a back end so all the skills you use for Access will be directly of use to you should you decide to use SQL Server later. (unless you use a different front end or IDE Integrated Design Environment to design it and even then the principles are relevant)

I would suggest you start out with access even if its for a couple of weeks and get a prototype up and running it will give you a good grounding in system design at that point you can choose to migrate to SQL if you wish.

Systems take time to design and build no getting round it and you have to learn to walk before you run.

Now if this system is business critical , needs to be used by loads of people and will contain very valuable information then you really need to seek professional help.

 

[boblarson]

Access doesn't have problems with processing multiple queries IF (and I repeat IF) things are set up properly and things are taken into account.

Examples:

1. Access should not be run as a standalone across a network. It should be split, with the backend having the tables and that can be on a file server and then a COPY of the frontend (everything but the tables) is located on the user's machine.

2. Queries, if you want them to work well, must be structured as to avoid Access needing to send the whole table over the network so that the frontend can process it. If you say, "well, then I won't split it" you would fall into the biggest trap in that then Access is running on your machine but the file is on the server. At that point you would be having Access send EVERYTHING over the wire (networK) and that would make it worse.

Optimization of queries is a good read here (as well as other performance topics):
http://www.fmsinc.com/microsoftaccess/performance.html

 

[Charles Fish]

Thank you all for the replies. Much appreciated. I can at least go back and argue a case for using Access..(or not!)