I've got a Database program which utilizes MS Access. It is now time and I have to turn it into a client/server application. The concept behind the plan is: Server supports the database, and something or multiple clients have to can get on concurrently. This program will still have to operate under Ms Access (because porting DB and spinning the application is unthinkable).
Also another plan which to tell the truth I do not enjoy is perfect for the clients to request the DB file in the server, transfer it via LAN and can get on in your area... but Clients could make changes into it so it's ineffective.
Now my real question is: That is the best way to implement this. And just how to gain access to the networked database? Allow it to be shared, or perhaps is there in whatever way using Indianapolis and the like.
File-based databases like Access are naturally single user. You are able to share them, but you're opening yourself up to and including realm of corrupt databases and discomfort. For those who have a multi-user application, you'll need a multi-user, server-based database.
If you work with database with Delphi's ODBC control, you don't have to be worried about porting AP and DB(well, maybe some minor problem to repair). ODBC makes all DB using same interface, so that you can simply change Databases Title (DSN) connection string to some remote server with acc/pw to login, after which everything should work. Plus it may go fine even when you alter Use of other ODBC system, like MySQL, with only couple of minor SQL command changes.
So to your question. Using file on server will definite fail whenever your user develops for bigger than, well, 2. On multiple user being able to access database concurrently, you'll need the transaction (or atomic) behavior within the DB side, that is didn't have in Access. Merely a real DB server like MySQL or MSSQL or Oracle/DB2/... can perform that.
Don't share the only database file. Make use of a real Database server, unless of course you simply have one client.
To talk about an access database, just put the *.MDB file right into a network share and open it up over the network. I personally use this method for approximately around 15 network customers, going beyond that or locations with many different traffic, I push to SQL Server.
Migration to SQLServer is an extremely easy task, particularly if your while using dbGO components (ADO) as all that'll be needed is altering the bond string to suggest towards the SQL Server instance. The only real problem I'd with your a conversion was with boolean fields. In access these fields come with an internal representation of or -1, as well as in SQL Server they've an interior representation of or 1. In Access they resolve as boolean, in SQL Server they don't. For example the next query fails in SQL Server:
SELECT * FROM TABLE WHERE BOOLFIELD
it needs to be rewritten as:
SELECT * FROM TABLE WHERE BOOLFIELD <> 0
All my other queries work correctly...except for date/time, that we suggest passing as parameters and letting the motive force handle the translation.
Alteration is rather simple. Make use of the migration tool emigrate your access database to SQL Server, then make use of the SQL Server script database to develop a script for you personally. Either give this script for your clients to deploy, do-it-yourself each and every location, or write a course to parse and execute each command as read in the file.
You are able to migrate data in one database to a different via SQLServer while using OpenDataSource method after which carrying out an place into out of your access database.
INSERT INTO TABLE FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Access.mdb')...TABLE
Another option is to visit n-Tier. If you possess the enterprise version of Delphi, then it's to not difficult to migrate the machine to a genuine n-Tier solution using TClientDatasets and writing a couple of server functions. Obviously mileage will be based much around the current layout of the application, and just how complex the machine is.
Why would porting the DB always involve spinning the applying?
What about while using SQL Server Migration Assistant emigrate your MS Access Database to SQL Server?