ADO Find and Bookmark, inconsistent results any suggestions?

[pingala]

Hi All,

i'm bit frustrated as all my users are reporting a weird behaviour
from one of our access database. It is an ADP application on SQL
Express 2005 as backend using Access 2003.

No problem of this sort has been reported whatsoever, and no change
have been done to either the dbase structure or the vba code since the
problem came out.

to be very concise, when a form open it will load -using the bookmark
method- the first record available. this is the code:

Dim rs As New ADODB.Recordset

Set rs = Me.Recordset.Clone
rs.MoveFirst
rs.Find "[IdItemOrdered] = " & Me![machineList], 0, 1, 1
If rs.EOF = False Then
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

where Me!machineList contains an ID previously checked with a direct
SQL query if it exist or not ... only then the control move over this
procedure ...

when i debug i can see the Me!machineList correctly populated, but
when it performs the Find method it found
nothing setting rs.EOF to True .. and so skipping the bookmark
operation.

Now this happen only on some records, while it doesn't happen on
others.. and there is no differences between these records... of
course the content is different but they contains the same amount of
data ...

any idea?
i'm completely lost.

thanks
T.

 

[Sylvain Lafontaine]

Many possibilities here. First, like mdb files, ADP are dynamically
compiled and this compilation is based on the version of windows and SP of
the client(s); so you have to make sure that everyone is using his or her
own copy of the ADP file. You should also decompile the copy of the ADP
file directly on the client machine before using it to make sure that you
are casher on this.

Second, beside a compilation problem, there are other things that are known
to cause memory corruption problem: the use of bit fields that can be null
(make sure that all of your bit fields cannot be null and have a default
value of either 0 or 1). Using RecordsetClone anywhere in your code instead
of Recordset.Clone is also a well known source of problem. Even if you are
using Recordset.Clone in your example, it's possible that the memory is
already corrupted if you have used RecordsetClone somewhere else.

Before calling rs.Find, make that the rs is valid by calling rs.EOF (and
rs.BOF?). You should make this test not only after but also before the call
to .MoveFirst and .Find.

You are saying that the problem occurs when the form opens; so a possibility
would be that you are changing the record source of the form directly in the
OnOpen event or the OnLoad event. Changing the record source in the OnOpen
event will force the OnLoad event to be called immediately, before the end
of the OnOpen event and not after its ending. This change in the behavior
of events can lead to subtil bugs and using the debugging will also change
the overall behavior of these events.

Using the debugger not only can change the order of execution of events (I'm
thinking in particular about the OnOpen and OnLoad events here) but also the
memory layout; so instead of using the debugger, use Debug.Print in order to
trace and debug this kind of problem.

Finally, make sure that you are closing the form before making any of your
tests and that you are not switching between the design mode and the view
mode. When you are switching from design mode to the View mode; the record
source is not necessarily called again and when it is, it's not necessarily
with the default values of the controls on the form (for the controls that
are used as parameters to the record source).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi All,

i'm bit frustrated as all my users are reporting a weird behaviour
from one of our access database. It is an ADP application on SQL
Express 2005 as backend using Access 2003.

No problem of this sort has been reported whatsoever, and no change
have been done to either the dbase structure or the vba code since the
problem came out.

to be very concise, when a form open it will load -using the bookmark
method- the first record available. this is the code:

Dim rs As New ADODB.Recordset

Set rs = Me.Recordset.Clone
rs.MoveFirst
rs.Find "[IdItemOrdered] = " & Me![machineList], 0, 1, 1
If rs.EOF = False Then
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

where Me!machineList contains an ID previously checked with a direct
SQL query if it exist or not ... only then the control move over this
procedure ...

when i debug i can see the Me!machineList correctly populated, but
when it performs the Find method it found
nothing setting rs.EOF to True .. and so skipping the bookmark
operation.

Now this happen only on some records, while it doesn't happen on
others.. and there is no differences between these records... of
course the content is different but they contains the same amount of
data ...

any idea?
i'm completely lost.

thanks
T.

 

[Sylvain Lafontaine]

I forgot to mention the old problem with bookmark and the "Option Compare
Database" or the "Option Compare Binary". Don't know if this problem is
still around or it can apply to the .Find method too but maybe you have a
problem with .Find or .Bookmark simply because you have used the wrong
option or have forgotten to write one at the beginning of your module(s).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Many possibilities here. First, like mdb files, ADP are dynamically
compiled and this compilation is based on the version of windows and SP of
the client(s); so you have to make sure that everyone is using his or her
own copy of the ADP file. You should also decompile the copy of the ADP
file directly on the client machine before using it to make sure that you
are casher on this.

Second, beside a compilation problem, there are other things that are
known to cause memory corruption problem: the use of bit fields that can
be null (make sure that all of your bit fields cannot be null and have a
default value of either 0 or 1). Using RecordsetClone anywhere in your
code instead of Recordset.Clone is also a well known source of problem.
Even if you are using Recordset.Clone in your example, it's possible that
the memory is already corrupted if you have used RecordsetClone somewhere
else.

Before calling rs.Find, make that the rs is valid by calling rs.EOF (and
rs.BOF?). You should make this test not only after but also before the
call to .MoveFirst and .Find.

You are saying that the problem occurs when the form opens; so a
possibility would be that you are changing the record source of the form
directly in the OnOpen event or the OnLoad event. Changing the record
source in the OnOpen event will force the OnLoad event to be called
immediately, before the end of the OnOpen event and not after its ending.
This change in the behavior of events can lead to subtil bugs and using
the debugging will also change the overall behavior of these events.

Using the debugger not only can change the order of execution of events
(I'm thinking in particular about the OnOpen and OnLoad events here) but
also the memory layout; so instead of using the debugger, use Debug.Print
in order to trace and debug this kind of problem.

Finally, make sure that you are closing the form before making any of your
tests and that you are not switching between the design mode and the view
mode. When you are switching from design mode to the View mode; the
record source is not necessarily called again and when it is, it's not
necessarily with the default values of the controls on the form (for the
controls that are used as parameters to the record source).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi All,

i'm bit frustrated as all my users are reporting a weird behaviour
from one of our access database. It is an ADP application on SQL
Express 2005 as backend using Access 2003.

No problem of this sort has been reported whatsoever, and no change
have been done to either the dbase structure or the vba code since the
problem came out.

to be very concise, when a form open it will load -using the bookmark
method- the first record available. this is the code:

Dim rs As New ADODB.Recordset

Set rs = Me.Recordset.Clone
rs.MoveFirst
rs.Find "[IdItemOrdered] = " & Me![machineList], 0, 1, 1
If rs.EOF = False Then
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

where Me!machineList contains an ID previously checked with a direct
SQL query if it exist or not ... only then the control move over this
procedure ...

when i debug i can see the Me!machineList correctly populated, but
when it performs the Find method it found
nothing setting rs.EOF to True .. and so skipping the bookmark
operation.

Now this happen only on some records, while it doesn't happen on
others.. and there is no differences between these records... of
course the content is different but they contains the same amount of
data ...

any idea?
i'm completely lost.

thanks
T.