Multiple Requeries Needed at Runtime before Records get displayed.

[Jeff A via AccessMonster.com]

hi all,

i have a bound subform (say MySubForm) in an unbound form (say MyForm).
MySubForm's Record Source is set to a query (MyQuery) which selects certain
records from a linked table (MyLinkdTable). In the MyForm, i have a command
button (cmdProcess) which essentially copies and processes records from a
table (MyTable) to (MyLinkdTable) using ADO. However, after cmdProcess,
MySubForm does not display the recently copied records. I checked the
underlying table (MyLinkdTable) and it is indeed there.

I placed a Me![MySubForm].Requery after cmdProcess. Sometimes it works.
Sometimes it doesn't. I even created another command button (cmdRequery)
that just does that, manually forcing MySubForm to requery. In most cases,
it takes more than three requeries before MySubForm displays the records.

The weird thing is when I try to step through the code, especially after
the Me![MySubForm].Requery line, MySubForm displays the records. No manual
requery (cmdRequery) was needed!

I'm having a difficult time solving the problem, especially since the code
seems to behave fine at debug mode but does not behave well at runtime. I'm
at my wits end. Any idea what causing this?

thanks, jeff

p.s. i'm still using Access 2000.

 

[Rev]

It sounds to me like you are not getting immediate response from the server
hosting myLinked table. I think I experienced this sort of shifty behavior
before. To be clear though, are you checking the existence of the data in the
underlying query at the server like in SQL server 2000 through the Enterprise
Manager or from within the Access environment housing the linked table? In my
similar experience I was stumped because the data was clearly in the SQL
Server table instantly looking through Enterprise Manager but not visible in
my linked table Access 2000. But then mysterously, after some time, the data
would just be there unanounced... I think in my case there was an issue of
having tables linked to multiple SQL Server databases through ODBC (multiple
DSNs with different passwords linked in the same Access database have caused
me other problems with updates and appended records to server tables).

 

[Jeff A via AccessMonster.com]

The linked table points to another Access table that is in a different mdb
file. What I've basically done is to separate the data from the code: one
file just contains data; another file contains the forms, queries, reports,
etc. I use linked tables in the code file.

In general, I use bound controls to display lists of records, usually in
continuous forms. But for some serious data manipulation, I use ADO since I
have a lot more control on the data this way. Consequently, I am connecting
to the data in two ways, through linked tables and through ADO. What I've
noticed is that data processed through ADO are not readily "visible" to
bound controls at runtime for some reason.

In a related problem, I have problems with DSUM as well. I use a DSUM
function to calculate a certain amount from a table. What I've noticed is
that any ADO added records are not readily visible to DSUM, resulting in
wrong calculations. But when I step through the code (debug mode), it works.

I've tried several things like changing the ADO access mode (Keyset to
Dynamic) and even the lock type. Still the same problem: it works in debug
but not in runtime.

 

[Dirk Goldgar]

The linked table points to another Access table that is in a
different mdb file. What I've basically done is to separate the data
from the code: one file just contains data; another file contains the
forms, queries, reports, etc. I use linked tables in the code file.

In general, I use bound controls to display lists of records, usually
in continuous forms. But for some serious data manipulation, I use
ADO since I have a lot more control on the data this way.
Consequently, I am connecting to the data in two ways, through linked
tables and through ADO. What I've noticed is that data processed
through ADO are not readily "visible" to bound controls at runtime
for some reason.

In a related problem, I have problems with DSUM as well. I use a DSUM
function to calculate a certain amount from a table. What I've
noticed is that any ADO added records are not readily visible to
DSUM, resulting in wrong calculations. But when I step through the
code (debug mode), it works.

I've tried several things like changing the ADO access mode (Keyset to
Dynamic) and even the lock type. Still the same problem: it works in
debug but not in runtime.

When you do these ADO updates, are you opening a separate connection to
the database, or are you using the same connection used by Access,
CurrentProject.Connection? If you open a separate connection, there can
be a substantial latency period before the updates you make via that
connection are seen by Access. If you have code like this:

Dim cnn As ADODB.Connection

cnn.Open "your connect string"

you'll see a latency, whereas if your code is like this:

Set cnn = CurrentProject.Connection

I wouldn't expect you to.

 

[Jeff A via AccessMonster.com]

I've just followed your advise. Still the same problem. There's some kind
of latency.

Right now, my "brute-force" solution is to iterate the .requery on the
subform to about 300 (that's the minimum i've discovered to work) before
the records show up. There's a noticeable delay doing it this way about (1
to 2 secs). My users will definitely think this "new" system seems slower
than their current DOS-based program.

 

[Dirk Goldgar]

I've just followed your advise. Still the same problem. There's some
kind of latency.

Right now, my "brute-force" solution is to iterate the .requery on the
subform to about 300 (that's the minimum i've discovered to work)
before the records show up. There's a noticeable delay doing it this
way about (1 to 2 secs). My users will definitely think this "new"
system seems slower than their current DOS-based program.

Would you care to post your code? Maybe we'll be able to see something
that is causing the problem.

 

[Andreas]

- I would probably use DAO, rather than ADO, if possible.

- Not sure this applies to ADO and don't have Access on this PC so I
can't check. I believe there is a method of "flushing" the data, which
forces data to be written immediately rather than being buffered (which
I think also interacts with the OS). Might be worthwhile having a look at.

Regards,
Andreas