Solved

db crashes when reset in vba

Posted on 2013-01-28
19
280 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

920 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

20 Experts available now in Live!

Get 1:1 Help Now