SQL or Access

[johncassell]

Hello there,

I'm looking for some advice on which database to use when developing an application.

My client only uses one PC in his office and uses MS Access on it's own to view and edit his orders.

I told him that I would make an application and convert his database into SQL server express (to be honest I told him this because it sounds a bit fancy and also I was looking longer term if his orders increase so wanted something which would hold more data).

This would be fine but I started to think about how SQL server would possibly decrease his systems performance (how much memory and processor use etc..) and thought maybe it would be best to stick to Access instead.

This is a question that will come to the forefront of my mind whenever making an application so I thought it worth a shot asking for some advice from people who have had experience in both.

Many thanks for any replies.

John

 

[ss7thirty]

You can always continue to use Access and if the needs of the application changed you could easily convert it to SQL. However, Access can only hold a certain ammount of records in a table (255000 i believe). You must take into account all the limitation of using Access. If there is a lot of code behind the application you will also have to convert it to work on SQL. The plusses of SQL are that you can implement stored procedures, sizes of the database, and speed of the transactions. However, for a program that will only be used on a single PC by a single person, you may just want to stick with access, but if you see growth in the future (more than 1 person using the application) you may want to go ahead and save yourself time later on down the road and just use SQL now.

 

[cjard]

Stick with access.. it will hold more than he will ever need, and in a one user environment, it's usually good enough. The benefits of moving to SQLSEx are not significant enough here..

 

[johncassell]

Hi all,

thanks a lot for the replies.

ss7thirty: Would you mind explaining the benefit of stored procedures (At the moment that means nothing to me!) and also when you mention the speed of transactions, does that mean that, for example, in a multi-user environment, i could say that entering data in a form will be fast and any reports being run will not slow that down (i.e reports are a non-priority item).

cjard: Yes, i have no doubt that for this current project, Access will be enough but I was thinking of future projects and both your input has been invaluable.

Thanks again

John

 

[cjard]

If youre looking to expand this in the future, then cutting your teeth on SQLS right now while it is still relatively simple, would be sensible. A stored procedure is code that resides inside the database. Because it is closer to the data store certain operations can be made quicker. Its not a magical performance improver though and many operations are made more awkward by stored procedures.

Access used by a single person vs SQLS used by a single person should have imperceptible differences in response delay. It is when things go multi user that SQLS starts to have the edge. Access really, really wasnt designed for multi user at all.. Dont try it!

End of the day, you can get some great docs on the internet about comparsions between the two - seek them out because they will be more in depth than anything we can offer in a forum..

 

[johncassell]

Thanks very much for that cjard.

Much appreciated.