Access hangs, but only on some computers (1 Viewer)

[Skeletal]

A colleague and I have spent a lot of time on this to no avail, so any hints as to what the problem may be are very welcome.

I have developed a fairly complex Access db (not split) that runs perfectly on four completely different machines (some new and fast, some old and slow) using variations of Windows XP.

I have run it in Access 2003, 2007 (both with and without SP2) and 2010, all fine.

On three other machines (all the same: HP 3010SFF, dual core E5400 2.6GHz 3Gig RAM XP pro), using Access 2007, the db “hangs”.

We discovered the hanging was due solely to one table not opening (i.e. all the other tables open perfectly). Thus we stripped out everything except the dodgy table.

We tried all the following on two of the “hanging” machines, but nothing stopped the hanging:

Re-installed Office 2007;
Tried with and without SP2;
Reduced dodgy table by deleting fields and records step by step, to two fields and zero records;
Created new table in a new db by importing data from dodgy table in hanging db, as CSV. The new table in the new db hung;
Cut and pasted data into a new table in a new db. It hung;
Changed the name of the table;
Changed names of field headings;
Stopped all anti-virus software and switched off firewalls;
Changed folder option in Windows to stop automatically looking for network drives;
Changed all fonts to Arial;
Disabled all non-Windows start-up items in msconfig;
Ran “detect and repair”.

On a working machine, I have set the dodgy table up with about 200,000 records (it has 41 fields) and it opens almost instantly.

When the db hangs, the processor runs at 0% for Access. This suggests these computers are waiting for something (hence the ideas for msconfig, AV software etc etc.). Obviously, I don’t have a clue what’s going on!

Thanks for looking.

Skeletal

 

[Guus2005]

Since you volunteer to add the phrase "not split" you know what that means. You are probably the only user of the application/database. You don't NEED to split it.

Having said that, you could try hitting control-break when the computer "hangs". When the computer is responding, is should bring you to the code it is executing or waits until the query is run.

HTH

 

[gemma-the-husky]

what do you mean - a table not opening?

why would a table open?


if its not split, do you mean its a different standalone app on each PC - or is there some shared data?

 

[Skeletal]

Sorry for the delay in replying, I was out yesterday.

There is only one user and the db is standalone; there is no shared data. The only reason I talked about splitting is that one cause of “hangs” is the network connection between the front and back-ends. As it is standalone, clearly, this cannot be an explanation.

Because another easy explanation of hanging is dodgy code, our tests involved tracking down which area was causing the problem, thinking it would be code. It is actually the opening of a table. We can reproduce the fault in a new db that ONLY consists of the dodgy table (or data imported from the dodgy table). Forget code, queries, forms etc etc, it is just one table.

My meaning of “table opening”:

In Access 2007 or 2010 open up the “Task pane” on the left hand side where you can select the various Access objects.

Double click on a table;
The table will “open” on the right hand side “work area”.

But this “opening” is actually in three visible stages:
1. The outer framework appears and the top left cell and its field heading are highlighted;
2. The first few records appear and fill up the outer framework;
3. Over the next few seconds, depending on size, all the other records will start populating the frame and you will see the right hand scroll bar shrink as more records appear.

The dodgy table stops opening after step 1. The table frame is there, and an area corresponding to the top right cell/heading is selected, but no records ever appear. The hour glass is showing and task manager shows 99% idle.

Sorry for the long explanation!

When running the db for real, any operation that involves the dodgy table will also hang (e.g. a simple query).

I admit I’ve not tried control-break, but I’m not sure what it would show because, as I say, there is no code running in the test db. If I did it when running for real, I think it would just show code that had stopped, waiting for the dodgy table/associated query to open/run. I’m not at the computers for a while so can’t quickly try it.

I appreciated this problem sounds impossible! Between us, my colleague and I have built many dbs and have never seen anything like this before.

Thank you both for your interest.

Skeletal

 

[DCrake]

How many records are in this "dodgy" table?

To me it sounds like you have some currupt records in there.

for testing purposes create a blank mdb and copy the definition of the table into it. Compact and Repair
then append the records from the suspect table.
try and open the table from there

Are there any issues?

If not copy the table back to the main mdb and try to open again.

 

[Skeletal]

This was one of the tests we did. In the end we got the table down to zero records and two fields, and that was it for one of the test dbs! And another test was to import data as a CSV into a test db.

But also, don’t forget that on all the other computers, the full original table opens in the blink of an eye. So it has to be a “dodgy record” that does not exist, that only affects one make of computer.

