SQL query

[Andy Tag]

I recently created a form that consist of several list boxes. When the form is opened Several SELECT queries run. I have been receiving feedback from folks that use the new form that sometimes they have to wait up to a minute before the form loads. It is not always the case sometimes it milli seconds. According to our DBA I learned that the database/table locks when a SELECT query is run. She said she could override this on her on, not sure if that is the best option or should I look for a method to close connection after the SQL SELECT query runs. Your suggestions are greatly appreciated.

 

[Ranman256]

you shouldnt need a connection. The tables are linked into the db, correct?
so the query for your list boxs run on the linked tables.
It should not take any time.

 

[Andy Tag]

Thanks, you are correct they are links. Any suggestion what could be causing random delays?

 

[Minty]

How are the end users connected to the database?
Is it a split front end / backend database?

 

[Andy Tag]

I am new to Access and SQL, it appears to be front end. I am linked to the SQL server.

 

[Ranman256]

SQL svr sometimes CAN cause a delay. Ive had this. depending on others using it, etc.

 

[Minty]

Are you connected over a LAN or WAN to the SQL server? WAN is certainly likely to cause you some issues.
Can you post up the queries or a picture of them to see the complexity?
Finally if the queries are complex you can create the queries as views on SQL server so only the results are shipped over the network to Access - this normally significantly speeds things up.

 

[Andy Tag]

Minty thanks for the response. We use LAN connection. Once I figure out how I can try your view query suggestion. Thanks.