SQL Server Performance Forum – Threads Archive
Performance using Access Client
Hi, My question is a application performance issue so maybe the wrong forum. I have a SQL Server 2005 Express database and connected Access 2003 clients. The performance seems to have degraded since moving from SQL 7. The users are seeing delays when clicking on menu optiosns that have open bound forms in Access. So I have been using the Profiler to have a look at things. I am filtering events SQL:Batchcompleted and RPC:Completed with duration > 10ms. I found some issues with the forms loading without filerting rows so i have changed itso it does not load any rows on opening. Anyhow some users say that they are still experiencing delays from the menu. So my question finally is if I see a delay on my menu but no corresponiding spike in the profiler can i assume that the problem is with Access and not SQL. Any help would be great. rotsey
quote:The users are seeing delays when clicking on menu optiosns that have open bound forms in Access.
Not really clear what you’re saying … Users are opening bound forms, and they do this by clicking on menu options?
Anyway, when you open a bound form directly from the database window in Access (F11) you should see the same response time as when going through menus.
If the form is based on an Access query, then check performance of the query by itself. If you are always opening the form with a filter applied, then run SELECT * FROM <query_name> WHERE <typical_filter_statement>, see what that does.
Debug the VBA code in the Form_Open, Form_Load, Form_Activate and Form_Current events, and review all the code that is called from those events. Check which steps take a long time.
One performance trick is to open a global DAO recordset on an ODBC linked table, but not one you actually use, and to keep the recordset open until you close the program. This seems to help a lot for Access.
I feel this is not a SQL problem and might be the network or access related issue, check as Adrian suggested. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
ok i will try those things One other issue you may be able to help with. The access clients are dropping the connection to the SQL db somehow because
after a form is left open for a while the bound controls have #name? in
them and a message comes up "no current record". Any ideas why this would be happening or a way to troubleshoot it. rotsey
If the connection is dropping you should check whether any network issues between the client & server, the error is obvious where the frontend unable to see the relevant record to manage. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>