It’s as though there is something else in the table, that the computer uses, that upsets it (and that is transferred in a CSV file as well). This is why we changed fonts, because when Windows renders a font it does not recognise, it can cause really strange problems. I know this was a million to one shot because all the other tables open perfectly (using the same fonts).

And, another test db with 100,000s records will work perfectly on the dodgy computers. It is only the combination of the table, or an “image” from the table, on just three identical machines.

To say we are clutching at straws is an understatement!

Skeletal

 

[DCrake]

How many machines are at fault? If only one, is it the same logged in person who is experiencing the problem?

If so, have you tried looing in yourself and trying it? Could be to do with their profile.

 

[Skeletal]

Three machines are at fault, five are fine (we recently tried it on another). We haven’t set them up for different users; whenever they are used it is as “Administrator”.

We keep wondering what it is about the machines and I did wonder if it was some junk running in the background, which is why I disabled it all using msconfig.

But why do the dodgy machines run other dbs without a problem. What on earth can be in a small empty table (eventually) that stops just these machines from working?

As I say, it makes no sense at all to us, which is why I posted here. I was hoping for “Ah yes, the well-known xyz bug. You need to open Access whilst standing in a bucket of water”! (I’ve not tried that yet, perhaps I should!)

Skeletal

 

[DCrake]

Hey if life was so simple then this website would not exist. Alot of solutions come from trial and error and past experiences from other members. It may be that you are experiencing a new issue. Or someone who knows about this issue has not read your question.

As a matter of interest send me two mdbs.

1. with a table that works
2. one with a table that does not.

There is no need to include anything else, and only a few records for trial purposes only, anonomise if you need to.

This is just to see if I can replicate this at my end. It's worth a try at least.

 

[Skeletal]

That is very kind of you. I’m not working at the dodgy computers’ location ATM (50 miles away so not a five minute trip), neither is my colleague (typical!). I think he is back on Friday so I’ll talk to him.

Forgive my stupidity, but to where do I send the db? Would it be to the xcraftlimited address?

I also need to try it on yet another computer I have at my current location. I’m also thinking of bringing over one of the dodgy ones to keep tinkering.

The stupid thing now is that it would have been more cost effective to buy three more new computers (not HPs!), but I hate failing to sort out problems!

Skeletal

 

[DCrake]

No you can upload here using the attachments icon (paperclip)

 

[Skeletal]

Ah, I see it!

I'll try to do this on Friday, depending on what my colleague is up to. I actually want to get the files off one of the dodgy computers to be sure it is the right one as on my machine here, it all works of course!

Skeletal

 

[The_Doc_Man]

If reducing the table size to 2 fields and zero records doesn't help, and it is reliably hanging on only one brand of machine, the problem is somewhere in the Windows HAL. The problem is, I don't know how to tell if there is a design flaw on the chip or a bad choice of HAL at load time.

Do you have vendor or 3rd-party diagnostics that you can run on the failing machines to see if a CPU instruction is hanging? Can you run a memory test to see what happens when you tell it to scan all memory?

Also, check the vendor specs for the motherboard to see what memory limit applies and verify that the memory configuration is correct. What does your ROM BIOS say you have in terms of memory? Who configured those systems? Did they come with less memory and someone added memory to them? If so, check that they populated the memory slots according to the instructions of the motherboard vendor. For example if you have 8 memory slots but only 6 are populated, it matters very much which two slots are NOT populated.

Now, let's get really hinkey. I looked up the E5400 CPU, which is a P4 Dual Core, but the documentation also mentions "overclocking." Do a system exerciser diagnostic where you stress the system. See what happens there. An overclocked CPU won't always manifest its problem right away. Exercise

One last thought. On the machines that fail, try to open your table but check your watch or the system clock. Let it fail. Do you have to do a Task Manager End Task to shut it down because it is not responding? Do that. Now get into the control panel, find the administrative tools, and look at your system event logs (and other event logs, for that matter) for times at or about the time where you had the problem. See if the system is logging some event right at the time of this hang.

I also note that the E5400 is a 64-bit CPU. Are you running a 64-bit version of Windows on that puppy? I have heard of bizarre behavior when you have the wrong "flavor" of Windows on a machine, though usually trouble happens more often when you have 64-bit Windows on a 32-bit system.

 

[Skeletal]

Wow Doc Man, that’s some info you have there!

I’ve been niggling that it is the machine/Windows rather than Access which is why I did try turning off stuff in msconfig. Beyond that I couldn’t think of other sensible things to do. We did note it was dual core and thought that may be the problem until I pointed out that it works on other dual core machines.

As to your specific points, I’m a bit embarrassed to say “I don’t know” to most of them as we spent most of our time trying to track down an “Access problem”.

