By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Confirm Successfull Update of Database

P: n/a
Hi

I am having trouble with returning a value from an ado connection
execute function when using msaccess. If I execute a statement to
delete a record from a table I then have to loop through the table
until I can confirm that the record has in fact been deleted. This is
obviously affecting my applications performance.

What I need to do is something like :

Dim I as Integer
I = Adoconn.execute strsql

I need "I" to indicate whether or not the statement was successfull or
not.

I have also had cases where, if I try to return a recordset
immediately after an update statement, the recordset does not return
the updated value. If I cause a delay first then the correct info is
returned.

Anyway to sort these two things out?

Steve
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
The Execute method will pass back the number of records operated on if you
use the correct parameter list.
Also, since Execute returns a Recordset, I can't follow why you are using
integer "I".

Given these variables:
Dim lngAffected as Long
Dim cnn as ADODB.Connection
Dim rst as ADODB.Recordset
Dim strSQL as String

You should use:
Set rst = cnn.Execute(strSQL, lngAffected, adExecuteNoRecords)
In the above:
1) strSQL contains an action query, not a SELECT statement.
2) *lngAffected* returns how many records were inserted, updated, or deleted
3) adExecuteNoRecords indicates that the sql is an action query and does not
return any records

You can use the shortcut syntax below since "rst" is returned as an Empty
and closed recordset.

Call cnn.Execute(strSQL, lngAffected, adExecuteNoRecords)

Ian Hinson

"Steve" <st*********@fwuk.fwc.com> wrote in message
news:ad**************************@posting.google.c om...
Hi

I am having trouble with returning a value from an ado connection
execute function when using msaccess. If I execute a statement to
delete a record from a table I then have to loop through the table
until I can confirm that the record has in fact been deleted. This is
obviously affecting my applications performance.

What I need to do is something like :

Dim I as Integer
I = Adoconn.execute strsql

I need "I" to indicate whether or not the statement was successfull or
not.

I have also had cases where, if I try to return a recordset
immediately after an update statement, the recordset does not return
the updated value. If I cause a delay first then the correct info is
returned.

Anyway to sort these two things out?

Steve

Nov 13 '05 #2

P: n/a
Ian

Thank you for your reply. I was obviously very confused as to how to do
it.

This still leaves me with one problem though. Using the *lngaffected*
variable in your code returns the no of records affected. Is there no
way of telling when the table has actually been updated so that it can
be read by another recordset?

What is happening is that if I run an Action query and immediately try
to populate a grid/list box etc using another recordset, the changes are
not shown in the grid until a few seconds later. I have to keep
refreshing the grid until I see that the change has taken place. I have
thought about putting a delay statement in so that it causes a delay
before repopulating the grid but surely this is not necessary?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

P: n/a
I don't use ADO, but see if the "Flush" method will do what you want. I
believe it will force the cache to write the records to the table.

--
Wayne Morgan
Microsoft Access MVP
"Steve Adams" <st*********@fwuk.fwc.com> wrote in message
news:41**********************@news.newsgroups.ws.. .
Ian

Thank you for your reply. I was obviously very confused as to how to do
it.

This still leaves me with one problem though. Using the *lngaffected*
variable in your code returns the no of records affected. Is there no
way of telling when the table has actually been updated so that it can
be read by another recordset?

What is happening is that if I run an Action query and immediately try
to populate a grid/list box etc using another recordset, the changes are
not shown in the grid until a few seconds later. I have to keep
refreshing the grid until I see that the change has taken place. I have
thought about putting a delay statement in so that it causes a delay
before repopulating the grid but surely this is not necessary?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #4

P: n/a
Are you populating the grid/listbox that is opened after the update, or was
already open and was held open during the update?

My understanding is that the Execute has immediate effect on the underlying
database.
I would expect any recordset opened after that would include the changes,
and not need a delay.
Something seems not normal about this.

Ian.

"Steve Adams" <st*********@fwuk.fwc.com> wrote in message
news:41**********************@news.newsgroups.ws.. .
Ian

Thank you for your reply. I was obviously very confused as to how to do
it.

This still leaves me with one problem though. Using the *lngaffected*
variable in your code returns the no of records affected. Is there no
way of telling when the table has actually been updated so that it can
be read by another recordset?

What is happening is that if I run an Action query and immediately try
to populate a grid/list box etc using another recordset, the changes are
not shown in the grid until a few seconds later. I have to keep
refreshing the grid until I see that the change has taken place. I have
thought about putting a delay statement in so that it causes a delay
before repopulating the grid but surely this is not necessary?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.