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.

MSDE vs Access as a dB Solution

I'm a hobbyist using D7Pro developing trivial (as in not all that complex - I do use some 3rd party tools) desktop apps that use Access for storage. A couple of them rely on sever stored data (Access) in order to allow multiple users - which happens rarely but could be up to 3 to 5 at one time. It's typical for every machine to have one version or another of Access on it. I'm thinking of changing the data back-end to MSDE. Primarily to improve performance and reliability but also to provide extensibility using SQL Server should the need ever arise. As it sits Access is the only database I'm relatively familiar with.
Q1) Why is this move to MSDE a good/bad idea?

We have an issue that I think using a Briefcase model would be best to resolve - for my users. In addition to the apparent speed issue (except when updating/checking for concurrency), on occasion the servers are unaccessible and will be down for extended periods (they're on ships)
while awaiting support. I'd like to have the local databases getting/saving changes to/from the copy on the server either on run/close, periodic checking the server, and/or by specific instruction (a button) by the user. I've had a look at implementation of this with Access and am not really keen on it - basically I'm afraid of blowing up the users dB :)
Q2) Is MSDE the better choice for implementing this?
Q3) Do you know of a good tutorial that would help me implement this specific model?
Q4) Is there an alternative (free/lite) dB/model that I should be looking at?

Yea, that's a lot of questions but ...

Thanks, in advance, for your time.
PNII Send private email
Saturday, November 12, 2005
There is Firebird. It works good and it's free.
Saturday, November 12, 2005
I was under the impression that  d7Pro was for use with Borland’s data engine, and not JET?

Anyway, lets just leave the above alone here, but it is not clear if you are talking about ms-access, or the JET data engine.

>It's typical for every machine to have one version or another of Access on it.

Well, if you trying to move to the MSDE, then your above problem of different versions of ms-access still is not solved, and still does not change anything in regards to users having different versions of ms-access. I going to tell you straight right now that you can not, and should not entertain the idea that your users will have different versions of ms-access for a application that you develop.

The only time I allow my software to run on different versions of ms-access for a client is when they are upgrading their software. (you can have the same back end database, but deploy DIFFERENT versions of the Front end to each computer. This allows you to run access 97, and access 2003 for example (mixed versions) in a company as they upgrade, and they can still use the back end data in multi user mode. In fact, this approach works VERY well, but you do want to "harmonize" the setups to reduce support costs. (when you track down a problem, it better be a solution for ALL computers…not just one, or you can get your self in a support situation where 8 computers can cost you more to run in a year then a company with 30 computers).

It is not 100% clear here if your applications(s) use a JET mdb file, or fact you rely on ms-access here also? (remember, ms-access is not the database, but only a tool like VB, or c++ that lets you write code, build reports or whatever. The data can be JET, or Oracle, or MSDE.

So,  you might want to clarify what role “JET” plays here, and also what role that ms-access plays here? (the last 3 versions of ms-access have used JET 4, and a copy of JET 4 ships with every copy of windows xp. So, what I am saying is that the last 3 versions of ms-access essentially use the same version of JET. And, the last 3 versions of ms-access actually default to a2000 file format (this is just to stop the madness of every new office suite having different file formats).

So, I going to let you clarify the above issues.

Note that if you are not in a upgrade environment, then you got to pick a version of ms-access, and restrict the users choices to that version.  (perhaps I would support TWO versions, but don’t bet on it.).  In fact, if you really wan to get a handle on this, you should force them to one version, or in fact deploy your application via the runtime, and thus you don’t care if they don’t have the correct version, as you just include that with your runtime. For sake of support, and deployment, having ONE version is much nicer (for example, I have a “upgrade” button to deploy new versions to the user via a web site, and this makes updates really easy. However, if users got a mixed environment, then this approach is not very clean at all.

>Q1) Why is this move to MSDE a good/bad idea?

We don’t know the above answer yet (as I mentioned, you need to clear up what you use ms-access for, and what you use JET for).

However, on a single pc in single user mode, I can’t say that using MSDE is going to solve much, if any problems for you.

>Q2) Is MSDE the better choice for implementing this?

I assume you are talking about replication. JET does support replication, and thus allows “off line” use of the application. You bring it back to the network, connect, and then “sync” with the master replica.  You can use the MSDE to do this also, but I don’t know if you want the trouble, and extra effort to run the MSDE on each computer.

My spider sense says you don’t need, nor want the MSDE on each machine…but it is a possible solution, but I can’t say it is better right now. Not knowing the load, and dataset sizes of the resulting tables on the server side is a again a big issue, and one that would tend to support using a real database server at least on the “server” side, and perhaps keeping data local via JET.

Are you writing software in ms-access, or just storing data in a mdb file..and using JET (which means you don’t even have to install ms-access on any of your machines). What role is ms-access playing here?

Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal Send private email
Sunday, November 13, 2005

My apologies, I didn't figure that mentioning that the machines typically have MS-Access installed would be taken to mean anything other than "JET is typically installed - does adding/distributing another dB solution make sense?" MS-Access has little if anything to do with the post other than that. I could have just as easily used WinXP as indication. The distributed and help file contain info on downloading JET and MDAC for those that need it ... hmmm, this may be enough (other than the fact that both of the above should exist on any XP box) to move to MSDE.

The program needs Windows, JET and MDAC to run. Yes it produces an MDB file.

Yea, replication there has to be a better solution ... perhaps something using XML, not sure at the moment but it's not replication. Replication, would seem to be, brain surgery as a solution for a headache but then I'm not using it what do I know.

Many thanks for taking the time ...
PNII Send private email
Sunday, November 13, 2005
Two things you need for d7:

1.) embedded firebird, on dll for install, enterprise scalability built in if you want to move up.

2.) IBObjects, at, very useful.

