[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Access front end issue, form hangs randomly with sql back end - need troubleshooting help

Posted on 2009-02-24
Medium Priority
Last Modified: 2013-11-29
History \ original configuration
Access DB 2000 format was running access 2002 front end and back end with no issues supporting  about  12 users, 4-6 max running at a given time
Moved back end to MS SQL server, SBS 2003 premium so I think its SQL 2000 server, we are using ODBC connection on a machine DSN.
Most clients were accessing the DB in Access 2002 on a Citrix PS 4.0 running on Win std 2003 SP2 server, some clients use XP SP2 with access 2002

The Problem
We have a 1 form that randomly locks in Access on the Citrix server. This could be via RDP or Citrix ICA session and takes 25% CPU when it hangs, this happens moving from record to record or when editing  and saving, tested on a XP workstation and it has not locked when testing.

Things tried
Check for latest MDAC and have re-install MDAC 2.8 SP2 - still hangs
Changed Access to 2007 on the Citrix server and company - still hangs
Turned on tracing for the ODBC session and didn't find anything there to help
Never hangs on an XP SP2 workstation when we have tested but most of the work is done on the Citrix server.
I have made a copy of the front end and changed the version from Access 2000 to 2007 - Im still testing that to see if it locks

What can I do to troubleshoot \ debug what is happening so I can resolve the problem?

Thank You in advance for your time
Question by:CGPalmDesert
  • 6
  • 3
  • 2
  • +1
LVL 29

Expert Comment

ID: 23724603
If you step through the code, you can find where it hangs, but that may or may not help. So many things can go wrong; it could be a faulty NIC, a broken cable, some kind of network collision, bad code, bad timing, etc.

LVL 85
ID: 23724674
Have you viewed the Event Log on the Citrix box?

Author Comment

ID: 23724988
There are no issues recored in the event log for the trouble, we have to kill the process or reset the session
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

LVL 85
ID: 23725062
So it's the same form each time? Anything odd about this - any ActiveX controls on the form? Does this form work with a large dataset?

Author Comment

ID: 23725221
Badotz - this ran fine for years with access front and back end,
DB files live on the SBS server where the sql back in now located,
Trouble happened after moving to SQL back end.
No other apps on the Citrix server have an issue so we can probably rule out network connectivity.

LSM - Same form, only mods made to front end when we moved to SQL back end was:
Adding a decimal place on a field from 1.23 to 1.234 DB already had the field
Changed 2 time fields to a text field - long story but doubt it has anything to do with it.
No Active X
Dataset comes from a filtered query that is run before the form is brought up.

Anyone know of a way to track where \ why it's hanging

Thanks for the response
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 23725252
Access doesn't provide much in the way of monitoring what's going on with the FE ... you could add some MsgBox's to your Form's Code and see where it's hanging (for eg, add a Msgbox to the Load event, one to the Current event, etc and see where the thing stops).
LVL 29

Assisted Solution

Badotz earned 100 total points
ID: 23725329
If it fails consistently on a workstation, step through the code until you find the statement that fails.

Author Comment

ID: 23727467
Mainly on the Citrix server ( I say that becsue it has not happend on a PC and the users don't use the program in house like they do on Citrix) It randomly hangs navigating from one record to the next, when we open the form the query is set to ask for an input record number, for example we input record 200808036 and it has 30 record associated for the main record number, I can page through the record and it randomly just hangs.

I can see about adding some message boxes and see if that helps in tracking down the issue, any other idea's ?
LVL 30

Expert Comment

ID: 23752676
>> I can page through the record and it randomly just hangs
So is the form bound directly to a ODBC linked table, or is it all loaded through code? If its loaded through code, there may be some properties you can adjust to do with how much data is brought to the client and how much is brought. i.e. it might be currently just grabbing the first 10 records, then when you go to 11 it goes back to get the rest and thats where the issue is. I haven't done any programming for a while but I seem to recall some properties along these lines.
Is the record number prompt managed by code or is it some kind of embedded parameter in the source SQL?
Are you using pass through queries or is it driver totally by ODBC linked tables with MS Access queries over the top?

Author Comment

ID: 23759692
The form is bound to a query (see query below) that is bound to the ODBC linked tables, record type is dynaset.

The query has a prompt (Enter Task Number) that asks you for a record input and then brings those record back and those records rarely exceed 30

SELECT [Utility Hole Information].TaskID, [Utility Hole Information].SD_PH_No, *
FROM (Clients INNER JOIN [Task Information] ON Clients.Cust_Id = [Task Information].ClientID) INNER JOIN [Utility Hole Information] ON [Task Information].TaskID = [Utility Hole Information].TaskID
WHERE ((([Utility Hole Information].TaskID)=[Enter Task Number:]))
ORDER BY [Utility Hole Information].TaskID, [Utility Hole Information].SD_PH_No;
LVL 30

Accepted Solution

nmcdermaid earned 700 total points
ID: 23766316
So the object 'Clients' and the object 'Utility Hole Information' are both seperate linked tables which you can see in your MS Access Table list?
I suggest you:
1. Create a view on the SQL server which performs the join above
2. Ensure that you have indexes on ClientID, Cust ID, Task_ID (not so important if you have less than a couple of thousand records)
3. Now use the view rather than the two tables in your query. Note that the view will not contain the 'WHERE' part. Your query on the MS Access side will.
4. Have a think about whether you REALLY need the * in your select list - you should only bring back columns you require.
As I understand it, MS Access will currently bring back all of the data from those two tables and perform the join on the client side. Its mich better if SQL Server does all the work and only sends back the data you require.


Author Comment

ID: 23780046
Thank you for the input, I will have to schedule time to test some of the items presented and I will post back what I find.

Author Closing Comment

ID: 31550685
Thank you for all your input, the problem is no longer happening and when it dose I will take the steps give to help track down the trouble.

Thanks for your input

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question