SQL-Query blocked

[Roschigraz]

Hello

I Have a big Problem with my accessXP-application

I put a List-object in a Form which is filled with Data from a Tabelle stored on a SQL_Server 2000

Select * from Table

The User is able to click an a row and the all fields were displayed in textboxes on the form.

and the save-event (vba) makes the Problem.....

if the count of Rows in the table is greater then 10.500 the query from the list do not end
exactly the Prozess on the SQL_server is still running an so the Prozess which will store the changes is blocked.

Is there somewhere a property to set the limit 10.500 to a higher value or another has anybody an idea to avoid this problem....

there are over 40 tables in this database, but this problem only occurse on one :-(((


Regards
Robert

 

[JerryKlmns]

Robert

I would recomend to give your user the option to search for a specific value and then get data to your form. Moving around more than 10000 records is a lot of traffic!

Just add a combobox to select the field for the search and one text box for the value to search. On the click event of a button, execute a stored procedure for the imput data, and display the results to your text boxes. If those values aren't to change then instead of textbox use a label that you 'll make it look like a textbox.That way your access form is lighter. You may also need to handle values not found.

 

[Roschigraz]

thank you

but I have put the Data on a List (must have from customer :-()

I can't find the reason for this problem, because I use the same technic in many forms an d also on tables with much more then 10000 records...

only 1 table makes trouble :-(

 

[JerryKlmns]

Then Robert, you'll have to check (maybe post?) the save-event (vba).I gave you a very recommended technique in TT.

 

[Roschigraz]

newest infos..... ;-)

the Problem does not depend on forms...

Testenvironment:

1. I create a query "SELECT * From Table"
2. I try to modify a field in Table

Scenario 1:
* open query
* modify

Result: Timeout, because query-prozess (at sql-server) blocked update-prozess

Scenario 2:
* open query
* jump to last record
* modify

Result: No Problem!!! Update of Record succesful



I think ACCESS do not finish the query himself ?!?!?!?

 

[Roschigraz]

@ Jerry

Vba save event:

I tried both:

1:

set rst = dbs.openrecordset("Table",dbopendynaset,dbseechanges)

rst.findfirst ...

rst.edit
rst!active = true
rst.update

rst.close

2:

dbs.execute "Update .....", dbseechanges


same Result.......

 

[JerryKlmns]

Access fetches all records from all tables/queries from the datasource involved, to the user's pc, filters/groups and then shows result. That 's traffic. SQL Server does all this using its datbase engine and transfers result to user's PC

You might read all data with LockType=.adLockReadOnly and then do your update/addition of records using a direct connection object to your SQL Server. Mutch faster!