ADO refresh problem...

[Matthew Wells]

Hello,

I have a main form with two continuous subforms - neither is using
master/child fields. Subform2 is bound to a local temp table. When I
update a record on subform1 it executes a function on subform2 that deletes
the records from teh temp table and repopulates it and of course executes a
me.requery afterweards. This is done with an ado connection object. The
problem is that subform2 doesn't "see" the data for a few seconds. I've
tried using a transaction and closing the conneciton object and using
DoEvents but it doesn't work. I've had to resort to putting in a 2 second
delay before the me.requery, but that's not reasonable. I must use the ADO
connection so DAO db object is out.

Is there a way to sync between the currentdb and ADO faster?

Thanks.

Matthew Wells
MW****@FirstByte.net

[russell.sinclair]

I believe that this is a result of a "feature" in ADO that means that
you can't see the changes between different connections in Access for a
specified time (I think it's 5 seconds though). Are you using the same
connection object or creating a new one? I'd suggest using
CurrentProject.Connection if at all possible or passing the current
connection between the forms.

Regards,
Russell Sinclair

[Lyle Fairfield]

Well what can you expect? ADO is the problem with everything except in
these special versions of Access that my friend Bill Gates sends
driectly to me where everything works perfectly, instantly and with a
minimum of fuss.

[Lyle Fairfield]

Well what can you expect? ADO is the problem with everything except in
these special versions of Access that my friend Bill Gates sends
driectly to me where everything works perfectly, instantly and with a
minimum of fuss.

[Matthew Wells]

I'm using Access for the back end so I can't use an ADO recordset behind the
subform (JET data is not editable), subsequently I can't use the same
connection. Becuase the subform is bound to a local Access table there is
inherently a lag between the ADO statement populating the local Access table
and the DAO bound form seeing the changes. I was hoping there was a way to
tell the Access form that new data is there - go get it. me.requery won't
work because the currentdb still doesn't know of the changes.
<ru**************@gmail.com> wrote in message
news:11*********************@p10g2000cwp.googlegro ups.com...

I believe that this is a result of a "feature" in ADO that means that
you can't see the changes between different connections in Access for a
specified time (I think it's 5 seconds though). Are you using the same
connection object or creating a new one? I'd suggest using
CurrentProject.Connection if at all possible or passing the current
connection between the forms.

Regards,
Russell Sinclair

[Matthew Wells]

I'm using Access for the back end so I can't use an ADO recordset behind the
subform (JET data is not editable), subsequently I can't use the same
connection. Becuase the subform is bound to a local Access table there is
inherently a lag between the ADO statement populating the local Access table
and the DAO bound form seeing the changes. I was hoping there was a way to
tell the Access form that new data is there - go get it. me.requery won't
work because the currentdb still doesn't know of the changes.
<ru**************@gmail.com> wrote in message
news:11*********************@p10g2000cwp.googlegro ups.com...

I believe that this is a result of a "feature" in ADO that means that
you can't see the changes between different connections in Access for a
specified time (I think it's 5 seconds though). Are you using the same
connection object or creating a new one? I'd suggest using
CurrentProject.Connection if at all possible or passing the current
connection between the forms.

Regards,
Russell Sinclair

[David W. Fenton]

"Matthew Wells" <MW****@FirstByte.net> wrote in
news:Uh*****************@newsread1.news.atl.earthl ink.net:

I'm using Access for the back end so I can't use an ADO recordset
behind the subform (JET data is not editable), subsequently I
can't use the same connection. Becuase the subform is bound to a
local Access table there is inherently a lag between the ADO
statement populating the local Access table and the DAO bound form
seeing the changes. I was hoping there was a way to tell the
Access form that new data is there - go get it. me.requery won't
work because the currentdb still doesn't know of the changes.

I don't quite understand what you're doing that forces you to use
ADO instead of DAO. Are you absolutely sure about that?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Darryl Kerkeslager]

"Matthew Wells" <MW****@FirstByte.net> wrote

I'm using Access for the back end so I can't use an ADO recordset behind
the
subform (JET data is not editable), subsequently I can't use the same
connection. Becuase the subform is bound to a local Access table there is
inherently a lag between the ADO statement populating the local Access
table
and the DAO bound form seeing the changes. I was hoping there was a way
to
tell the Access form that new data is there - go get it. me.requery won't
work because the currentdb still doesn't know of the changes.
<ru**************@gmail.com> wrote

I believe that this is a result of a "feature" in ADO that means that
you can't see the changes between different connections in Access for a
specified time (I think it's 5 seconds though). Are you using the same
connection object or creating a new one? I'd suggest using
CurrentProject.Connection if at all possible or passing the current
connection between the forms.

What you are experiencing is not an ADO issue, but a code problem. You
should always use CurrentProject.Connection, unless you are accessing a
remote, non-linked database. If there is any "lag", it is because you have
attempted to create a New ADODB.Connection to the current db, and that is
known to cause a delay. Just don't do it.

Although you may not be able to use ADO to edit a Recordset, you can
certainly use ADO to edit the underlying data table. If the underlying data
is a query, you can also use the Recordset.Clone to edit the data.

Perhaps if you posted your code?

Also, as David Fenton said, why do you feel you *have to* use ADO? I use it
because I'm comfortable with it, but if you're working with Access on FE and
BE, then there's nothing preventing you from using DAO.
--
Darryl Kerkeslager

[RoyVidar]

Matthew Wells wrote in message
<Uh*****************@newsread1.news.atl.earthlink. net> :

I'm using Access for the back end so I can't use an ADO recordset behind the
subform (JET data is not editable), subsequently I can't use the same
connection. Becuase the subform is bound to a local Access table there is
inherently a lag between the ADO statement populating the local Access table
and the DAO bound form seeing the changes. I was hoping there was a way to
tell the Access form that new data is there - go get it. me.requery won't
work because the currentdb still doesn't know of the changes.
<ru**************@gmail.com> wrote in message
news:11*********************@p10g2000cwp.googlegro ups.com...

I believe that this is a result of a "feature" in ADO that means that
you can't see the changes between different connections in Access for a
specified time (I think it's 5 seconds though). Are you using the same
connection object or creating a new one? I'd suggest using
CurrentProject.Connection if at all possible or passing the current
connection between the forms.

Regards,
Russell Sinclair

I think it is Access 2000 that has a problem with not updateable ADO
form recorsets based on Jet data, not later versions
http://support.microsoft.com/kb/281998/EN-US/
http://support.microsoft.com/default...b;EN-US;227053

Since you're dealing with both DAO and ADO, the following will probably
not work, either, but perhaps it can give some ideas?
http://support.microsoft.com/kb/q200300/

But perhaps as the others have suggested, you migth get off a bit
easier by having a look at a DAO approach.

--
Roy-Vidar

[Matthew Wells]

I will be moving to SQL Server so I'm trying to write my code in ADO now.
I'm also stuck using Access 2000. I am trying to write the entire system
using unbound forms and no table links. This works fine for everything
except continuous subforms. I also can't use third-party controls - which I
like - so I am very constricted. A good grid control would work very well
but I can't use it. It looks like I'm stuck using DAO for those subforms.

Thansk for all your help out there.

"Matthew Wells" <MW****@FirstByte.net> wrote in message
news:EG***************@newsread3.news.atl.earthlin k.net...

I'm using Access for the back end so I can't use an ADO recordset behind the subform (JET data is not editable), subsequently I can't use the same
connection. Becuase the subform is bound to a local Access table there is
inherently a lag between the ADO statement populating the local Access table and the DAO bound form seeing the changes. I was hoping there was a way to tell the Access form that new data is there - go get it. me.requery won't
work because the currentdb still doesn't know of the changes.
<ru**************@gmail.com> wrote in message
news:11*********************@p10g2000cwp.googlegro ups.com...

I believe that this is a result of a "feature" in ADO that means that
you can't see the changes between different connections in Access for a
specified time (I think it's 5 seconds though). Are you using the same
connection object or creating a new one? I'd suggest using
CurrentProject.Connection if at all possible or passing the current
connection between the forms.

Regards,
Russell Sinclair