Debugging

Troubleshooting Access Hanging When Switching to Design View

How I used two low-level tools--ProcMon and WireShark--to figure out why Access was taking forever to switch to design view in forms and reports.

Mike Wolfe

Mar 12, 2022 • 7 min read

Microsoft Access takes forever to switch to design view sometimes.

It seems to happen on forms or reports with one or more subforms or subreports. The issue also tends to involve applications with backend Access tables that are linked via UNC or IP address, where those tables are not available (e.g., production tables that are inaccessible during development).

Five years ago, this problem popped up for me. It was taking more than a minute to switch from Form View to Design View. Getting to the bottom of this problem was no small feat. It involved multiple low-level debugging tools–ProcMon and WireShark–and several hours of troubleshooting. Luckily, I documented the whole troubleshooting process in our FogBugz bug tracking database.

Now, I could simply tell you what the problem ended up being, but that probably won't help you. Chances are you won't have the exact same problem as I did. Instead, I'll take you along on my journey of fixing the problem.

My hope is that by following my process, you can arrive at the solution to your unique problem.

Prologue

What follows is a lightly edited version of my original troubleshooting notes. I decided to leave them mostly intact, so that you could follow along and observe the fits and starts of a typical troubleshooting session when dealing with such a low-level problem.

Steps to Reproduce [3:18 PM]

To reproduce:

Open application on the client network
Open ImportWebPmts form
Switch to Design View
Click on the subform in the top left corner of the form
Wait...and wait...and wait some more

Capturing the Problem with ProcMon [4:28 PM]

This step involves the use of Process Monitor, a utility which I wrote about here.

I believe the issue this time is that Access keeps trying to connect to a network file that it can't find.

If I run procmon to Show File System Activity only and create a filter where Process is msaccess.exe then I see the following:

Notice the full minute between the successful CloseFile on MAILERS.DBF and the first failed attempt to CreateFile \\10.1.1.242\...\M2015.mdb.

A full minute passes before the first "BAD NETWORK PATH."

Another 26 seconds passes before the second "BAD NETWORK PATH."

Finally, it gives up almost immediately on the third attempt that returns "BAD NETWORK PATH."

Capturing the Problem with WireShark [4:47 PM]

WireShark is an open-source network monitoring utility.

It logs network traffic at the packet level (i.e., "packet sniffing"). I use it only on those rare occasions when I need more network information than I can get from ProcMon. I am not qualified to give you any real advice on WireShark usage. My primary goal with this article is to simply make you aware of its existence and provide an example of why you might want to use it.

Monitoring in Wireshark provides a deeper look into what is going on.

The Time column shows the number of elapsed seconds since we started the trace. The first request to 10.1.1.242 occurs 17.9 seconds in to the trace. I'll refer to this as second 0.

At second 0, a request goes out via port 445, Microsoft-DS Active Directory, Windows shares.

At second 1, a request goes out via port 139, NetBIOS Session Service.

These are the two most likely protocols that the linked table will be running over.

Access waits 3 seconds after each request, then retransmits.

Access waits 6 seconds after each 2nd request, then retransmits a third time.

After another 20 seconds goes by with no response, Access gets desperate and tries port 80, Hypertext Transfer Protocol (HTTP). This time Access gets an immediate response and the handshake completes. Access now sends the packet, "OPTIONS /tclaims/taxbilling/Shared/ HTTP/1.1". This request is ACKnowledged, but no data actually comes back (Len=0).

Access waits for about 30 seconds, then responds by announcing that it is closing the HTTP connection ([RST, ACK]).

Access then immediately tries ports 445 and 139 again. Once again, it waits 3 seconds before resending, then another 6 seconds before resending a third time.

Finally, it gives up.

If we line this up with the ProcMon results, we see that the full minute we observed in getting the initial Bad Network Path response corresponds to the amount of time spent trying to connect via ports 445, 139, and 80.

Problem Caused by Uninvolved Tables [4:54 PM]

One thing I should point out is that the .mdb causing these problems is only connected to the following tables, currently:

MBilling
SBilling

Also, the Billing table is connected to:

T:\Shared\TaxBilling\S2015.mdb

There are probably other tables connected to unavailable network drives that are not causing these problems.

I believe the root of the problem is that we are using an IP address to connect the table. Because of this, we are forced to wait for the TCP timeout every time.

The MBilling and SBilling tables are not involved in any of the forms that we are currently opening. So Access is doing something where it is trying to read some piece of information from the source file for every linked table in the database.

Who knows why it is doing this, but it is very annoying. It means that we need to be really careful about linking any table on IP address, even if the table is rarely used within the program.

New Idea: Remove the Problematic Tables [5:05 PM]

Since the MBilling and SBilling tables get re-connected before being used (via the AttachYear() function), I'm going to try simply deleting the links to see if it speeds things up.

Dropping the Links to the Problematic Tables [5:12 PM]

It took probably over a minute to delete the MBilling table. I probably was waiting on the TCP timeout again. However, the SBilling table deleted instantly. Perhaps Access remembered that it was not worth trying to connect to 10.1.1.242 that time.

I then tried opening the Import Web Pmts form in Design View. It took about 10 seconds to open. Still slow, but much faster than the minute and a half it was taking before.

I thought I should try a Repair and Compact in case there were still some vestiges of the old linked tables hanging around in the system tables. The repair and compact itself took well over a minute and then I got the following message:

This is probably unrelated to the issues with TCP time-outs, but I thought it worth preserving here.

Identifying Other IP-Linked Tables [5:22 PM]

There were still some other tables that were connected by IP address. I ran the following code to identify them:

Sub listTables()
Dim td As TableDef, Cx As CnInfo
    For Each td In App.DB.TableDefs
        Cx = ParseConnect(td.Connect)
        If Cx.Database Like "\\*" Then
            Debug.Print Left(td.Name & Space(20), 20); Cx.Database
        End If
    Next td
End Sub

This produced the following list:

MTownship \\10.1.1.242\tclaims\taxbilling\Shared\M2015.mdb
Postings1 \\courthouse\Redir$\bgrandjean\desktop\DeletedRecods.mdb 
School    \\10.1.1.242\tclaims\taxbilling\Shared\S2015.mdb

Deleting Other IP-Linked Tables [5:26 PM]

Again, it took over a minute to delete the link to MTownship. However, it also took a long time to delete the link to School.

Just Saying... [5:27 PM]

When I tried to compact and repair I got the same error message as before about the file already being in use. Just saying.

Error Resolved [5:30 PM]

After deleting all the tables that were linked by IP address, things are much snappier again. It takes roughly one to two seconds to switch into design view now. Much of this delay could be due to the fact that we are keeping table links to other locations' ODBC tables.

I should note that I did not remove the linked table that is linked by UNC:

Postings1 \\courthouse\Redir$\bgrandjean\desktop\DeletedRecods.mdb

It does not seem like it would hurt to remove that link, but it probably would not save us much time either.

Epilogue

What did you think of this format?

Is it helpful to see someone else's troubleshooting process as they are working through a problem? Or is it like hearing about someone else's dream?–interesting to them, but super boring for everybody else. If you liked this format, are there other types of problems you would like to see get this treatment?

Let me know in the comments below!