MS Access causing problems w/SQL Server

[mwestra]

I've got a situation that's been bothering me for some time now. Hopefully someone can help me out.

I've got a form in an Access 2000 database that's got some ODBC linked tables to my SQL Server 2000. On this form are two combo boxes, each with 3 columns of data listed. Both combo boxes point to a single SQL table (a different table for each combo box). That part of it works fine.

Once I add a couple text boxes that reference a particular column of the combo boxes (ie. =[cmbComboBox].[Column](1)), things start to slow down drastically and if I get a couple more people using this same form, I start to get SQL Server timeout errors. If I close the form, everything takes off again. I notice in EM (Current Activity) that my connections have a very long wait time, and have a Wait Type of NETWORKIO and it just hangs there. If I close the Access form, naturally, my connections close.

Does anyone have any suggestions on how to fix this problem or why it's happening?

Any help would be greatly appreciated.

Marty

 

[donutman]

I'm guessing, the tables are getting locked. Are you allowing updates to the SQL tables?
-Karl

 

[timscronin]

On the access side what is your combo box based on. Make sure if they are query that they are read only. You may want to look at creating some simple pass_through queries for performance which also might eliminate the problem you are having.

 

[mwestra]

I did try setting the queries of the two combo boxes to read only but still no joy.

Is there a way to set the RowSource property to an SQL stored procedure?