views:

82

answers:

3

Platform: Windows / Database: Access / Language: C++

Several years ago I had a Windows app that uses a database and I just installed an empty database as part of the install process. The database had all of the queries and macros I needed.

I am wondering what the current best practice for this sort of thing is: Do you build the database on first use and populate the macros etc or is the method I did a few years ago still an option. I have limited number of users (< 100) at about 10 locations.

A: 

Intranet web app, vpn for external users, sql server backend.

buckbova
But I dont want all the costs of running VPN and webservers. People are still using desktop apps right?
Skip
+2  A: 

Without knowing more about your requirements I can only recommend a general strategy of creating the DB from scripts during installation (preferred) or at first run (less preferred). If you're talking about a desktop app where each installation has it's own DB instance, then you can still get away with Access .mdb files -- although I would recommend trading up to SQL Express and creating a new DB from scripts. Using Access seems to bring a few headaches around distributing the right Jet/ACE drivers and avoiding DLL hell. SQL Express lets you stick to T-SQL instead of Access' pseudo-SQL, and will make it much easier if you do decide to migrate to a centralized server.

Matt
Thanks. I will definitely move to SQL Express. I wasn't sure just how to 'distribute' the black database. But it seems that building it from scripts is a good solution.
Skip
Another bonus with scripts is that you've got something text-based that you can check into source control, diff, etc.
Matt
I think version control is a major reason for doing this. Thanks once again.
Skip
Um, I'm not sure about this assertion of Jet/ACE drivers. If you use MDB format, you're golden on all platforms. It is only ACCDB format where you have to install the drivers yourself, but they are available for download separately, so this isn't a terribly big deal, as long as you can install them.
David-W-Fenton
We hit a snag last year on a project where we needed to lay down the new Office 2010 ACE x64 drivers in one installation scenario, and we were using .MDBs. It's possible that our analysis was incorrect, but it was troublesome nonetheless. So that's the headache I was referring to.
Matt
If you were using MDB, you didn't need the ACE drivers. I'm not clear what the deal is with Jet 4 support on 64-bit platforms, though. Indeed, I'm not clear on how Access 2007 and 2010 themselves intereact with MDBs. If you create an MDB in A2007 (haven't tried it A2010) the default DAO reference is 3.6, not the ACE version, so that would suggest that A2007 is not using the ACE to interact with MDBs, but Jet 4, instead. If that's the case, 64-bit A2010 would have to support 64-bit Jet 4 somehow. So, I have no answer, just more questions!
David-W-Fenton
+1  A: 

You should definitely check out SQL Server Compact, it's a robust database with a very small footprint