Delphi - Network ODBC Database (MS Access)

Question

Hello everyone.

I have a Database program which uses MS Access. The time has come and I need to make it a client/server app. The idea behind the scheme is: Server holds the database, and one or multiple clients need to access it simultaneously. The program will still need to operate under Ms Access (because porting DB and rewriting the app is out of the question).

Also another scheme which to be honest I don't really like is for the clients to request the DB file from the server, transfer it via LAN and access it locally... but Clients can make changes to it so it is ineffective.

Now my question is: Which is the proper way to implement this. And how to access the networked database? Make it shared, or is there any way using Indy and such.

Answer 1

Why would porting the DB necessarily involve rewriting the application?

How about using the SQL Server Migration Assistant to migrate your MS Access Database to SQL Server?

Answer 2

File-based databases like Access are inherently single user. You can share them, but you are opening yourself up to a world of corrupt databases and pain. If you have a multi-user application, you need a multi-user, server-based database.

Answer 3

If you are using database with Delphi's ODBC control, you don't need to worry about porting AP and DB(well, maybe some minor issue to fix). ODBC makes all DB using same interface, so you can simply change Data Source Name (DSN) connection string to a remote server with acc/pw to login, and then everything should work. Also it may work fine even if you change Access to other ODBC system, like MySQL, with just few minor SQL command changes.

So back to your question. Using file on server will definite fail when your user grows for larger than, well, 2. On multiple user accessing database simultaneously, you will need the transaction (or atomic) behavior in the DB side, which is lacked in Access. Only a real DB server like MySQL or MSSQL or Oracle/DB2/... can do that.

Don't share the single database file. Use a real Database server, unless you only have one client.

Answer 4

My solution: Use something like RemObjects SDK to define an interface that applications call to get the data from the database. You probably have a datamodule or something that already defines the calls needed, but it can help tidy your app. Then you can access the data remotely using your app, and still access the Access database. When it suits, you can migrate the data to something else and not have to change the clients at all as they are now using the interface and don't care about the implementation.

They also do a remote data access system, but I shy away from that as it doesn't fit my needs. May be worth a look though.

Answer 5

Sometime moving from one DB to another is very easy. I migrate from Access to Firebird very easily (I think Firebird is better than MySQL for Delphi)

The tools I use : http://www.clevercomponents.com/products/dbcomparer/dbcomparer.asp

Answer 6

To share an access database, just place the *.MDB file into a network share and open it across the network. I use this technique for up to around 15 network users, going beyond that or locations with a lot of traffic, I push to SQL Server.

Migration to SQLServer is a very easy task, especially if your using the dbGO components (ADO) as all that will be required is changing the connection string to point to the SQL Server instance. The only issue I had with such a conversion was with boolean fields. In access these fields have an internal representation of 0 or -1, and in SQL Server they have an internal representation of 0 or 1. In Access they resolve as boolean, in SQL Server they do not. For instance the following query fails in SQL Server:

SELECT * FROM TABLE WHERE BOOLFIELD

it has to be rewritten as:

SELECT * FROM TABLE WHERE BOOLFIELD <> 0

All of my other queries work properly...with the exception of date/time, which I suggest passing as parameters and letting the driver handle the translation.

The conversion process is fairly simple. Use the migration tool to migrate your access database to SQL Server, then use the SQL Server script database to generate a script for you. Either give this script to your customers to deploy, do it yourself at each location, or write a program to parse and execute each command as read from the file.

You can migrate data from one database to another via SQLServer using the OpenDataSource method and then performing an insert into from your access database.

INSERT INTO TABLE FROM 
  OpenDataSource('Microsoft.Jet.OLEDB.4.0','Access.mdb')...TABLE

Another option would be to go n-Tier. If you have the enterprise version of Delphi, then its not to hard to migrate the system over to a true n-Tier solution using TClientDatasets and writing a few server functions. Of course mileage will depend much on the current layout of your application, and how complex the system is.

Answer 7

If your users are distributed widely around the Internet and you don't want to use a file share, one option is to use EQL Data to keep multiple copies of the database in sync. It also helps publish the tables on the web.

Answer 8

Hello again people. I was checking MSSQL Server and both 2005 and 2008 required the .NET framework to work. What other database systems which are compatible with ADO are available (not file based DB)