?
Solved

refresh on form crashes db

Posted on 2013-01-25
22
Medium Priority
?
299 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

743 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