By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,292 Members | 1,758 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,292 IT Pros & Developers. It's quick & easy.

Forms Slow to Save and go into Design View

P: 171
This is driving me absolutley insane! Every time I want to take a very complex from into design view or save it after amending it in design view, it takes ages to save. All I can think of is that I make all my queries in the Form e.g. if I have a list box I will have saved the query in the row source of the list box instead using VBA listbox.rowsource = "query". What would be the best way to make the forms faster (to go into and come out of design view)???

Please help... before I go bald pulling my hair out...

Some forms just Crash when I save from Design View... "Catastrophic Failure" it says, Catastrophic, I never knew Access had such depth of emotion, but it seems I have made it panicked and afraid, I don't think it will stay on my computer after many more catastrophies.
May 8 '09 #1

✓ answered by iheartvba

I Found the reason my DB was slow to go into design view. It was because I had a network printer set as my default printer!

Share this Question
Share on Google+
46 Replies

Expert 100+
P: 407
Have you tried Compact and Repair?
Also, try not clicking too loudly, you might be scaring the poor little beastie!

May 8 '09 #2

P: 171
yes, many many a time have I gone down the route of compact and repair, I have, but it seems like it Access has been violated too many times it has suffered too many catastrophies, anarchies, wars, famines, pain and suffering then it could bare, I am now just going to revamp the forms and feed them SQL via VBA instead of putting them on the forms, it does make the forms faster, but it takes a long time to revamp the form. My advice: Always Always Always, feed your forms queries from Vba don't save it permanently in the row sources if possible.
May 8 '09 #3

Expert 100+
P: 407
I'm hearin' ya.

May 8 '09 #4

Expert Mod 15k+
P: 31,707
It's not impossible (or even unlikely at a guess) that your database has some corrupted elements in it. Not everything is fixed by a Compact & Repair (unfortunately).

Consider creating a new database from scratch and importing objects into it from this one.

Be careful and test before any objects you have any suspicions of.

It may turn out to be that individual form alone, but I doubt it can hurt to try it out.

Good luck.
May 8 '09 #5

Expert 2.5K+
P: 2,653
To the best of my knowledge RowSource property is nothing more than a string property containing SQL expression. There is nothing resource consuming or unsafe in using them.

"Catastrophic failure". :D
I was impressed too when saw it first time.
As far as I could recall this error occurs when Access fails to communicate with Activex OLE server. Do you have Activex control(s) on your form?

May 8 '09 #6

P: 171
I have just gone on another persons laptop today (which was about as powerfull as my PC) and the the Reports and Forms on his laptop loaded about 10 times as fast as on my PC. We both have MS Access 07. Why would this be, when I go into design view my access takes ages.
Aug 13 '09 #7

Expert 100+
P: 904
I use Access 2007 also and have run across this problem also. I have forms with embedded rowsource queries and also forms that link to queries for rowsource. It does not appear to be the cause though.

I find it slows down after a marathon session of programming from switching back and forth from design mode to form view it slows down. It appears to me that Access keeps allocating memory when going in and out of design mode and form view and doesn't seem to release it properly. It therefore slows right down after numerous switches.

I have been able to fix the problem by cloing access and reopening or rebooting. Keep in mind this happens rarely and after many design/form view switches (sometimes hours).

I don't know if you have the same scenario.

Aug 13 '09 #8

Expert 100+
P: 1,287
I often get crashes when switching from design to form view and back, especially on forms with subforms, so I've taken to just closing the form and then re-opeing it in the other view every time. It's kind of a pain, but it's quicker than restarting Access all the time, and I haven't seen any slowing.
Aug 13 '09 #9

Expert 100+
P: 904
I have never had AC2007 crash on me (fingers crossed). Just the slow down like I mentioned. But it does sound like there seems to be a problem with switching between design/form view since your problem is also associated with that sequence of events.

Aug 13 '09 #10

P: 171
I have just seen an improvement in the speed recently after an update was done to MS Office, still abit slow, but I can live with it.
Aug 18 '09 #11

Expert 100+
P: 378
I had the same problem, on some very complex forms. It was usually a matter of rebuilding the form from scratch that would solve it (some kind of form-bound corruption). In some other cases, I had to rebuild the entire database by re-importing all the objects into a new one..