It was my colleague who bought the machines so I will quiz him when he gets back. As they are all “works” machines, I expect they will have been bought as standard from a supplier.

I can say when it hangs, we have to use task manager to shut down Access, and the CPU is 99% idle; this was why I was thinking it was “stuck” waiting for something else.

Also, I’ve just tried it on yet another machine, a real old dinosaur; it works perfectly!

As an aside, you don’t realise how fast modern machines are until you try using an old one!

I will be definitely will be looking into these ideas; thanks.

Skeletal

 

[Guus2005]

...
There is only one user and the db is standalone; there is no shared data. The only reason I talked about splitting is that one cause of “hangs” is the network connection between the front and back-ends. As it is standalone, clearly, this cannot be an explanation.
...

That is not exactly true. It is not the connection but the fact that data is transfered via a slow network. So this problem remains when you open the table when you opened the database on a remote location instead of the local computer's harddisk.

You said that on three computers the PC hangs if that table was opened. Perhaps it is a defective network cable?

You can check this more or less by monitoring the traffic in the task manager. (ctrl-shift-esc|tab networking). Time of day is also an important factor. Try it at lunch/dinner time.

The above ideas are hardware related next one is software related.

Try recreating the dodgy table from scratch. First remove the table then compact and repair and then recreate it.

HTH

 

[Skeletal]

Sorry Guus2005, I’m not sure I follow your logic. As I say, the db is completely standalone, the db and all its data is on the C drive, no network is involved. In fact the dodgy computers are not actually on a network.

Bizarrely, one of the crazy ideas (and a bit related to The Doc Man’s ideas) is that NOT being on a network may be an issue i.e. if Windows, for some strange reason, decides it want to look at a network, and it isn’t there, it just stops. This may show up from the event viewer.

We did a lot of experiments making up dummy tables. All would work accept any that had any influence from the original (i.e. importing data etc.). Having thought about peoples’ ideas on this thread, one thing we’ve not tried is to import NO data from the dodgy table once it has zero records. I’ve never tried to import zero data (stupid idea really!) so Access may not let you do it anyway. (We imported the actual table, which still hung, but not zero data from the table, if you see the difference).

It would be fun to have a working table, import nothing, and then for it to hang!

I think I need to bring one of the computers from their current location to my home and devote my life to it for the next few weeks.

Skeletal

 

[gemma-the-husky]

out of interest, are you running any code at all. any timers set, or anything like that, in any open form, at the time you try to open this table?

secondly, delete the dodgy table, and import a good one from one of the OK versions. See if that helps.

-------------
and just to clarify again. Are all users running their own copy of this database on their own PC? Or are they all using and sharing the same copy on a server?

 

[Skeletal]

On the real database there is a stack of code and multiple forms. Originally, the obvious answer was that some of this was going haywire. That is why we eliminated everything down to the one dodgy table.

One of the test dbs thus became the dodgy table, nothing else. All we do on this test db, is open Access, then try opening the table, which hangs. And, ultimately, this single table had two fields and zero records, that’s it, the entire test db, is just two fields and zero records!

Any test db, and the real one, will work fine on six machines, any test db and the real one will not work on any of the three dodgy machines.

As to the broader question of “is there anything else running”, then in terms of what we are doing, no. But, it could be that Windows is running something we don’t know about.

There is actually only one real db, on one working computer that one user uses. It is in use up to 12 hours per day 5 or 6 days per week without a problem. All this fuss has occurred because, shortly, the users want to move the db on to another computer, and carry on working from that one. It is so mission critical that we said we would do a test run to make sure it is still all OK. I mean, running Office on one machine will be the same as any other won’t it??!! Much to our shock and amazement we hit the problem. This started the quest, because there are three “spare” computers but they are all unfortunately the HP ones, and as per this thread, the db won’t work on any of them. Getting some stick from the users, I then demonstrated the db working on six other assorted machines without a problem. (These other machines can’t be used permanently for the users).

Skeletal

 

[dfenton]

This is a long thread and I skimmed through it quickly, but is there a memo field in the problematic table?

 

[Skeletal]

Yes, the thread is getting worryingly long; thanks to all who are still offering suggestions.

A very good point about memo fields. Yes, the real db has one, but the users limit the text to a few dozens of characters.

During the culling process to get down to the remaining two fields for the test dbs, the memo was removed. The two fields remaining are an auto-number ID and a text field.

Oh, and one test we did was to allow Access to generate a new auto-number ID field just in case the existing one was corrupt. Needless to say this did not cure the problem either!

I’m trying to contact my colleague to update him on this thread, but he’s not answering his phone….I think he must have emigrated!

Skeletal