Latency in DB-update using Jet

[Tormod Engebu]

I'm currently using Jet for an Visual Basic Access application.

The application is supposed to delete a row in a table, and afterwards
update indexes on the remaining rows in that table, by giving them new
indexes. This works very well for me until I got to the point (on my
computer) that I had over 1500 rows that was to be updated. Then,
because of the latency-problem the row deleted was "not deleted"
before the update-statement triggered, making my application crash.

On my computer the "limit" of rows before this occures was 1500, but
on my clients computer the limit case just after 50-60 rows.

Any suggestions to make this delete-statement final before the update
kicks in? Is it possible to "commit" changes somehow, forcing the
delete to be done?
Is there any other way of connecting to an Access DB that will make
the latency go away (or at least trigger at a higher level..) I need
to be able to delete a row, and immidiately update about 2000 rows to
be on the safe side here.

And no, SQL Server or MySQL or any other database (except filesystem
stuff) is not an option.

Anyone?

[John Winterbottom]

"Tormod Engebu" <to****@engebu.com> wrote in message
news:9e**************************@posting.google.c om...

I'm currently using Jet for an Visual Basic Access application.

The application is supposed to delete a row in a table, and afterwards
update indexes on the remaining rows in that table, by giving them new
indexes. This works very well for me until I got to the point (on my
computer) that I had over 1500 rows that was to be updated. Then,
because of the latency-problem the row deleted was "not deleted"
before the update-statement triggered, making my application crash.

Can't answer your question directly since I've never had this problem - VB
normally executes sql commands synchronously through DAO or ADO. Perhaps if
you post the exact code you're using.

A couple of other points though; are you talking about the actual table
(system) indexes? If yes then why are you doing this? Re-indexing constantly
is probably not a good idea. If you're talking about a column named 'Index'
instead then it sounds like you have a design normalization issue. Post your
table structure with some sample data and show us exactly what you are
trying to accomplish and somebody will be able to help.

[James Fortune]

to****@engebu.com (Tormod Engebu) wrote in message news:<9e**************************@posting.google. com>...

I'm currently using Jet for an Visual Basic Access application.

The application is supposed to delete a row in a table, and afterwards
update indexes on the remaining rows in that table, by giving them new
indexes. This works very well for me until I got to the point (on my
computer) that I had over 1500 rows that was to be updated. Then,
because of the latency-problem the row deleted was "not deleted"
before the update-statement triggered, making my application crash.

On my computer the "limit" of rows before this occures was 1500, but
on my clients computer the limit case just after 50-60 rows.

Any suggestions to make this delete-statement final before the update
kicks in? Is it possible to "commit" changes somehow, forcing the
delete to be done?
Is there any other way of connecting to an Access DB that will make
the latency go away (or at least trigger at a higher level..) I need
to be able to delete a row, and immidiately update about 2000 rows to
be on the safe side here.

And no, SQL Server or MySQL or any other database (except filesystem
stuff) is not an option.

Anyone?

If I understand you correctly, you need something like (from this NG
and Northwind.mdb):

Set MyDB = CurrentDb
DoCmd.SetWarnings False
strSQL = "DELETE * FROM tblQuotes WHERE [QuoteID] = " & Me!QuoteID &
";"
MyDB.Execute strSQL, dbFailOnError
DoCmd.SetWarnings True
Do While MyDB.RecordsAffected < 1
DoEvents
Loop
Set MyDB = Nothing

Make sure your table has at least one record to be affected by the
query before using this technique or the loop won't exit. Update
queries can also use this technique.

James A. Fortune

Petition for redress of grievances was a remedy during the early years
of the U.S. to prevent, among other things, "tyranny of the majority."

[Tormod Engebu]

Thank you!
Will try your sugegstion for now, and maybe rewrite the code for the
next version to not use Access.

t.

[Tony Toews]

ja******@oakland.edu (James Fortune) wrote:

DoCmd.SetWarnings False
strSQL = "DELETE * FROM tblQuotes WHERE [QuoteID] = " & Me!QuoteID &
";"
MyDB.Execute strSQL, dbFailOnError
DoCmd.SetWarnings True

You don't need to do Setwarnings with db.execute. If you do put them in you must
also put the DoCmd.SetWarnings True line in the error handling section.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[James Fortune]

Tony Toews <tt****@telusplanet.net> wrote in message news:<5q********************************@4ax.com>. ..

ja******@oakland.edu (James Fortune) wrote:

DoCmd.SetWarnings False
strSQL = "DELETE * FROM tblQuotes WHERE [QuoteID] = " & Me!QuoteID &
";"
MyDB.Execute strSQL, dbFailOnError
DoCmd.SetWarnings True

You don't need to do Setwarnings with db.execute. If you do put them in you must
also put the DoCmd.SetWarnings True line in the error handling section.

Tony

Thanks for the information Tony. The MyDB.Execute strSQL replaced
what was formerly a DoCmd.RunSQL that took too long to complete. I
didn't realize that the db.execute wouldn't pop up a warning.

James A. Fortune

Until somewhat recently people were allowed to spell English words any
way they wanted. - From info at the New England Historic Genealogical
Society

[Darryl Kerkeslager]

Stil R.
"James Fortune" <ja******@oakland.edu> wrote:

Until somewhat recently people were allowed to spell English words any
way they wanted. - From info at the New England Historic Genealogical
Society

[James Fortune]

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message news:<3p********************@comcast.com>...

Stil R.

Gud. (Without any orthographical connotation consequences.)

James A. Fortune