delay in retrieving records from a databese

Hi, experts,

Delphi 7 Enterprise, MS-Access XP, TADOConnection -> TADOQuery -> SQL-statements for "insert" and "select" :  

1. A record written by a Delphi-program into a table of a access-database using sql-statement
   (TADOQuery.SQL.Clear;  TADOQuery.SQL.Add('insert ...');  TADOQuery.ExecSQL)

2. Looking into the access-database directly (using access)  ->  new record is visible.

3. Then in the same delphi-program a sql-query is started:   It will not give back this last record entered !!!
   (TADOQuery.SQL.Clear;  TADOQuery.SQL.Add('select ...');  TADOQuery.Open;  loop until eof)

4. All records written before this last one are recovered by the "select" (3.).

5. Same thing when the next record is put into the table. The very last record cannot be retrieved, all the others are.      and so on, and so on ...

A Close or deactivation of the ADOQuery before putting the new "select" does not change anything.

What can I do in order to be able to read the last record immidiately after entered ?  Where is it hidden - obviously it  _is_   put into the table successfully (see 2.)  ?

Thanks,
K.-P. Becker
KPBeckerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
how is your cursorlocation is set by the TADOConnection?

maybe if clClient it hleps to set it to clServer

meikl ;-)
KPBeckerAuthor Commented:
Hi, Meikl,

thank you for your reply - unfortunately "may not be".

The cursorlocation indeed was set to clUseClient but setting it to clUseServer made no difference.

I tried to add a "TADOQuery.Last" but the query obviously will not bring back this last record from the server. So this is no solution , too.
When the program is shut down and started again the "query takes it all".

Probably another hint ?

K.-P. Becker

Kyle FosterCEOCommented:
This sounds like an ADO issue.  I would first install the latest version of MDAC and then try it again.

http://www.microsoft.com/downloads/details.aspx?FamilyID=78cac895-efc2-4f8e-a9e0-3a1afbd5922e&DisplayLang=en

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Pierre CorneliusCommented:
Sounds like a problem with your isolationLevel.

What is your ADOConnection1.IsolationLevel property set to?

Try setting your ADOConnection1.IsolationLevel property to "ilReadCommitted"

Regards
Pierre
saravananvgCommented:
Hello Sir,

  I think you need to refresh your record set once new details added, hence close the query and then open it.

ie.,

instead of
(TADOQuery.SQL.Clear;  TADOQuery.SQL.Add('select ...');  TADOQuery.Open;  loop until eof)

say it as

with TADOQuery do
begin
   Close;
   SQL.Clear;
   SQL.Add('Select......');
   Open;
end;
and then loop till eof


with regards,
padmaja.
KPBeckerAuthor Commented:
Hello, saravananvg,

thank you for answering. I already tried to close and open the query but this was not helpful.

with regards
K.-P. Becker

"A Close or deactivation of the ADOQuery before putting the new "select" does not change anything." (my question from Feb., 14.)
Ayd192Commented:
Hi,
What about ADOConnection? Have you tested closing then opening it?
How many connections to Access do you have in your application? You'd better have only one connection in your application unless you have a very good reason for it.

Regards,
Aydin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KPBeckerAuthor Commented:
Hi, Aydin !

I thought that closing and opening the _connection_ every time I have written a record is no good idea, but I will try if nothing other works.

It's amazing:  Only a short time ago I was told in "experts-exchange" that it is good style to use one connection for reads and a second one for writes. So I did in my program. I will check whether one connection will solve to problem.

Thanks,
K.-P. Becker
Kyle FosterCEOCommented:
1 Connection for reads and 1 for writes.  Who told you that.  First, it's a wasted connection, second I can now see why you are having problems.   I don't agree that you should have to close and re-open the connection.  That is an uneccesary and time consuming work around to the issue.

If you write to the database and then do a read and the record is not there then something is being cached somewhere.  The trick is to determine where the problem is.

Your TDataset doesn't do this, so it has to be either the connection or the ADODrivers.  I still think your problem is going to be in the MDAC version that you are using which will give you the latest ADO drivers for Access.  I recommended that you install the latest version, but you didn't reply.
Ayd192Commented:
Hi K.P. Becker,

Like kfoster11 I don't think closing and reopenning the connection is a good idea, but as I told you if you have to use more than one connection in your application, the solution is closing and reopenning the connection (kfoster11's hint about catching may lead to a better solution.)

Good luck,
Aydin
Kyle FosterCEOCommented:
Sry Ayd192, I wasnt trying t knock your comment.  I was just stating that the habit of workarounds is a bad habit to use.  If you don't solve the problem, then it WILL come back to haunt you at the worst possible time.

I have had terrible experiences with MDAC and just either reinstaling it or upgrading it to the latest release has fixed them 90% of the time.  

I've written my own descendents of TCustomDataset so I know that there is no caching there.  I wrote my own to add the caching.  But, Microsoft is known for caching and since you are using their ADO Drivers to their Access database, you need to make sure that everytihing is working together on that end.  If that doesn't solve the problem, then you have narrowed it down by a very large margin.

I can say that I have never seen this problem with the Delphi database architecture.  At least not until the .NET versions, then it is part of the architecture.
KPBeckerAuthor Commented:
Hi, experts !
I was out of office for a couple of days so it took some time to test and answer.

Meikl:  The CursorLocation does not have any influence to the problem.

Pierre:  The same is true for the IsolationLevel of the ADOConnection.

kforster:  Update of MDAC did not solve the problem, too.

saravananvg:  To close and open the query does not help.

Aydin-1:  I do not want to close and open the ADOConnection every time a record is written. This takes a lot of time and I think this is nothing a Connection is thought to be.

Aydin-2:  I úsed two ADOConnections: One for reading and one for writing. Using only one the program works !!!  So it may be a problem with the cursors and / caching.
As I already wrote I was told to use different Connections for reading and writing.

kforster:
Have a look at this question:   Title: Change between reading and writing via SQL very time-consuming
-------------------------------------------------------------------------------------------------------------------------------
KPBecker To mikelittlewood:  
Why using 2 ADOConnections ?  One will do as well, isn't it ?

Comment from mikelittlewood
Date: 03/10/2005 03:52PM CET
You can use one connection K.-P. Becker, but it is safer to use 1 read connection and one write connection.
This is the normal practice when using ADO connections in a commercial environment.
--------------------------------------------------------------------------------------------------------------------------------
In the case of this problem (switching is very time-consuming) the advice of mike littlewood really was very helpful.

It looks as it one can have either getting back the record written last (unsing one connection)  or a fast switching between  reading and writing (using two connections).  -  Very strange, isn't it ?

I would like to give the points to Aydin who gave a solution and another 100 points to kforster.

Thanks to all of you !
K.-P. Becker
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.