June 9, 2008 at 12:52 am
I use a SQL-Server view to display data in a MS Access Listbox.
Now I need to udpate data in one of the underlying tables. But due to the fact, that not all records are fetched yet, the connection is sleeping until I scrolled down to the very end of the listbox. Then! I can run an update. If I run the update before, the udpate fails, because the connection used is still waiting for the completion of the select.
Hmm. How do I manage that the view is fully fetched or not locking the underlying tables??
Chris
June 10, 2008 at 1:06 pm
Adding some code to quickly scroll to the bottom and back up?
June 10, 2008 at 5:49 pm
How many records would you expect to be returned?
GUI design suggests that a Listbox would not have a very large number of records so should be very fast to populate.
Try running the view in SQL Server itself (SELECT * FROM yourView).
Is it almost as slow as when yourView is used to populate the Access Listbox?
If no then the problem is in Access.
If yes then the problem is in the view. Look at optimising the view in all the usual ways: joins, indexes, ...
If still no luck then you could post the view definition, the underlying table definitions and any indexes on the tables and some of the tuning experts may be able to help.
June 12, 2008 at 12:51 am
Hi,
thanks for the answer.
the view itself is very fast. The problem is with MS Access. I decided to set the view WITH (NOLOCK), that helped.
Chris
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply