Records added with ADo don't show up in query immediately

[gerbski]

Hi all,

I am relatively new to ADO, but up to now I got things working the way
I wanted. But now I've run into somethng really annoying.

I am working in MS Access. I am using an Access frontend separately
from a backend. The tables from the backend database are linked in the
frontend database.
In the frontend there is a Form with a listbox in it. The listbox
rowsource is a query that selects all the records from a (linked)
table. Via this form I am adding new records to the table, and for this

I am using a ADO Connection and Recordset. The data is added to the
table via the AddNew and Update Methods of the recordset.
Now, when I Requery the Listbox in the form straight after the adding
of the record, I will not see the newly added record. It will take a
short time, from one second up to 3 seconds before I will see the newly

added record via a Requery of the Listbox. Which is very annoying, as
you can imagine.
I've got two possibilities to avoid this problem, both of which I have
not been able to find:
1) Perform a check to find when the data is 'really' added to the
database so i can then perform the requery.
2) force the recordset, connection or whatever to immediatly write the
data to the table and 'release' it for other queries to see, so I know
for sure my Requery will give the proper data.
Any help will be much appreciated!
Greetings,
Gerben.

['69 Camaro]

Hi, Gerben.

Via this form I am adding new records to the table, and for this
I am using a ADO Connection and Recordset. The data is added to the
table via the AddNew and Update Methods of the recordset.

Using Recordsets for data manipulation is the "last resort." You must have a
really, really good reason to avoid bound forms or SQL, because it's extra work
and it's slow.

It will take a
short time, from one second up to 3 seconds before I will see the newly
added record via a Requery of the Listbox.

That means you're connecting to the linked tables in the current database file,
or you're connecting to the tables located across the network and your network
is rather fast, or you have a lot of data being pulled across the network.
Connecting across the network can take up to five seconds with that built-in
delay from having multiple connections.

I've got two possibilities to avoid this problem, both of which I have
not been able to find:
1) Perform a check to find when the data is 'really' added to the
database so i can then perform the requery.
2) force the recordset, connection or whatever to immediatly write the
data to the table and 'release' it for other queries to see, so I know
for sure my Requery will give the proper data.

Are you sure you can't use a bound form and a list box with a row source that
uses a query on the linked tables so that you can just requery the list box as
needed, with instant or near instant results displayed?

There are also ways to optimize list boxes and combo boxes, but it sounds like
you've got several design problems that need to be addressed first, where
Recordset usage is only one of them. Do you have indexes on the join columns,
the criteria columns, and the sort columns in the query used to populate the
list box?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"gerbski" <ge*****@holland.aswrote in message
news:11**********************@42g2000cwt.googlegro ups.com...

Hi all,

I am relatively new to ADO, but up to now I got things working the way
I wanted. But now I've run into somethng really annoying.

I am working in MS Access. I am using an Access frontend separately
from a backend. The tables from the backend database are linked in the
frontend database.
In the frontend there is a Form with a listbox in it. The listbox
rowsource is a query that selects all the records from a (linked)
table. Via this form I am adding new records to the table, and for this

I am using a ADO Connection and Recordset. The data is added to the
table via the AddNew and Update Methods of the recordset.
Now, when I Requery the Listbox in the form straight after the adding
of the record, I will not see the newly added record. It will take a
short time, from one second up to 3 seconds before I will see the newly

added record via a Requery of the Listbox. Which is very annoying, as
you can imagine.
I've got two possibilities to avoid this problem, both of which I have
not been able to find:
1) Perform a check to find when the data is 'really' added to the
database so i can then perform the requery.
2) force the recordset, connection or whatever to immediatly write the
data to the table and 'release' it for other queries to see, so I know
for sure my Requery will give the proper data.
Any help will be much appreciated!
Greetings,
Gerben.

['69 Camaro]

Hi, Gerben.

For more information about improving performance in multiuser databases, please
see the following Web page for a link to Access MVP Tom Wickerath's article,
"Implementing a Successful Multiuser Access/JET Application":

http://www.Access.QBuilt.com/html/articles.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AMwrote in
message news:5t******************************@adelphia.com ...

Hi, Gerben.

>Via this form I am adding new records to the table, and for this
I am using a ADO Connection and Recordset. The data is added to the
table via the AddNew and Update Methods of the recordset.

Using Recordsets for data manipulation is the "last resort." You must have a
really, really good reason to avoid bound forms or SQL, because it's extra
work and it's slow.

>It will take a
short time, from one second up to 3 seconds before I will see the newly
added record via a Requery of the Listbox.

That means you're connecting to the linked tables in the current database
file, or you're connecting to the tables located across the network and your
network is rather fast, or you have a lot of data being pulled across the
network. Connecting across the network can take up to five seconds with that
built-in delay from having multiple connections.

>I've got two possibilities to avoid this problem, both of which I have
not been able to find:
1) Perform a check to find when the data is 'really' added to the
database so i can then perform the requery.
2) force the recordset, connection or whatever to immediatly write the
data to the table and 'release' it for other queries to see, so I know
for sure my Requery will give the proper data.

