Hi,
I'm having a speed issue with a form, and wondered if anybody can help me. I'm using Access 2007 as a front end to a SQL server 2005 database.
The record source for my form is an ODBC linked SQL view, called vwStock, which itself is built from three other views. Two fields in vwStock are built using the case statement.
The view runs fine, both in SQL and Access and returns about 800 records. There are no noticable speed issues. It returns the whole dataset in about 1 - 2 seconds.
The record source for the form is vwStock and each control on the form simply uses a field from the view for its control source. All of the calculations are carried out in the view, none of the controls have calculated data as their control source.
However, the form is very slow. It takes about 30 seconds to open, but worse, it seems to refresh periodically. For example, if you try to scroll down the form (only 800 records) it goes away for another 30 seconds to think about it, and if you try and filter the records it does the same. Even if you minimise or maximise the form, it does the same. I assume that it's doing this because recordset type is set to dynaset, but why does it take so long when the underlying view is relatively ok?
I've tried changing the recordset type to snapshot, and this solves the problem, but unfortunately it also makes the form read only. Can anybody think of a solution to this, or at least explain why it might be happening?
Thanks for any help
Colin
I'm having a speed issue with a form, and wondered if anybody can help me. I'm using Access 2007 as a front end to a SQL server 2005 database.
The record source for my form is an ODBC linked SQL view, called vwStock, which itself is built from three other views. Two fields in vwStock are built using the case statement.
The view runs fine, both in SQL and Access and returns about 800 records. There are no noticable speed issues. It returns the whole dataset in about 1 - 2 seconds.
The record source for the form is vwStock and each control on the form simply uses a field from the view for its control source. All of the calculations are carried out in the view, none of the controls have calculated data as their control source.
However, the form is very slow. It takes about 30 seconds to open, but worse, it seems to refresh periodically. For example, if you try to scroll down the form (only 800 records) it goes away for another 30 seconds to think about it, and if you try and filter the records it does the same. Even if you minimise or maximise the form, it does the same. I assume that it's doing this because recordset type is set to dynaset, but why does it take so long when the underlying view is relatively ok?
I've tried changing the recordset type to snapshot, and this solves the problem, but unfortunately it also makes the form read only. Can anybody think of a solution to this, or at least explain why it might be happening?
Thanks for any help
Colin
