RE: Reading Access DB: get someteimes Data - sometimes not

From: Jim Rodgers (JimRodgers_at_discussions.microsoft.com)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 06:28:54 -0700

I may be on the wrong track here, but let me tell you about my experience.

I used to test for .EOF until I ran into a situation where I just happened to be at EOF, but not because the recordset was empty. Rather, I had somehow previously navigated to EOF.

I recently changed my typical practice of
      If Not .EOF Then
to
      If .Recordset>0 Then
and some of my bugs went away.

However, when I was experimenting with some new code last week, I got a recordcount=-1 when the recordset WAS valid. Now, I may go back to doing what you had:

      If Not (.EOF And .BOF) Then

This (.EOF And .BOF) is ONLY true if the recordset is empty. I think I saw this in an example a few years back.

The only other time when I did not immediately see the expected results in my recordset was when I was using multiple connections on an Access file database (which is not recommended, BTW). An update on one connection may not be instantly visible on another connection. And it's not a matter of adding a DoEvents or anything like that. By referencing JRO ("Microsoft Jet and Replication Objects 2.6 Library"), you can use this trick...

       Dim jroEngine As JRO.JetEngine
       Set jroEngine = New JRO.Engine
       jroEngine.RefreshCache

Back to the general case: I have no more instances where the results seem delayed since I began using .BeginTrans and .CommitTrans almost everywhere. A great side effect of using transactions was a roughly five-fold increase in speed in certain tedious procedures.

Hope this is useful,

Jim

PS: I use MDAC 2.8 everywhere. My setupprograms always install MDAC 2.8 as well as certain MSJet updates (when I use Access or Excel files). 

-- 
James W. (Jim) Rodgers, P.E.
Senior Consultant
General Consulting Engineers, LLC
Atlanta, GA
"Marcel Gätzi" wrote:
> Hi,
> 
> I have a very funny situation with reading the same Access db with the same sql statement. Normaly I get data, then not anymore.
> 
> I have this situation only at one customer  - the others work fine. According to the customer - after this occures the server crashes. Has anyone a clue what's goin on here? Here's the code I use:
> 
>    cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
>    lsConnect = "Data Source=" + fuSetFilename(lsDBPath, "db.mdb")
>    cnn.Open lsConnect
>    cnn.CommandTimeout = 6000
>    Set cmd.ActiveConnection = cnn
>    
>    ls_SQL = "Select Key1 From TableXl Where ID = 1 Order By Key1;"
>    
>    cmd.CommandText = ls_SQL
>    Set rst = cmd.Execute()
>    
>    If rst.BOF And rst.EOF Then
>       '**************************   sometimes True
>    Else
>       Call rst.MoveFirst
>       Do While Not rst.EOF
>          '*************************   in the beginning, data is retrieved
>          Call rst.MoveNext
>       Loop
>    End If
>    
>    rst.Close
> 
> further information:
> VB 6.0
> Runs on Windows Server 2000
> Database is on a local Directory!
>

Relevant Pages