Are you sure you can't use a bound form and a list box with a row source that
uses a query on the linked tables so that you can just requery the list box as
needed, with instant or near instant results displayed?

There are also ways to optimize list boxes and combo boxes, but it sounds like
you've got several design problems that need to be addressed first, where
Recordset usage is only one of them. Do you have indexes on the join columns,
the criteria columns, and the sort columns in the query used to populate the
list box?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"gerbski" <ge*****@holland.aswrote in message
news:11**********************@42g2000cwt.googlegro ups.com...

>Hi all,

I am relatively new to ADO, but up to now I got things working the way
I wanted. But now I've run into somethng really annoying.

I am working in MS Access. I am using an Access frontend separately
from a backend. The tables from the backend database are linked in the
frontend database.
In the frontend there is a Form with a listbox in it. The listbox
rowsource is a query that selects all the records from a (linked)
table. Via this form I am adding new records to the table, and for this

I am using a ADO Connection and Recordset. The data is added to the
table via the AddNew and Update Methods of the recordset.
Now, when I Requery the Listbox in the form straight after the adding
of the record, I will not see the newly added record. It will take a
short time, from one second up to 3 seconds before I will see the newly

added record via a Requery of the Listbox. Which is very annoying, as
you can imagine.
I've got two possibilities to avoid this problem, both of which I have
not been able to find:
1) Perform a check to find when the data is 'really' added to the
database so i can then perform the requery.
2) force the recordset, connection or whatever to immediatly write the
data to the table and 'release' it for other queries to see, so I know
for sure my Requery will give the proper data.
Any help will be much appreciated!
Greetings,
Gerben.

[gerbski]

'69 Camaro wrote:

Using Recordsets for data manipulation is the "last resort." You must have a
really, really good reason to avoid bound forms or SQL, because it's extra work
and it's slow.

First of all: thank you for your comments. They are food for thought...

I have a very good reason for unbound forms and data manipulation with
recordsets I have had very bad experiences with bound forms in which
data entry was tricky and hard to get to behave like I wanted. With
unbound forms I can let my controls behave any way I want to, let the
user enter data, and before I add the data to the database I can
perform any check I want to. So while your comments are valuable
comments, I prefer to stick with unbound forms.

That means you're connecting to the linked tables in the current database file,
or you're connecting to the tables located across the network and your network
is rather fast, or you have a lot of data being pulled across the network.
Connecting across the network can take up to five seconds with that built-in
delay from having multiple connections.

To be precise: I am working on a development environment where
everything is local, but have to port it eventually to a network
environment. Hence the separate backend and frontend and hence the use
of ADO. But right now this problem is showing up on a front-end to
back-end connection where both databases are in the smae folder on my
harddisk.
I am not pulling a lot of data over this connection: it is a table with
6 columns (4 text of lenghts 35, 35, 2, and 12, 1 boolean and 1
autonumber) which has 232 rows. Not something that would give rise to
performance problems.

Are you sure you can't use a bound form and a list box with a row source that
uses a query on the linked tables so that you can just requery the list box as
needed, with instant or near instant results displayed?

Yes, I could, but I don't want to ;-)

>
There are also ways to optimize list boxes and combo boxes, but it sounds like
you've got several design problems that need to be addressed first, where
Recordset usage is only one of them. Do you have indexes on the join columns,
the criteria columns, and the sort columns in the query used to populate the
list box?

There are some indexe on the table, and in fact the query for the
listbox just references this one table: no connection with other
tables, and no criteria to filter the data. I do use sorting, but once
the new record has popped up in my list that sorting is almost
instantaneously. So I don't think the problem lies in that area.

HTH.
Gunny

Thanks for your comments, I will surely look at the link you gave me
and see if I can find something there. In the menatime, if there are
any other suggestions: they are still most welcome....

Gerben.

[Bob Quintal]

"gerbski" <ge*****@holland.aswrote in
news:11**********************@k21g2000cwa.googlegr oups.com:

>

....

>
I have a very good reason for unbound forms and data
manipulation with recordsets I have had very bad experiences
with bound forms in which data entry was tricky and hard to
get to behave like I wanted.

I think the problem you had was that you did not know the
correct process to get the data entry smooth on an unbound form.
With unbound forms I can let my

controls behave any way I want to, let the user enter data,
and before I add the data to the database I can perform any
check I want to.

I can easily achieve the same using bound forms.

>Are you sure you can't use a bound form and a list box with a
row source that uses a query on the linked tables so that you
can just requery the list box as needed, with instant or near
instant results displayed?

