MS Access apparently extremely unstable

[XV1957]

Access extremely unstable on my PC

I need some urgent help or advice.
Thanks to good advice from some participants to this forum, I was well on my way to developing an ERP in Access.

This morning I have had a number of crashes in a large program. When that happens, I comment out every line and then restore them line by line, taking care of the logic elements first (restore if and end if, with and end with, whereby each one is indented perfectly and commented).

What particularly irritates me is that I was able to release entire blocks of code after testing them for absence of crashes, until new crashes came up. When I then commented out again my latest corrections, what used to work now also caused a crash. I could make no sense out of this weird behavior.

Fed up with this, I decided to restore a back up database. Making a test copy of the back up, I tested the program in question with some fake but realistic data, and it worked. Great.

Now I need to make some small changes in the software. I barely opened the Visual Basic Program window without making any change, and this version now crashes again.

What is wrong here? I am losing confidence in this program, which really sets me back months.
It seems I am up against something that defies a logical approach.
Does anybody have an idea? Thanks in adavance

 

[Minty]

You need to supply a lot more information, how large is large ?
Do you re-compile it after adding new code ?
How much code is there ?
Is this a Split FE/BE ?
Have you tried it on another machine ?
Have you tried re-importing all the objects into a fresh empty db?

 

[XV1957]

Hi Minty,
Being self taught in Access (fortunately not in programming) I know I am missing out on a few things.
Was not even aware you could compile a program, but I will certainly start doing this.
That certainly might take out some loose ends which I have left floating around and that could have an incidence.
Thanks a lot for the advice, which gives me the courage and better tools to work on.
If that does not help, I will revert to you.
Have a nice day

 

[gemma-the-husky]

are multiple users sharing the same database front end concurrently, by any chance?
is the database split.
are you using wired lans

a split database, on a wired lan, with individual fromt ends is very very stable.

 

[JHB]

..
Now I need to make some small changes in the software. I barely opened the Visual Basic Program window without making any change, and this version now crashes again.
..
Does anybody have an idea? Thanks in adavance

I would also try decompile!
http://www.fmsinc.com/microsoftaccess/Errors/Bad_DLL_Calling_Convention.asp

 

[XV1957]

Hi gemma-the-Husky,
No, this is a simple db on one single computer.
Today I copied forms from my test database to my live one, compiled the live database with no issues, ran the same program and the whole thing crashed again.
I then compacted and repaired the db, ran the same program without any change and without any issue, and it worked. Not something that gives me lots of confidence.

 

[jdraw]

Get yourself a couple of free utilities to make things a little easier.

MZTools
Smart Indenter

Do some research on Compact and Repair.
Read this article from FMS

For database design see this

For general Access/database info see Crystals Access Basics

 

[cyberman55]

After 20 years with this thing - I can say the single most important thing I've found was to import all objects into a new database container whenever odd things started happening and decompile/recompile didn't work.

I learned this technique from an actual Microsoft support person back in 1995 when ordinary users could call and reach someone in Redmond. Those were the days...

 

[Minty]

When it crashes what exactly is running - we run two fairly intensive business databases in access front ends, with literally 1000's of lines of code and it's very stable (Well until I break it trying to be too clever...).

Is your data stored in a Access BE or SQL server / other db?

Do you have AutoName correct switched on ? It causes no end of issues on any of my databases, switch it off and back to sanity.

 

[XV1957]

To jdraw, thank you very much for your fast response.
I will look into the issues you point out right away.

 

[XV1957]

cyberman,
thank you for your message. Something to definitely consider if things become problematic.

 

[XV1957]

Hi Minty,
thank you for your reply. I had Name autocorrect on, so now switched it off.
I am using Access as a signe user database, front end and Access database all in one.
The plan is to migrate to SQL server or similar, as soon as we have a server.
The crash has been happening when I press a command button that activates my largest program (in terms of lines and recordsets involved).
It seems to happen after I copied forms from my test db to my live one, even after a recompilation the db that brings no issues to light.
After compacting and repairing (of which I know nothing) and without any change whatsoever to the software, the same button causes no crash and the program does its job as intended. Which is the annoying thing really.

 

[XV1957]

Hello JHB,
just went through the thread you sent me, and it looks very promising.
It sure looks very useful. Thank you very much for taking the time to share your knowledge. Have a nice day!