junnz
asked on
Two Delphi clients accessed one MS Access database?
It is possible that two Delphi clients (two clients of the same application) from different workstations of a LAN network, access the same MS Access database located in a network drive?
If yes, what design issues need to be addressed?
If no, what is the alternative database solution (simple, inexpensive and efficient)?
If yes, what design issues need to be addressed?
If no, what is the alternative database solution (simple, inexpensive and efficient)?
I also have a few applications that run in a LAN and connect to a access mdb and they work just fine .
Alternatives may be: MySQL (there are mySQL components for delphi also - search for mycomponents on google)
The price is the same as for MSACCESS. MySql is faster than access, this is a plus too.
You could check Firebird also. As far as I know it is free and if you have experience with Interbase they are similar.
Alternatives may be: MySQL (there are mySQL components for delphi also - search for mycomponents on google)
The price is the same as for MSACCESS. MySql is faster than access, this is a plus too.
You could check Firebird also. As far as I know it is free and if you have experience with Interbase they are similar.
I would use ADO in that case, since ADO is more network-aware than the BDE. But yes, it is possible to have multiple clients accessing the same database.
But you might consider bringing things a bit further and develop your application more client-server like. Thus you'd create a server-module that will be located on the system that holds your Access database and then your client application would just have to connect to this server module to get whatever data it needs.
There are quite a few techniques to do this but if the server is running on W2K or XP-Pro, then either COM+ or the use of webservices would be the most interesting solutions.
The drawback of these client-server techniques, however, is the steep learning curve. But once you're familiar with it, the quality of your applications will improve. The clients, for example, would just have to know one thing, which is the name of the server. The server itself would be accessing the database and thus only the server module has to worry about the exact location of the database and e.g. the username and password that you need to access it.
But this is just how I would approach this, since I'm experienced with client-server software.
If you want to keep it simple then all you basically need to know for each client is where the database is located. But use the ADO components!
But you might consider bringing things a bit further and develop your application more client-server like. Thus you'd create a server-module that will be located on the system that holds your Access database and then your client application would just have to connect to this server module to get whatever data it needs.
There are quite a few techniques to do this but if the server is running on W2K or XP-Pro, then either COM+ or the use of webservices would be the most interesting solutions.
The drawback of these client-server techniques, however, is the steep learning curve. But once you're familiar with it, the quality of your applications will improve. The clients, for example, would just have to know one thing, which is the name of the server. The server itself would be accessing the database and thus only the server module has to worry about the exact location of the database and e.g. the username and password that you need to access it.
But this is just how I would approach this, since I'm experienced with client-server software.
If you want to keep it simple then all you basically need to know for each client is where the database is located. But use the ADO components!
ASKER
Thanks all of you. I think I have got the answer for the first question, which it is "Yes" to connect two users with a Access database.
I wish to get more detail info regarding to the design issues. I don't have experience with client-server software development and haven't done Delphi database application development for a long time, but wish to look at both options of the simple solution ( as Geo's suggestion) and client/server modules solution (as workshop_Alex's suggestion).
questions to Geo:
>select queries for reading data
use Adoquery component for queries?
>non data-bound controls in GUI
what are the controls used? How to connect to data?
> insert/update/delete queries in transactions...
how to achieve this transaction processes
questions to workshop_Alex
How can I come accross these client-server techniques learning curve? Any examples or references?
I have increased the Point to 200.
I wish to get more detail info regarding to the design issues. I don't have experience with client-server software development and haven't done Delphi database application development for a long time, but wish to look at both options of the simple solution ( as Geo's suggestion) and client/server modules solution (as workshop_Alex's suggestion).
questions to Geo:
>select queries for reading data
use Adoquery component for queries?
>non data-bound controls in GUI
what are the controls used? How to connect to data?
> insert/update/delete queries in transactions...
how to achieve this transaction processes
questions to workshop_Alex
How can I come accross these client-server techniques learning curve? Any examples or references?
I have increased the Point to 200.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Geo, I have done some tests, and your solutions make sense to me, thus I accept your answer. Just wondering can you help me with one more question?
Previously I had a local database programs using ADOTable component to fetching data from Access database. I tried to run two instances of this same program within a LAN and connected them to one Access database, the problem was when updated the ADOTable from one program instance (using ADOTable.insert, and post), the dbgrid tied with ADOTable in other program instance wasn't updated (after calling ADOTable.refresh). Why is that happened and how to do a real "refresh" on ADOTable to reflect on both instances?
Previously I had a local database programs using ADOTable component to fetching data from Access database. I tried to run two instances of this same program within a LAN and connected them to one Access database, the problem was when updated the ADOTable from one program instance (using ADOTable.insert, and post), the dbgrid tied with ADOTable in other program instance wasn't updated (after calling ADOTable.refresh). Why is that happened and how to do a real "refresh" on ADOTable to reflect on both instances?
My experience shows that users get confused and don't like such an automatic refresh much. They usually prefer to refresh their copy of the dataset manually (i.e. pressing a button on the form) instead. The reason is that when they are scrolling the grid and just have found a record of interest (but haven't positioned to it yet) and the program performs ADOTable1.Requery, they usually lose that record and have to search it again.
Use Requery method for ADO components instead of Refresh. Doing requery in one of the instances doesn't update the others, though. You have to call Requery in all instances if you want to refresh their datasets.
Regards, Geo
Use Requery method for ADO components instead of Refresh. Doing requery in one of the instances doesn't update the others, though. You have to call Requery in all instances if you want to refresh their datasets.
Regards, Geo
ASKER
Hi, Geo
Thanks for your answer. It is very helpful.
Thanks for your answer. It is very helpful.
- select queries for reading data,
- non data-bound controls in GUI,
- insert/update/delete queries in transactions for manipulating data.
There are many issues concerning database design also.
Regards, Geo