Yes, I could, but I don't want to ;-)

If you are normal, you don't want to pay income taxes either.
Not wanting doesn't mean you can do as you please without
suffering the consequences.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

['69 Camaro]

Hi.

I am not pulling a lot of data over this connection: it is a table with
6 columns (4 text of lenghts 35, 35, 2, and 12, 1 boolean and 1
autonumber) which has 232 rows. Not something that would give rise to
performance problems.

So if you're having performance problems in a single user environment, imagine
how much more of a problem this is going to be when many other users are
grabbing the data from across the network. Eventually, you are going to have to
find ways to speed up the data operations, no matter how reluctant you are at
the moment to move away from Recordsets. However, the link I gave you to Tom
Wickerath's article should help to some degree, even if you're using Recordsets.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"gerbski" <ge*****@holland.aswrote in message
news:11**********************@k21g2000cwa.googlegr oups.com...

>
'69 Camaro wrote:

>Using Recordsets for data manipulation is the "last resort." You must have a
really, really good reason to avoid bound forms or SQL, because it's extra
work
and it's slow.

First of all: thank you for your comments. They are food for thought...

I have a very good reason for unbound forms and data manipulation with
recordsets I have had very bad experiences with bound forms in which
data entry was tricky and hard to get to behave like I wanted. With
unbound forms I can let my controls behave any way I want to, let the
user enter data, and before I add the data to the database I can
perform any check I want to. So while your comments are valuable
comments, I prefer to stick with unbound forms.

>That means you're connecting to the linked tables in the current database
file,
or you're connecting to the tables located across the network and your
network
is rather fast, or you have a lot of data being pulled across the network.
Connecting across the network can take up to five seconds with that built-in
delay from having multiple connections.

To be precise: I am working on a development environment where
everything is local, but have to port it eventually to a network
environment. Hence the separate backend and frontend and hence the use
of ADO. But right now this problem is showing up on a front-end to
back-end connection where both databases are in the smae folder on my
harddisk.
I am not pulling a lot of data over this connection: it is a table with
6 columns (4 text of lenghts 35, 35, 2, and 12, 1 boolean and 1
autonumber) which has 232 rows. Not something that would give rise to
performance problems.

>Are you sure you can't use a bound form and a list box with a row source that
uses a query on the linked tables so that you can just requery the list box
as
needed, with instant or near instant results displayed?

Yes, I could, but I don't want to ;-)

>>
There are also ways to optimize list boxes and combo boxes, but it sounds
like
you've got several design problems that need to be addressed first, where
Recordset usage is only one of them. Do you have indexes on the join
columns,
the criteria columns, and the sort columns in the query used to populate the
list box?

There are some indexe on the table, and in fact the query for the
listbox just references this one table: no connection with other
tables, and no criteria to filter the data. I do use sorting, but once
the new record has popped up in my list that sorting is almost
instantaneously. So I don't think the problem lies in that area.

>HTH.
Gunny

Thanks for your comments, I will surely look at the link you gave me
and see if I can find something there. In the menatime, if there are
any other suggestions: they are still most welcome....

Gerben.

[RoyVidar]

"gerbski" <ge*****@holland.aswrote in message
<11**********************@42g2000cwt.googlegroups. com>:

Hi all,

I am relatively new to ADO, but up to now I got things working the
way I wanted. But now I've run into somethng really annoying.

I am working in MS Access. I am using an Access frontend separately
from a backend. The tables from the backend database are linked in
the frontend database.
In the frontend there is a Form with a listbox in it. The listbox
rowsource is a query that selects all the records from a (linked)
table. Via this form I am adding new records to the table, and for
this

I am using a ADO Connection and Recordset. The data is added to the
table via the AddNew and Update Methods of the recordset.
Now, when I Requery the Listbox in the form straight after the adding
of the record, I will not see the newly added record. It will take a
short time, from one second up to 3 seconds before I will see the
newly

added record via a Requery of the Listbox. Which is very annoying, as
you can imagine.
I've got two possibilities to avoid this problem, both of which I
have not been able to find:
1) Perform a check to find when the data is 'really' added to the
database so i can then perform the requery.
2) force the recordset, connection or whatever to immediatly write
the data to the table and 'release' it for other queries to see, so I
know for sure my Requery will give the proper data.
Any help will be much appreciated!
Greetings,
Gerben.

This is a known phenomena with ADO/Jet OLEDB. Here's an article about
it http://support.microsoft.com/kb/200300

Basically, if you perform the operations on the same connection, the
changes should be available.

If you're completely unbound, then you could first fire of the action
query, then open a/the recordset to populate the combo on the same
connection, and it should be available.

But I'm not sure I understand the need to go unbound. I mean, Access
is designed for bound approaches, and does that extremely well.

--
Roy-Vidar