The one common ground I had on all of these problem databases, is the format was originally 2003/mdb, upgraded to 2007/accdb. After re-importing everything into a blank ACCDB, they tend to behave for a while.

On another note, I'm beta testing office 2010. I loaded up one of my bigger databases, did a compact/repair, and it permanently removed 20MB off the size. Normally it would get to be about 70mb after a few hours in design.. I would compact/repair and it would drop to 40mb. After the Office 2010 compact/repair, it would go down to 20MB! The database still works just fine using 2007, so maybe there's some extra junk that 2007 stores in the 'Msys' tables that isnt getting properly cleared out.
Aug 18 '09 #12

P: 171
Thanks Megalog,
Just wondering is there a smart way to import forms etc into a new accdb file or should I just do a copy and paste?
Aug 18 '09 #13

Expert Mod 15k+
P: 31,707
The Import process can handle multiple objects, including not just Tables, but also Forms, Reports, Queries, etc.
Aug 19 '09 #14

P: 171
Hi NeoPa the Import process worked brilliantly. The DB is working much better now. The only thing I would like to know is how can I regularly maintain the database so it doesn't get to the stage where I need to import the Items into a new database shell.

Aug 31 '09 #15

Expert Mod 15k+
P: 31,707
I'm not really sure TBH. I would consider Compact/Repair regularly of course.

Otherwise I suspect you may have something else wrong with your setup which may be allowing these corruptions to occur. As the scope for that is almost infinite there's little I can say to help with that I'm afraid.

BTW When I refer to the setup I'm not restricting this simply to your Access configurations (unfortunately - That would be simpler).
Aug 31 '09 #16

Expert 100+
P: 378
Is this database being used by multiple people at the same time? If so, is it a split (front end/back end) setup? If not, that can encourage corruption to happen.

If it is already split, and the end users are running their own copies of the front end.. then the only other recommendation I can give is to have a good backup system in place. We use a winzip backup, that's run on a pc 24 hours a day with the system scheduler. This way, we can go back to any given date and pull a good copy to restore any form or report that happened to corrupt itself in the master copy.
Aug 31 '09 #17

Expert Mod 15k+
P: 31,707
See Front-End / Back-End (FE/BE) for stuff about Front End / Back End.
Aug 31 '09 #18

P: 171
Firstly to Neopa's point regarding setup:

Backend: access database with only tables

FrontEnd Forms: All unbound forms so I don't think there would be a situation where the database can be close whilst a record is actualy being edited, all edits, additions and deletes of records are done via Update, Append and Delete queries respectivley.

