SQL Server database Implementation . . .

[hhm.2910]

Please tell me the ways to implement a VB program with SQL server database. Is it as simple as implementing MS Access Database ?
Do I need to install the SQL server on the client's PC ?
Is there any way to use the SQL database file place in the application directory without installing SQL server ?
The program I want to built is going to be used only be a single user. Please suggest whether I should user SQL Server of MS Access.

[RhinoBull]

Although MS Access is a nice db engine it lacks some critical functionality like stored procedures, triggers, etc and it's not free either.
You can create JET based database programmatically but you'll need Access installed when it comes to maintenance or perhaps to print report directly from it.
It might be a headach (to some degree) to migrate your MS Access later on to MS SQL... and so on.
On the other hand if you start with MS SQl 2008 Express you'll find yourself in much better situation.

So, my advise is: VB 2008 Express+MS SQL 2008 Express - both are free btw.

[si_the_geek]

In terms of your VB code, they are very similar. Exactly what differs depends on which version of VB you are using, and what database connection technology you are using.


You do need to install SQL Server, but it doesn't need to be complex.

There are several editions of SQL Server, and only the two free versions are appropriate for you (as the others cost a lot of money, need a server, and provide no extra benefit for this situation).

There is the Express Edition, which is basically a mini version of the usual SQL Server products, but is designed to be used on a normal PC by a limited amount of users. It supports features like Stored Procedures, but unfortunately does require extra installation (if you are using VB.Net this is apparently as simple as just ticking a box when making your ClickOnce package), and is reasonably large.

The other one of interest is the Compact Edition, which is far smaller (I think just 7 DLL's), but does not support as many features (eg: no Stored Procedures). In terms of installation you simply add the DLL's to your install package.

Is there any way to use the SQL database file place in the application directory without installing SQL server ?

You should never store data files in the application directory - you should use the directories designed for that purpose instead (such as AppData), otherwise you will get problems if the users Windows account is not an admin (or they are using Vista).

[dee-u]

If it is going to be a single user only then Access should be fine. SQL Server could be too much for such cases.

[GaryMazzone]

I tend to disagree... I have been moving away from any use of Access for a database backend for a couple of years now. I think SQL Exresss or SQL CE is the way to go.

[dee-u]

If it would have been in a multi-user environment then I would have suggested alternatives to Access but if it is a single-user application then Access should be fine in most cases, one scenario that I would advise against using it (in a single user case) is when the database should exceed the limit of its 2GB size.

[RhinoBull]

MS Access was fine when we had no alternatives - we now have free express editions of MS SQL, Oracle and of course MySql that make MS Access look "crippled".
Would you want your app to be crippled? I'm sure your answer is "no".

[dee-u]

We have our own beliefs and opinions on things. I'd say Access is still fine if it fits the requirement, and the application that utilizes it shouldn't be crippled if it is designed correctly. Cheers everyone!