Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Two Delphi clients accessed one MS Access database?

Posted on 2004-09-29
8
169 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Machine not responding during CopyFile() 3 104
Delphi 2 69
drawing animated level bar based on numbers 3 104
Adoquery sql  left join does not work 25 99
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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