MSSQL Database opposed to MS Access

[cjohnson]

I am write a lot of VB.net programs using MS Access, but a lot of people don't like MS Access, and it is somewhat limited, so I am trying to learn to use MSSQL, actually, MSSQL Express. I have created a sample application, and it seems to work much like Access. My question is, what are the benefits of using MSSQL for standalone apps for private use or on a Network, and what are the limitations of the express edition.

Appreciate any help,
Chris

 

[Arg81]

Think I answered this for someone before, did a quick search and found this - shows the limitations of SQL-EXP
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1082707,00.html

To be honest, I'd say for standalone apps there really isn't much difference, that's my personal opinion although I'm no expert, no doubt someone else will answer disagreeing with me

 

[JohnH]

MSAccess is file-based, MSSQL is server-based. For a standalone machine there shouldn't be much performance difference, but the SQL solution is better integrated for Visual Studio development and provide better scalability for future application/architecture changes.
Check this SQL Express overview.

 

[cjohnson]

Thanks very much. The links were a big help. I personally prefer Access, and think I will stick with that as long as possible. The express version eliminates the benefits of moving from Access - such as simulataneous users.

Thanks,
Chris

 

[Administrator]

MSAccess is file-based, MSSQL is server-based. For a standalone machine there shouldn't be much performance difference, but the SQL solution is better integrated for Visual Studio development and provide better scalability for future application/architecture changes.
Check this SQL Express overview.

They are all "file based" it's more of an "in-process" vs. "out of process" that is the difference.

 

[JohnH]

Yes, both local applications and servers use files for storage, but there is a difference using a server or not using a server. The service that is SQL Server and doing the direct database access should be better suited for these operations both for a single and multiple consumers than the standalone, even though it absolutely is scaled towards multi-use. The SQL Express is by default configured and optimized for standalone machines and infrequent use, but it doesn't take much to enable it for local network, or scale up to full version and big networks.

 

[cjard]

One thing that always amazed me, was that Jet contained some limited network server-like facilities, whereby if you were using an MDB on a remote machine, then your local Jet engine would contact the remote Jet drivers and ask them to run e.g. a query, returning only the smaller result set rather than drag the entire MDB across the network to you machine just so your local Jet drivers could perform the query on it. Cool.