The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

64 bit replacement for Access / Jet / DAO

I have a 32 bit application that uses DAO to connect to an Access database. I need to upgrade the application to be true 64 bit (not WOW64). Microsoft does not offer a 64 bit version of DAO.

The features I really liked about the Access database were that it was well-supported in Windows, it allowed multiple users to connect simultaneously (though the nature of our application is that it was never more than five or so at a time), and it was serverless.

I have found two potential replacements:

1. SQLite. Pros: serverless. Cons: The documentation warns that having multiple users connect to a database over a network could result in corruption. Does anyone have any experience with this?

2. Firebird. Pros: Appears to be more robust than SQLite. Can work as an embedded database for single users. Cons: Requires a database server to be used for multiple users to connect to a file over a network.

I am not considering SQL Server Compact or SQL Server Express. Microsoft is too flightly with their database technologies for me to take these seriously.

Are there any databases that I missed that I should be considering? Does anyone have any specific experiences with either SQLite or Firebird that would favor one or the other? Are my descriptions of SQLite and Firebird correct?
David Send private email
Friday, May 16, 2008
 
 
Flighty?
anomalous
Friday, May 16, 2008
 
 
I'm confused that you say "Microsoft is too flightly with their database technologies for me to take these seriously.", yet you are happy with Access (http://msdn.microsoft.com/en-us/library/aa167840(office.11).aspx)

I'd recommend SQL Server every time over Access for even small projects.  The engine is robust, easy to tune and capable of scaling to very large databases.  It supports all standard SQL features, like triggers, user-defined functions and stored procedures and can even host CLR functions if your architecture requires that.
Cade Roux Send private email
Friday, May 16, 2008
 
 
What I mean by "flightly" is they can't commit to a database technology. Joel said it better than I can in his Inc article:

"(Microsoft's) technological cover fire has included no fewer than eight different 'official' ways to get data out of a database. (For those of you keeping score at home, they were DbLib, ODBC, RDO, DAO, ADO, OLEDB, ADO.NET, and LINQ -- and I'm sure I've missed some others.)"
http://www.inc.com/magazine/20080401/how-hard-could-it-be-fire-and-motion.html

They've also recently replaced MSDE with SQL Server Express.

If I could use 64 bit DAO, I would do it without a second thought. Microsoft is forcing me to switch database engines, so I don't really see any reason to stick with them.
David Send private email
Friday, May 16, 2008
 
 
just to clear one thing up, the problems with sharing sqlite data over a network have nothing to do with sqlite and everything to do with the network. if you're doing file-based data access (including that used by ms access/jet) you'll run into this problem regardless of what you use. sqlite is no more likely to corrupt than access to the bugs in the network o/s locking mechanisms.
-don
Don Dickinson Send private email
Friday, May 16, 2008
 
 
You can still connect to SQL Server with DAO.  I wouldn't recommend it, but you can.  My current preferred connectivity is through ADO.NET with a Native SQL OLE DB provider.  Changing the connection mechanism has not meant a lot of changes for the architecture of my database designs.

SQL Server Express is simply MSDE 2005.  It's basically the same thing with the 2005 engine instead of the 2000 engine.

All versions of SQL Server from CE to Enterprise have the same core functionality and you connect the same ways.  You can easily move from SQL Server Developer Edition to Express to Enterprise as long as you are not dealing with clustering or any of the very specific features (attaching file databases in Express, too) or coming up against overall size limits.

When you are dealing with SQL Server, you have a full server-based RDBMS, and, althought network connectivity is important, it should not compromise the ACID in the database engine, whereas with Access or anything file-based over the network, you're SOL.

I'm not pushing you to use SQL Server, but just pointing out some corrections to your assumptions and that it has been very stable for over the past 10 years, with very few fundamental differences since 7.0 which would affect your front ends.  Lots of things you might want to take advantage of, but nothing at what I would call the core level of most applications.  You haven't told us much about your application, but since it's multi-user, it sounds like it has a strong CRUD element.  SQL Server may not be apropriate for all kinds of scenarios where even flat files might be more appropropriate.

