[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

refresh on form crashes db

Posted on 2013-01-25
22
Medium Priority
?
301 Views
Last Modified: 2013-01-28
Whenever I refresh on a particular form it crashes the entire db. I'm pretty sure it's corrupt or at least haven't been able to find out why.

can someone help me with maybe a checklist on how to resolve this?
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
  • 8
  • 3
22 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 800 total points
ID: 38821002
Here is the 'checklist' :


Open the VBA Editor and from the menu ...Tools>>References ....
If you see any listed as **Missing: <reference name>, including the asterisks and the word Missing, the
you need to fix that first.

Then, follow this procedure:

****
0) **Backup your MDB BEFORE running this procedure**
****
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
Close the mdb after the Compact & Repair.
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Close the mdb.
6) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

7) Close the mdb
8) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an **example** of the command line syntax  (be SURE to adjust your path and file name accordingly) before executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:
Also, the double quotes are required.

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\YourMdbNameHERE.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

AND ...
Once you get familiar with the Decompile idea (and ALWAYS make a BACKUP first!) ... you can add both Decompile and Compact/Repair to the Right Click menus in Windows Explorer, which I use multiple times daily:

Getting the Decompile and Compact context menu options
http://access.mvps.org/access/modules/mdl0039.htm (note:link my be temporarily broken - if so, let me know and I can upload).

mx
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1200 total points
ID: 38821007
try doing this
do first a decompile
see this link  http://www.granite.ab.ca/access/decompile.htm

then do a compact and repair

if the problem persists
create a blank db and import all objects


if the problem still persists,

you need to create a new form.

to make it easy, open the form in design view
select all the controls, Ctrl + C (copy) then paste them to the new form
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821021
mx:
have already done all those steps though I didn't hold the shift key while opening.
Access 2000 db running in A2002 environment. I went through every step again including the hold shift and form still crashes on refresh.

capricorn1:
I've already tried to creat a new form but perhaps I was missing something. I'll try again now and get back with results.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 75
ID: 38821028
Holding down Shift key is just to prevent any start up code/form from running - unnecessarily.

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38821030
you can also save the form as text file with

Application.SaveAsText acForm, "NameOfForm", "c:\folder\NameOfForm.txt"

in the new db, retrieve the form with

Application.LoadFromText acForm, "NameOfForm", "c:\folder\NameOfForm.txt"
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821042
>>if the problem still persists, you need to create a new form.

just did this. Created new blank form, copied all controls and added recordsource, saved. Closed. Re-opened. Ctrl-G copy/pasted procedures. Saved. Compact/repair.

refreesh and still crashing.

will try to save as text now. Question: can I save it as text then import into the Same db? er, I know i can but will this help or is it just garbage out = garbage in?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38821048
did you create the new form in the new db?
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821065
i created the new form in the SAME db and that didn't work. Creating the form in a new blank db now.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821073
created new form in new db with this Application.LoadFromText acForm, "NameOfForm", "c:\folder\NameOfForm.txt"

then dragged and dropped to current (old) db and still crashes
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821078
I even tried earlier to put everything into new db and refresh still crashed it.

Could it be there are too many controls or something?

the system is xp sp3 and I know there were issues a while back but I thought that was resolved.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38821079
don't use the old db, as i've said in my first post, import all objects to the new db.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821081
>>don't use the old db,

done and still crashes
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38821083
<Could it be there are too many controls or something?>

in the new form that you created, start with a few controls

btw, how many controls do you have in the form?

Number of controls and sections you can add over the lifetime of the form or report = 754
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821089
>>btw, how many controls do you have in the form?
not sure how to count but a good 100 at a glance along with 2 subforms.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1200 total points
ID: 38821095
the problem could be your subforms, do a process of elimination
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821098
ok, "relatively" good news. just deleted subforms and about half the controls and no more crash.

Now to work backwards.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38821105
try the loadfrom text, then delete one subform if the problem persists
do it again, now delete the other subform
once you find which subform is causing the problem,
create a new subform
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821111
ok, found the suform causing havoc. Strange thing is when I open it up directly and perform a refresh it doesn't crash.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38821113
it could be the relation of the subform with the main form,  codes called from main referring to the subform.. etc
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821125
holy cow.

got it down to one control, TmpExchangeRate and it turns out it had an empty after update procedure.

Private Sub TmpExchangeRate_AfterUpdate()

End Sub

I deleted this and no more crash. Amazing. I had no idea something like this could cause such a headache.
0
 
LVL 1

Author Closing Comment

by:Shawn
ID: 38827594
I hope I split the points ok. The question was to help construct a checklist. I think it's a great one and will help me in the future too. I also gave points for including checking the subforms. I had no idea they could create havoc like that.

thank you,
Shawn
0
 
LVL 75
ID: 38827694
cool.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

656 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