Solved

Two Delphi clients accessed one MS Access database?

Posted on 2004-09-29
8
160 Views
Last Modified: 2010-04-05
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)?
0
Comment
Question by:junnz
8 Comments
 
LVL 17

Expert Comment

by:geobul
ID: 12187644
Yes, it's possible. I, personally, use:
- 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
0
 
LVL 11

Expert Comment

by:calinutz
ID: 12188083
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.
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12188626
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!
0
 

Author Comment

by:junnz
ID: 12195062
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Accepted Solution

by:
geobul earned 200 total points
ID: 12198106
Hi,

I also recommend (and use) ADO when connectiong to MSAAccess db as others said.

For instance, our users should be able to view and manipulate some data (from several db tables):

1. getting and showing a recordset:
- ADOQuery with SELECT statement(s) for fetching data from the db;
- some kind of grid just for viewing the recordset and selecting records (dbgrid, dbctrlgrid, listview, etc.);
- buttons for actions on the selected record (delete, modify and AddNew, of course);

2. manipulationg data:
When an action is selected I usually open a new form where there is only one record shown (either new or the selected one). I usually use TEdit, TMemo, TCheckBox, etc. (not databound controls like TDBEdit, TDBMemo ...) here filling their values from the selected db record (or empty for new ones). This is my personal choice and I don't say that this is the best way. It isn't the easiest way also because it requires more code I have to write. I think you should start with TDBxxx controls first.

The user fills the form and on OK (Save) button click I first check whether everything is filled in correctly (all mandatory fields are not empty, some data like numbers are numbers, etc.) and then start a transaction where new data is being written to the db:

        // start transaction
        dm.Con.BeginTrans; // 'Con' is ADOConnection component
        try
          // one or several INSERT/UPDATE/DELETE SQL statements using ADOQuery
          with dm.qry do begin // 'qry' is an ADOQuery component attached to 'Con'
            Close;
            SQL.Clear;
            SQL.Add('UPDATE his SET hi_ref = '+QuotedStr(edToRef.Text));
            SQL.Add(', hi_file = ' + QuotedStr(edToNumber.Text));
            SQL.Add('WHERE hi_ref = '+QuotedStr(FromRef));
            SQL.Add('AND hi_file = '+QuotedStr(FromNumber));
            ExecSQL;
          end;
          // etc.
     
          dm.Con.CommitTrans; // apply the changes
        except
          dm.Con.RollbackTrans; // roll back everything - something went wrong
          Alert('MoveHistoryEvent failed. From:QUE'+sQueNumber+' To:'+sRef); // log file entry
          ShowMessage('Operation failed !');
        end;
        // end of transaction

The second form is closed and datasets on the first form are refreshed.

I must also say that I always set the following ADOQuery/ADOConnection property CursorLocation to clUseServer.

Regards, Geo
0
 

Author Comment

by:junnz
ID: 12213653
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?
0
 
LVL 17

Expert Comment

by:geobul
ID: 12215225
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
0
 

Author Comment

by:junnz
ID: 12230872
Hi, Geo

Thanks for your answer. It is very helpful.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now