Also, since we're talking about 64-bit and databases, I have to wonder what the importance of native 64-bit is - is this a heavy processing application, or requiring some very large database activity.  Because SQL Server is a client-server RDBMS, you can easily have 32-bit clients connecting to a high performance 64-bit SQL Server and taking advantage of the SQL Server's power without worrying at all about client horsepower.

In my daily experience, even an eight-way 64-bit SQL Server (Enterprise on Enterprise x64, 8-way Unisys, but not sure if it's 8xsingles, 2xquads or 4xduals) is only going to perform marginally better in small scenarios where a one-way or two-way 32-bit SQL Server desktop (Developer on XP Pro, Dual Core) is already adequate.  Your memory, disk array and database design are going to a have a bigger impact.
Cade Roux Send private email
Friday, May 16, 2008
 
 
Don: Yes, the SQLite corruption issue is a network / OS problem, not one with the database. We've had relatively few problems with Access, though they do happen occasionally. Have you (or anyone else) ever tried running SQLite over a Windows network with multiple users? If it's truly as robust as Access, that would be good enough.

Cade: I honestly tried to include enough information to avoid the "You haven't told us enough" comment, without writing a novel. Sorry about that. I'll try to expand.

Our software is an add-on for AutoCAD. The 64-bit version of AutoCAD is native 64-bit. For our program to work with AutoCAD, we have to also be 64-bit. The only thing driving the 64-bit conversion is compatibility with AutoCAD. There is nothing about our program that requires 64-bits. You are completely right that it is overkill, but it is unfortunately necessary.

The application is multi-user, but not many. In most cases, there is only one person connected. A large number of users working on a single database would be five. The software is used to design HVAC and electrical systems for buildings. Each building gets a separate database plus a number of associated AutoCAD drawings. The database and the drawings get moved around the file system a lot. Using Access, this isn't a problem. I imagine this will get a little more complicated going with a client-server RDBMS (SQL Server or Firebird).

And yes, I have been a little dramatic regarding Microsoft's databases. That's just my frustration at the fact that the one solution I would love--upgrade Jet to 64-bit--they've decided not to do.
David Send private email
Friday, May 16, 2008
 
 
sqlite is at least, if not more robust than access. but, neither is particularly better over a network. i've supported several ms access-based solutions over the years ... one in particular corrupted once every few months. always, the data was recoverable with a compact (or whatever) command from within the access environment. my 3 production sqlite applications have never corrupted. as far as i know ms access does not have true acid transactions (could be wrong, but i don't think so), so sqlite is probably a little more stable. if you're really, really concerned about network issues, then use mssql server or another client-server technology.

best regards,
don
Don Dickinson Send private email
Friday, May 16, 2008
 
 
SQLite is better at self-healing. I wrote an app for a university using Jet 3.5, and while Access supported transactions, commit/rollback etc., they only work while Access is running. If Access crashes, it will not self-heal.

SQLite, with its journal file, will self-heal when the power comes back on.

To me, that makes SQLite a more robust database.

In the 5 years I was involved with the university, the vast majority of database problems were related to the network/windows, not Access. For the most part, Access was very solid, and we had 25 users on it.

I was hoping MS would rewrite "Microsoft Jet Database Engine Programmer's Guide" for Jet 4, and it appears they were going to (based on Amazon.com pre-release information about the book), but decided against it in favor of MSDE.

Jet 4 is great and has a place, especially for local databases. And if you need reports and scripting, you can use the full power of Access to manipulate your Jet database.

But SQLite can be compiled right into your EXE, which in the end gives it a certain advantage.
anony
Friday, May 16, 2008
 
 
Wow, choosing Access over SQL Server because Microsoft is too 'flighty'.  It doesn't get much more backward than that.  He was including Access as one of his '8 ways'.  Access has serious concurrency problems- you've been very lucky that you haven't had major corruption issues.
Joel Coehoorn Send private email
Friday, May 16, 2008
 
 
For what it's worth, everytime SQLite issues over a network are mentioned in the SQLite newsgroup, NFS file locking is always specifically mentioned.  That's not a guarantee that Windows networking always does the correct file locking, but I haven't heard of any issues in the couple of years I've been following the newsgroup.