Regarding firebird: You will be glad you moved. Very scalable both in embedded version and server version, same file format for single user embedded and multi user server version.

Sunday, November 13, 2005
Hi "      ", (sp?)

I've looked at that, guess I should look again. I may be upgrading to D2006 and moving to D.Net so it may all be moot. Still doing the research.

PNII Send private email
Sunday, November 13, 2005
>The program needs Windows, JET

Excellent, it is most important clarify this, and I am glad I asked, and you cleared this up (a lot troubles for some companies are their mix of ms-access, and this is NOT your problem). So, for all sake, ms-access is a complete non issue here…Good!!!

Since you are coding this stuff, then perhaps you continue to use JET on each computer (less code/work to change what works now), and then consider some type of data store on the server side. The trick (and likely your reason for question(s) ) is to come up with a way to “sync” the data to the server.

You can roll your own based on some type of time stamp field. (not that hard). It really depends on “which” way the data needs to move, and how often you need to do this.
It also depends on how many users, and what kind of connection those users do/will have to the data store.

Regardless, you do need to design for standalone, disconnected operation. When you have this requirement, and the data still need to be accessible by all others, then I think replication is better then rolling your own. Too bad JET does not sync with sql server!!

As mentioned, it also depends on how many tables, and how complex the application is. If it is rather simple (only a few tables), then you can simply write some code to “update”  and “download” the server data when they connect. If you got complex data model, then this may not work for you..

Moving to a data engine that lets you replicate to the “main” server is a good solution here, but you got a existing application that works, so you can’t re-design from the ground up here.

Running a copy of sql server on each machine is possible, but it kind of a pain too (when your application starts, you need to startup sql server (MSDE) in this case. And, when you exit your application, you have to shut down the server. And, even simple things like making a copy, or back of the files requires that you stop the server, and detach it from the data file while you make a backup. And, worse, with a server based system, you don’t have ONE SIMPLE file like you do with JET.

I think only you can answer some of the above questions based on your particular situation, but my first approach would be to consider moving the back end data to server based system, and writing some code to “update” the server with new data from each client. If the data/application is complex, then you might just have to bite the bullet, and start using a server based engine on EACH computer.

If the load/volume of data is low, then I would still consider replication to a JET file on the server.

Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal Send private email
Sunday, November 13, 2005

Nice response, lots of food for thought. In retrospect it appears that I've left out another significant issue; the program in question is scheduled to be rewritten in D7 (actually I may hold out for D2006 and .Net) it currently only exists in VB6. It desperately needs a UI/Logic update and that's why the thoughts of change. No, VB6 is not where I'm going today :)

I was thinking of reworking the data structure to more readily accommodate the controls I have (or rather had) in mind. Now that I'm thinking along the lines of moving to D2006 and .Net, oddly enough, newer versions of the controls I've used in VB6 will be available in the newer Delphi IDE.

Having a look at the briefcase model using replication may deserve another look even though I'm expecting better prospects with D2006. I like MDB files, most likely because they're all I've ever really worked with other than brief encounters with Paradox and dBase. They are just so fragile, specifically where replication is involved, (at least that's my understanding) that it sometimes is a little spooky when you push them too far. As I said I'll have another look once I settle into the new IDE and see what I have to work with there.

The dB in question is as normalised as I can possibly make it, contains eleven data/program specific tables (five of them look-ups) and a limited number of program generated tables - probably not more that thirty at most. There are only one or two of the tables that get changed with any frequency and all the program generated ones are local in nature and can be ignored with regards to replication/syncronising.

We'll see where it goes, thanks for the discussion - it's helped quite a bit.
PNII Send private email
Monday, November 14, 2005
I'm currently working on an application which will have the same issue. I will have a jet db on clients to let them work disconnected and I will have a backend server to consolidate (SQL server or jet).
I'm facing now a logical problem about how to sync the db's as many users, while disconnected might update the same records. So, what update takes precedence? The most recent? The Bosse's update?
So far I think I shall not rely on replicas.
Any suggestion?
Sevenoaks Send private email
Monday, November 14, 2005

This is where the *real* fun begins. Data collisions and what do you do? How often do you go to the server to check for updates? How often do you post local updates? ... There's a million of them. Time for me to augment the library I think.