FrontEnd Queries: Most queries are made via Access query builder but now creating much more via RunSql and ADO functions of VBA due to the greater flexibility. Also for very complex queries I first use a Make Table query to make a query into a table and the query that table (if that makes sense), the purpose of this is to increase the speed of running very complex queries mainly for reports (I'm not sure if this could be causing corruption)

FrontEnd Reports: All made via Access Report Builder

3rd Party Products: The only 3rd party product being used in the database is Eztwain which is an addon to be able to Scan via VBA.

Now to answer Megalog's comment:

1. I have split the DB

2. I do do backup's but when it got corrupt, I didn't want to revert to the last working backup, because it may still have been partly corrupt, so I thought might aswell overhaul the whole thing into a new shell. Any suggestions whether I was right in thinking that?

The main thing I can think of that would corrupt things is that many times when I am designing a Form, Report or especially a query, access just hangs, then I have to shut it down via Ctrl+Alt+Del, and that happens a lot when I am designing something new.

Thanks Guys
Aug 31 '09 #19

Expert Mod 15k+
P: 31,707
Having no experience of Eztwain I would suggest checking that out (try without for a while).

I should also bring to your attention that the setup is not just that which runs within Access, but also to that within which Access runs. Hence the comment about it being almost infinite in scope. It may be worth testing doing some of your development on an alternative PC.

Obviously we cannot hope to take you through this as it is specific to your own PC environment about which we can know very little.
Aug 31 '09 #20

P: 171
Fair Enough, I agree w/you 100% I have just got a new PC about a month ago which is much faster, and obviously now it crashes less because the PC can handle the load much better, so I guess much less chance of corruption.

Aug 31 '09 #21

Expert Mod 15k+
P: 31,707
I wouldn't expect the speed or performance to have so much of an effect regarding corruption, but a PC after all, is an enormous container of so many configuration options. Who can ever know that their PC is set up without any problems. I know mine have some - I just try to keep them to a minimum.
Aug 31 '09 #22

P: 171
Just on the note of doing a compact and repair on a regular basis: Is there a way I can Automate this so Compact and Repair is done @ a specified time every night, and more importantly would this be recommended, I mean would it be harmfull to do Compacting and Repairing daily?
Aug 31 '09 #23

Expert Mod 15k+
P: 31,707
It can be done. I would recommend making a backup copy at the same time. Sometimes, on rare occasions I believe, the Compact / Repair can corrupt your database. This may only be in older versions, but I would. Just to be safe.

Would you like me to dig up something on automating from the OS Scheduler? It does involve some fiddling in code as well as command line options etc.

I suppose with a tag like yours you won't be afraid of some coding ;)
Aug 31 '09 #24

P: 171
Hi NeoPa,
Yes! Defiantley I do heart vba, Bill Gates didn't become rich by accident Ms Access and the whole MS office suite is really something amazing, and an empowerment for small business to be able to compete with the big guys.

With my database, it is currently used in multiple sites so I can't compact and repair them all my self. Everyone seems to recomend doing the compact and repair on a regular basis so I think it would be worth the time spent on it. What would you suggest?

in short I don't mind the coding, if you think it is a good idea to do compact and repair on a regular basis, then yes, I would love some direction on how it would be done.
Aug 31 '09 #25

P: 171
Also NeoPa, in relation to your comment #22 of this post, where would I start looking of config issues of my PC? Also it's just on a peer-to-peer network so would it be an idea to get a Server OS? I would prefer not to as we don't want to increase our IT costs.

Aug 31 '09 #26

Expert 100+
P: 378
See now, here is where I'm still a bit confused..

You say you have a split db.. yet you want to automate a compact repair on this? But your corruption lies in the front end, not the back end data. Are you distributing copies of the front end to all users, or are they all running the same networked front end file? That simultaneous usage of the same FE file is what could cause the corruption, in my mind.

You should have a design master FE file.. it can link to the same live BE or a test copy of the BE file. When you update the FE, then you would push out a new version of it to a client accessable area, preferably in a read-only format (MDE/ACCDE). Doing what you can to minimize the chance of corruption occuring in the first place should be your top priority.
Sep 1 '09 #27

Expert Mod 15k+
P: 31,707
Let me respond first to this post as it is a little off topic (although relevant it won't help to discuss it here).

This is such an open-ended subject that I'm afraid the best advice I can give is to keep your eyes open. You can post a question in our Windows forum if you like, but as the symptoms are so unclearly defined, I feel the best you can expect is some general good advice. I certainly see no obvious solutions you could get from the web.

Alternatively you could consider getting a professional in to check it out, but that costs, and with such problems there's no guarantee they'll find the actual problem anyway. They're likely to offer you some good advice though (if they're any good) which will probably help even if they don't manage to find the specific cause(s) of your current problem.

I'm sorry I can't be more help with this aspect.
Sep 1 '09 #28

Expert Mod 15k+
P: 31,707
Before I proceed with the automation of the Compact / Repair, Megalog makes a good point. Can you say how your FE & BE are set up, both for the development stage and the roll-out (where do your users find their databases)?
Sep 1 '09 #29

P: 171
I Develop the Front End on my PC then when it is ready for distribution, I place a Front End on each users desktop which links to the bank end (No User shares a Front End With another User). all the Front Ends link to 1 back end. The link is done programatically with the network path saved in a table.

Sep 1 '09 #30

Expert Mod 15k+
P: 31,707
Ah, that's clear.

I have found the example I need, but I will need to do some work on it to allow it to make sense.

My process involves the scheduler running a CMD file which in turn invokes Access with the database and a parameter which is passed to the database .

On open, the database checks the parameter and behaves accordingly. That way you can have normal use as well as automatic jobs.

For a fuller explanation I'll have to do some work to make it more palatable ;) If you can use this to make a start though then all the better.
Sep 2 '09 #31

P: 171
Hi NeoPa,
I see where you're coming from until getting scheduler to rum the CMD file which open access, and the Access checks it's parameter when it opens, but I don't know how then the Compact and repair would be initiated programatically.

Sep 2 '09 #32

Expert Mod 15k+
P: 31,707
No worries. I have some code for that.

Tell me. Which of the databases gets the corruption. The control (FE) or data (BE) database?
Sep 2 '09 #33

P: 171
Control (FE) get's the corruption, but I'm not sure whether that's during use or during designing (probably designing in my opinion)

I do however want to Compact and Repair the BE on a regular basis as well.

I'm not very good at knowing when a database is corrupt

Sorry, I couldn't identify a corrupt database if it offered me a bribe!
Sep 2 '09 #34

Expert 100+
P: 378
Doing a Compact/Repair on the BE is a bit trickier. You have to wait until everyone is out, or initiate some type of procedure that warns and then kicks all the users out before your process can run.

Identifying a corrupt BE is pretty simple. The records will contain garbage code.. or the data is shifted into the wrong fields.. Or sometimes it'll just simply crash when you scroll down to the corrupted area. Basically when you start to think 'Something is VERY VERY wrong here'.... =)
Sep 2 '09 #35

Expert 100+
P: 1,287
I do a Compact and Repair on startup, but only to save the file as a backup.
Expand|Select|Wrap|Line Numbers
  1. Set cnn = CreateObject("ADODB.Connection")
  2. Set userRecords = CreateObject("ADODB.Recordset")
  3. 'close the connection to the backend
  4.   DoCmd.Close acForm, "ConnectForm"
  5. 'see how many users are connected
  6.   cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
  7.    & "Data Source=" & dbPath
  9. '-1 = adSchemaProviderSpecific
  10.   Set userRecords = cnn.OpenSchema(-1, _
  11.    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
  12.   userCount = 0
  13.   While Not userRecords.EOF
  14.       userCount = userCount + 1
  15.       userRecords.movenext
  16.   Wend
  17.   userRecords.Close
  18.   Set userRecords = Nothing
  19.   cnn.Close
  20.   Set cnn = Nothing
  21. 'if others are connected, can't back up
  22.   If userCount > 1 Then
  23.       'reopen connection form
  24.       DoCmd.OpenForm "ConnectForm", acNormal, , , , acHidden
  25.       Exit Sub
  26.   End If
  27. 'do the compact and repair
  28.   If Not Application.CompactRepair(strSourceDB, strDestDB, True) Then
  29.       Err.Raise 666, , "Unable to back up the database."
  30.   End If
Sep 2 '09 #36

Expert Mod 15k+
P: 31,707
Chip's code looks pretty thorough for handling the BE.

I'm lazier. I trawl through my connected tables to determine all the BE databases and compact them all. If they're busy the compact won't work, but if they're run automatically at a quiet time they generally do. This may not be a good enough approach in various situations but does for ours.

First, the code I use behind a simple command button on my Compact form :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCompact_Click()
  2.     Dim strWork As String, strDBs As String
  3.     Dim intPos As Integer, intLen As Integer
  4.     Dim tblThis As TableDef
  6.     strWork = Replace(conWarning, "%1", conStub)
  7.     If Not CheckOp(strWork, conJob) Then Exit Sub
  9.     Call SwitchMode("Process")
  10.     strDBs = ""
  11.     For Each tblThis In CurrentDb.TableDefs
  12.         strWork = ConnectName(tblThis.Name)
  13.         If strWork > "" Then
  14.             strWork = """" & strWork & """" & vbCrLf
  15.             If InStr(1, strDBs, strWork) = 0 Then _
  16.                 strDBs = strDBs & strWork
  17.         End If
  18.     Next tblThis
  19.     intPos = 2
  20.     Do
  21.         intLen = InStr(intPos, strDBs, vbCrLf) - intPos - 1
  22.         If CompactDb(Mid(strDBs, intPos, intLen)) Then
  23.             intPos = intPos + intLen + 4
  24.         Else
  25.             strDBs = Left(strDBs, intPos - 2) & _
  26.                         Mid(strDBs, intPos + intLen + 3)
  27.         End If
  28.     Loop Until intPos > Len(strDBs)
  29.     strWork = MultiReplace("The following databases were compacted:%1%2", _
  30.                            "%1", vbCrLf, _
  31.                            "%2", strDBs)
  32.     Call ShowMsg(strMsg:=strWork, strTitle:=conJob, intButtons:=vbInformation)
  34.     With cmdExit
  35.         .Default = True
  36.         Call .SetFocus
  37.     End With
  38.     Call SwitchMode("View")
  39. End Sub
This will not work directly in your database due to calls of minor specific procedures, but most of the code is workable and the thread is fairly clear I hope (easy enough to bend to your needs). The specific procedures are generally just oft required shortcuts or timesavers.

The exception, of course, is CompactDb(), which is included below. The same comments apply for this code also :
Expand|Select|Wrap|Line Numbers
  1. 'CompactDb compacts a remote Access database.
  2. Public Function CompactDb(ByVal strDB As String, _
  3.                           Optional ByVal strDBTo As String = "", _
  4.                           Optional strPW As String = "", _
  5.                           Optional blnZip As Boolean = False) As Boolean
  6.     Dim strCmd As String, strLocale As String, strZip As String
  7.     Dim strSrcDB As String, strDstDB As String
  8.     Dim blnTmpDB As Boolean
  10.     On Error GoTo ErrorCDB
  11.     Call Echo(True, "Compacting """ & strDB & """.")
  12.     strSrcDB = strDB
  13.     strDstDB = IIf(strDBTo = "", Replace(strDB, ".Mdb", "New.Mdb"), strDBTo)
  14.     If Exist(strDstDB) Then Kill strDstDB
  15.     If strDB = CurrentDb.Name Then
  16.         strSrcDB = Environ("Temp") & "\" & _
  17.                    Split(strDB, "\")(UBound(Split(strDB, "\")))
  18.         strCmd = MultiReplace("CMD.EXE /C ""Copy %F %T""", _
  19.                               "%F", strDB, _
  20.                               "%T", strSrcDB)
  21.         Call ShellWait(strCmd)
  22.     End If
  23.     If strPW <> "" Then strLocale = ";pwd=" & Scramble(strPW)
  24.     Call DBEngine.CompactDatabase(SrcName:=strSrcDB, _
  25.                                   DstName:=strDstDB, _
  26.                                   DstLocale:=strLocale, _
  27.                                   SrcLocale:=strLocale)
  28.     If strDBTo = "" Then
  29.         Kill strDB
  30.         Name strDstDB As strDB
  31.         strDstDB = strDB
  32.     ElseIf strDB = CurrentDb.Name Then
  33.         Kill strSrcDB
  34.     End If
  35.     If blnZip Then
  36.         strZip = Replace(strDstDB, ".Mdb", ".Zip")
  37.         Call Zip(strZip, strDstDB)
  38.     End If
  39.     Call Echo(True, """" & strDB & """ compacted.")
  40.     CompactDb = True
  41.     Exit Function
  43. ErrorCDB:
  44.     CompactDb = False
  45. End Function
In addition this code will ZIP up the resultant database assuming the WinZip Command Line software is installed.

I suggest you may want to cherry-pick the code that suits you from all available.
Sep 2 '09 #37

P: 171
Hi Guys,
First to MegaLog's Post # 35. Thanks MegaLog, now I know how to check if the Back End is corrupt fortunatley I have never encountered a corrupt back end (knock on wood)
Now to ChipR & NeoPa. Thanks Guys that's brilliant, that will keep me out of mischief for a while. I will have a look at the code and try it out a bit later stage as something urgent has popped up.

Your help is very much appreciated as always
Sep 2 '09 #38

Expert Mod 15k+
P: 31,707
As the whole process of automating a database from outside of Access is a bit of a fiddly one, I created Invoking a Database From the Command Line for you.

I expect I'll refer to it elsewhere too mind you ;)
Sep 2 '09 #39

Expert Mod 15k+
P: 31,707
That's never a problem. The real world can be like that sometimes.

That last bit of work was something I'd been promising myself for a while now anyway. You're not the first to ask for help on similar lines.
Sep 2 '09 #40

P: 171

P.S. You guys are doing a great job here, no other forum even comes near bytes.
Sep 2 '09 #41

Expert Mod 15k+
P: 31,707
Thank you on behalf of all of us.

We do like to help if we can :)
Sep 2 '09 #42

P: 171
I Found the reason my DB was slow to go into design view. It was because I had a network printer set as my default printer!
Sep 14 '09 #43

P: 1
Have searched everywhere for why I had this problem, thank you so much for your advice on the network printer. Pointed to a local printer and the slow performance disappeared
Feb 20 '10 #44

Expert Mod 15k+
P: 31,707
Thank you for registering just to show your appreciation. We're very glad we could help.

I think, with no disrespect to Chip's answer which is currently set as Best Answer, that I will change that to reflect I<3VBA's one, as that seems the most appropriate solution, and is what we want people to find when they're scanning quickly through a bunch of threads.
Feb 21 '10 #45

P: 2
Saving form can be slowed down to tens of seconds or even loger by Track Name AutoCorrect Info option. A have just faced this problem with such form having huge number of controls.
At this moment it is solved by turning off this option in Form_Open event and setting to previous state in Form_Close event:

Expand|Select|Wrap|Line Numbers
  1. Private Track_Name_AutoCorrect_Info As Boolean, Perform_Name_AutoCorrect As Boolean, Log_Name_AutoCorrect_Changes As Boolean
  3. Private Sub Form_Open(Cancel As Integer)
  5. On Error Resume Next 'just in case
  6. With Application
  7. Track_Name_AutoCorrect_Info = .GetOption("Track Name AutoCorrect Info")
  8. If Track_Name_AutoCorrect_Info Then
  9.     Perform_Name_AutoCorrect = .GetOption("Perform Name AutoCorrect")
  10.     If Perform_Name_AutoCorrect Then
  11.         Log_Name_AutoCorrect_Changes = .GetOption("Log Name AutoCorrect Changes")
  12.         .SetOption "Log Name AutoCorrect Changes", False
  13.     End If
  14.     .SetOption "Perform Name AutoCorrect", False
  15. End If
  16. .SetOption "Track Name AutoCorrect Info", False
  17. End With
  18. On Error GoTo 0
  20. End Sub
  22. Private Sub Form_Close()
  24. On Error Resume Next 'just in case
  25. With Application
  26. .SetOption "Track Name AutoCorrect Info", Track_Name_AutoCorrect_Info
  27. If Track_Name_AutoCorrect_Info Then
  28.     .SetOption "Perform Name AutoCorrect", Perform_Name_AutoCorrect
  29.     If Perform_Name_AutoCorrect Then _
  30.         .SetOption "Log Name AutoCorrect Changes", Log_Name_AutoCorrect_Changes
  31. End If
  32. End With
  33. On Error GoTo 0
  35. End Sub
Obviously you can do it manually, permamently.
The only thing very, very disturbing here is detaile reson of that slow down.
May 21 '12 #46

P: 2
The code above works fine in Access 2002, but in the Runtime 2010 Form_Close event generates

Error 31556
To view object dependencies or change the Track Name autocorrect
option, Microsoft Office Access must close all objects and update
dependency information.

Changing Track_Name_AutoCorrect_Info As Integer, Perform_Name_AutoCorrect As Integer and Log_Name_AutoCorrect_Changes variables
from Boolean to Integer, and conversion from -1 to 1, for example using Abs() function:
= Abs(.GetOption ...)
doesn't help ...

So maybe better turn off Track Name autocorrect manually, forever ?

Or use
If Val(Application.Version) > 11 Then Exit Sub
because this slowing down is significant in older versions.

On Error GoTo Exit_Label is more safe then On Error Resume Next.
Form_Activate and Form_Deactivate events would be better then Form_Open and Form_Close.
May 22 '12 #47

Post your reply

Sign in to post your reply or Sign up for a free account.