I moved from Access to SQLite a few years back and have never regretted it.  I don't have multi-user, but I do often have two processes (and multiple threads) accessing the database.  That used to mess Access up, but it's never happened with SQLite.  (Note that I always had both the Access and SQLite databases on the local machine).

HTH
Doug
Saturday, May 17, 2008
 
 
OP, can you split your code into 2 parts, a 64 bit
part to interface with Autocad, and a 32 bit part
to interface with Access, and use eg COM calls to
connect them together?
Object Hater
Saturday, May 17, 2008
 
 
What's so special about 64 bits anyway?

I mean, a string is a 'string' of 8-bit bytes -- okay, 16 or 32 bit unicode values.  64 bits isn't helping there very much.
AllanL5
Saturday, May 17, 2008
 
 
Correct me if I'm wrong, but I'm pretty sure you can use ADO to connect to an Access DB from 64 bit code.

DAO was deprecated so long ago that it not having 64 bit support is not unreasonable - especially given that it is not at all difficult to port DAO code to ADO.
Anna-Jayne Metcalfe Send private email
Sunday, May 18, 2008
 
 
Thanks to everyone for their replies.

ADO exists for 64 bit, but there is no driver to connect to an Access database using it:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=479480&SiteID=1

I could split my code into two parts, but with the way the code is written, that would require almost as much work as my other options. I figure this is as good a chance as any to make a change to something that currently supporter.

I agree, flightly was a bad choice of words to describe Microsoft's databases. But I still won't use SQL Server.

I've apparently been very lucky with Access over the last seven years. I'm still debating whether to choose if I prefer to be lucky (SQLite over a network) or good (Firebird).
David Send private email
Monday, May 19, 2008
 
 
In my experience with Firebird and SQLite on a client app a couple years back, generally speaking, the tooling support for SQLite was far stronger--which for us at the time was huge from a productivity standpoint.  So we went that way.  I've no idea where things stand today, probably closer to 4 to 5 years later now that I think about it... but that could be another vector to consider when deciding upon the two.
James Nguyen
Tuesday, May 20, 2008
 
 
I would choose firebird
Here are the details why is better

http://www.firebirdnews.org/?p=1445
also firebird gives you more features : triggers , stored procedures , full transactional engine (no crippleware)
and the most important is multiple threads can work and access one database , no locking on it
Popa Marius Adrian Send private email
Wednesday, May 21, 2008
 
 
I would also second the suggestion for Firebird.  The true free database aspect with no restrictions (free as in beer) are critical to us, along with the fact that I can develop one application against one database schema and deploy as either embedded vs. a 'real' server that is still $0 cost to a customer.  Its an ideal way, for example, to develop an application and give it away for trialware in single user mode by just providing Firebird embedded (a very small DLL BTW, under 2mb in total size).  But if a customer wants to upgrade the software to multi-user, they just install FB server editions (again $0 cost) and point the application to that. 

All without having to suffer without stored procedures, triggers, views, etc.  Since Firebird is the open source port of Interbase, it has a lot of great history & maturity, and its totally cross platform so you can offer your customers deployments on Windows, Linux, Sun Solaris, Mac OSX, etc.  The FB community are awesome for support (found mainly on Yahoo Groups) and its also a damn solid database for web applications (ie. PHP Interbase libraries are rock solid).

As for admin tools, I use IBExpert and love it.  Its rich, gives me the ability to debug stored procedures, etc.

The best thing to do here is to create a list of your requirements and rate all options against them.  We did this, and Firebird (still to this day) ranks highest amongst all options.  The only other one that came close was PostgreSQL, but PGSQL didn't really make it easy to deploy a Windows trialware single user install of our software, so we dismissed it for that reason.

Good luck in your hunting!

Myles
Myles Wakeham Send private email
Wednesday, May 21, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz