?
Solved

db crashes when reset in vba

Posted on 2013-01-28
19
Medium Priority
?
287 Views
Last Modified: 2013-02-22
In one of my databases whenever I reset the project in vba after an error the entire db shuts down.

Can someone help me construct a checklist to find out why the db is closing after every reset?
0
Comment
Question by:Shawn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
  • 2
  • +1
19 Comments
 
LVL 75
ID: 38828979
Have you tried the Decompile procedure ?
0
 
LVL 1

Author Comment

by:Shawn
ID: 38829005
wow, thought I did but I guess it was a while back.
That did the trick!

Is that common? It seems to reoccur once I've been coding for a while.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38829013
spoke too soon. still crashes on a form when I end the procedure
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 1

Author Comment

by:Shawn
ID: 38829034
for example. I've set up a subform to show recorded errors. When I click on a button "view" which opens that record it throws an error if there are no errors in the subform (logical).

the error then reports "Syntax error (missing operator) in query expression '[ErrorID]='."

When I click reset the db closes.
0
 
LVL 75
ID: 38829059
Didn't Cap1 have you replace a Form last week?  Or was that someone else ?
Have you tried creating a new, blank db ... then importing all objects (except Linked tables) ?

mx
0
 
LVL 1

Author Comment

by:Shawn
ID: 38829080
yep, that was me.
created new db and all the rest of in the list you guys helped set up.

this bug has been ongoing for ages. gets frustrating while coding and even more so when people are trying to work with the db.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38829090
I'm wondering, it's a pretty old db and there is a lot of unused code and even blank procedures. Is there a way/tool to clean up the db?

this might fix numerous quirks.
0
 
LVL 75
ID: 38829165
Do you have Rick's Find & Replace ?

http://www.rickworld.com/products.html#Find and Replace 9.0

This has a xReference feature that will help you find unused objects & code.

And this excellent product as well (I have both):

http://fmsinc.com/MicrosoftAccess/BestPractices.html

Pretty much one of these tools will be necessary for the cleanup ...
0
 
LVL 1

Author Comment

by:Shawn
ID: 38829201
I do have the Replace product but have never tried it for that type of replace.

I'll look into it and get back.

cheers
0
 
LVL 75
ID: 38829232
Cross Ref:

F
0
 
LVL 1

Author Comment

by:Shawn
ID: 38829258
ok, I have this window open but cannot see how to find unused object and code.

could you show me an example?
0
 
LVL 1

Author Comment

by:Shawn
ID: 38829302
>>Total Access Analyzer $299 per version. ouch. I just bought vb Watchdog.
0
 
LVL 75
ID: 38829323
Well, yes a bit more ... but ... the data TAA collects on your db is simply amazing - seriously, the full time I ran a full scan ... wow!
0
 
LVL 1

Author Comment

by:Shawn
ID: 38829338
ok, I'll put it on my list. Looks good though it would be nice if they gave a discount :)
0
 
LVL 85
ID: 38830123
still crashes on a form when I end the procedure
Does it always crash on the same form, or does any form produce the crash?

If it's always the same form, then you may just have a corrupt form. Did cap have you REPLACE that form with a NEW form, or did you replace it with one from a backup?  Sometimes the backup is corrupt as well, and the only recourse is rebuilding the form from scratch.

If that doesn't work - does this happen on all machines, on only on your machine? If it's on all machines then the trouble is likely with the database, but if it only happens on your machine, then be sure that you've fully updated Access and Windows.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38832094
>>Does it always crash on the same form, or does any form produce the crash?
any form in the db crashes.

>>does this happen on all machines
yes, we use this db in Windows xp and TS on Windows server 2003. We use Access 2002, sp3 and the db is in the 2000 format.

So it happens on all forms and at least 2 different platforms.

I'm wondering if maybe Access 2003 or 2007 would be more stable. Thoughts?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 38832398
I have no idea if it would be more stable, but given that Access 2002 is 5 versions old, you're sort of working against yourself.

If you want my opinion, it's loooooong past time to upgrade. If it were me, i'd move up to 2010 ...
0
 
LVL 1

Author Comment

by:Shawn
ID: 38832503
ok, I'm going to try that. we only have licensing up to 2007 right now so I guess 2007 will have to do.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38857148
For laughs, try creating a blank new database and importing all the objects.

But like LSM, I would consider upgrading.

Windows and Office share a great many system files.
So most updates to Windows now, may not support a 10 year old Access format...

Access 2002 has long past out of it's support cycle...
http://support.microsoft.com/gp/lifeoffice#Office

JeffCoachman
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

649 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