GL Have fun.
PNII Send private email
Monday, November 14, 2005

We've too had the same issue with replication and this can be quite a bear to figure out and code, as you must have a store and forward mentality. 

Getting the replication scheme right is going to be key in your situation, where the ships are offline and need to replicate on a sporadic basis.  With ship communications, you should be coding replication based on the premise that the average link is going to be at 9600 baud (hard to believe in this day and age, but this is what you'll see as common with shipboard communications).  Using a briefcase model is likely to kill any users as the cost will be too high and the client will nix the use after getting their first communications bill. 

Also keep in mind that you will be at the mercy of the communications provider on board the vessel, which in practical terms means that replication will have to either occur via email or via straight file transfer (on their terms).  You can forget about using something like web services or MSMQ to shuttle data back and forth.

Back to your original question however.  Although there are benefits to using JET, real consideration need be given to MSDE/ SQL 2005 Express, especially the latter, as the inclusion of managed code SP could help tremendously in the custom replication scheme which you would need to code to make the ship/shore talk.  This of course, combined with the robustness of MSDE and all of the features which it offers above JET. 

As for the installation of MSDE, if done right, this is painless.  You can write a small piece of code which would self configure the server upon first start of your application.  For the client software, the first time it starts, it just simply asks for the name of the machine hosting the server, and your code could then self configure and test the connection.

Some people complain about having a database service up and running, bringing it down to copy/backup etc...  Most likely, if you put together a custom replication scheme, you'll need to have the DB available all of the time anyway and any contingencies that you need to plan for can be done via SQLDMO anyway.  We've had ours running for months without any problem. 

As for backups, probably not a good idea to do so onboard, especially if you have a replication scheme running, as you'd end up out of sequence and with conflicting replication messages should they restore.  The features in the application should be robust enough to keep the sailors from wanting to restore.  This, combined with the replication challenges, is easier said than done.

Will probably get flamed for my comments, but we must remember that the shipboard world is completely different than the connected world.  Unfortunately, the big DB providers don't have a replication scheme that fits within the model that is needed here, which is why a custom scheme would be needed that would deal with the size/frequency limitations, selected tables, variable recepients as well as the potential conflicts in data which may come up. 

Without spending two days writing about the potential data conflicts which occur, all I'll say on this is that these must be minimized during the design of the database/workflow and any conflicts which do arise must be handled by replication in a known and consistent fashion.  Please don't think of replication as an additional feature, it is an integral part of the database design process and must be treated as such. 

Of course, if you say that you only want one way replication, forget about a complicated custom replication scheme described above, there are different rules for this, but the basic challenges remain the same.

Michael Johnson Send private email
Monday, November 14, 2005

Thanks for the reply and the discussion. It's a little easier for me in that replication off the vessel isn't where I'm going - thank <insert deity of choice> for that. The reason is typically that once the onboard servers go down, unless the 2nd officer (hey, not my choice) can manage to coax them/it back to life, we're done until we get back to a port where we can get IT support. Obviously if you've locked up all your current data on the server, you're pretty much done. We're kinda like the real world in a self contained sort of way.

The way we're attacking it so far is to maintain daily backups from the server - it goes down, we switch to the local database then overwrite the server version when we get it back - try that over the phone (I don't work on all the ships at once) with someone who still uses the paperclip to get around in MS-Word. Admittedly it's not quite that bad.

Actually replication with shore, if we needed to, wouldn't be all that bad provided you could schedule it for an instance when we're in one of the three coastal ports where we've set up broadband wireless - any other time it's sat-phone/auto-tel at ~$5/min. One of the advantages of just being a coastal service.

As to the design considerations for replication, excellent point it'll have to be a founding element in the design/rework. No, it's has to be full - I want the users accessing a local database but I want them thinking that they're using the server when it comes to visibility of changes.

When I originally built this project advanced (at least to me) dB consideration wasn't much more than normalization of the tables and storing parameter queries in the dB for use by the program. Well it was all that was needed at that point. One of the most recent features I've used (although with a different app) is allowing users to build their own queries in the program and save them to the database (or not) for future use. It would seem, I've a long way to go yet :)

I sure appreciate your comments, thanks.
PNII Send private email
Monday, November 14, 2005
You should switch to MSDE for any multi-user DB. There are many examples of source code that will give you control over the settings, essentially turning it into a full SQL Server.

Installing MSDE on the client computer is another thing. There are issues with installing and using MSDE where computers are 'locked down'.  If you can get past that issue, then I would recommend MSDE there as well for any 'mobile' users (that's what we call them in our products).

You may consider reversing your model. Have the land-based users work off of a single main server, and the ship-based users are the 'mobile' users.  You have a consolidated land-based system of all the ships and each ship would sync only its segment of data with the main server. This way you are only maintaining one database, the one on the main land-based server. This is basically the model we use for thousands of clients with our product Office Accelerator (see
Ralph Send private email
Friday, November 18, 2005

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

Other recent topics Other recent topics
Powered by FogBugz