I’ve been programming against Access databases under VB6 for a while, but since everyone is talking about moving to the latest and greatest of .NET, SQL, etc. I’ve been trying to learn these new technologies, however, I’m unsure if they’re right for me.
My database programming has been on a small scale, using databases for personal applications, for example, the way Adobe Photoshop Album uses an MDB file to keep track of all the tags and pictures in it’s database. Another common scenario where I utilize MDB files is when I have reasonably complex user settings that need to be saved and manipulated. I generally don’t program for large scale, multi-user, enterprise scenarios. I don’t have to worry about multiple users accessing the data, or heavy loads, or gigantic datasets, etc.
Considering that fact, is it worth it for me to switch over
to using SQL databases I’m finding that the learning curve is quite steep and although
programming using that model may have advantages, they don’t seem to me to be compelling
for my needs. Additionally, one major drawback that I’m finding of switching
over to SQL is the complexity that it adds to my deployment and debugging. From
what I understand I can’t just distribute my exe and database file to be dumped
into a folder, but the user has to install a whole server on their system, and
the database has to be installed for that instance, and I need to concern
myself with instances, access permissions, security issues, etc. Sounds like more
trouble than its worth! And the user is often annoyed that there’s now another
major service constantly running in the background of their system (affecting
its performance), even when the app that needed it is not running. Another
drawback: currently, using Access files, if the user of my app is having problems,
I can have them just email me the mdb file so I can see what’s going on and
program against the same scenario they may be having, but from what I
understand, using SQL server, that’s a much more complicated process, to
extract the SQL database, and transport it to someone else who will then have
to insert into their server, etc. I may not be understanding the details
exactly right, but if it’s something like that, it just doesn’t seem to be
worth the trouble.
Any SQL evangelists out there than can help me understand
the situation better, explain how I’m misunderstanding the technology and why these
aren’t really problems, etc. Is it possible that SQL just might not be right
for me
using SQL for small apps
Roger Cheng - MSFT
strDisplayName
If you're using databases on a small, single user scale, then you may well be better off with MDB databases, rather than having to set up SQL Server.
You're right about all the drawbacks you mention.
Personally, I would never use Access, because it's never been a 'real' database. I believe stored proc support exists now, but it's too late, IMO. However, if you're happy using it, it sure sounds like the easiest solution to your problems. You should never upgrade just because something is 'new' or people told you it was 'cool'. If Access meets your needs, then you should stick with it until something else provides a compelling case to move.
John.Padilla
StevenFowler
SQL Server is definately better to code against. I distribute apps with MSDE, it's not really a big deal. I certainly can't easily get clients to send me a copy of their database, however. Instead, I have reporting built in that tells me most of the info in the database, if I need it.