Solved

db crashes when reset in vba

Posted on 2013-01-28
19
279 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 84
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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now