Forms Slow to Save and go into Design View

[iheartvba]

Hi,
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.

[JustJim]

@iheartvba
Have you tried Compact and Repair?
Also, try not clicking too loudly, you might be scaring the poor little beastie!

Jim

[iheartvba]

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.

[JustJim]

@iheartvba
I'm hearin' ya.

Jim

[NeoPa]

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.

[FishVal]

@iheartvba
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?

Regards,
Fish.

[iheartvba]

Hi,
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.

[mshmyob]

@iheartvba
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.

cheers,

[ChipR]

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.

[mshmyob]

@ChipR
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.

cheers,

[iheartvba]

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.

[Megalog]

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.

[iheartvba]

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?

[NeoPa]

The Import process can handle multiple objects, including not just Tables, but also Forms, Reports, Queries, etc.

[iheartvba]

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.

Thanks

[NeoPa]

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).

[Megalog]

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.

[NeoPa]

See Front-End / Back-End (FE/BE) for stuff about Front End / Back End.

[iheartvba]

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

[NeoPa]

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.

[iheartvba]

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.

Thanks

[NeoPa]

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.

[iheartvba]

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?

[NeoPa]

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 ;)

[iheartvba]

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.

[iheartvba]

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.

Thanks

[Megalog]

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.

[NeoPa]

@iheartvba
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.

[NeoPa]

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)?

[iheartvba]

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.

Thanlks

[NeoPa]

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.

[iheartvba]

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.

Thanks

[NeoPa]

No worries. I have some code for that.

Tell me. Which of the databases gets the corruption. The control (FE) or data (BE) database?

[iheartvba]

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!

[Megalog]

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'.... =)

[ChipR]

I do a Compact and Repair on startup, but only to save the file as a backup.

Set cnn = CreateObject("ADODB.Connection")
Set userRecords = CreateObject("ADODB.Recordset")
'close the connection to the backend
  DoCmd.Close acForm, "ConnectForm"
'see how many users are connected
  cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
   & "Data Source=" & dbPath
 
'-1 = adSchemaProviderSpecific
  Set userRecords = cnn.OpenSchema(-1, _
   , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
  userCount = 0
  While Not userRecords.EOF
      userCount = userCount + 1
      userRecords.movenext
  Wend
  userRecords.Close
  Set userRecords = Nothing
  cnn.Close
  Set cnn = Nothing
'if others are connected, can't back up
  If userCount > 1 Then
      'reopen connection form
      DoCmd.OpenForm "ConnectForm", acNormal, , , , acHidden
      Exit Sub
  End If
'do the compact and repair
  If Not Application.CompactRepair(strSourceDB, strDestDB, True) Then
      Err.Raise 666, , "Unable to back up the database."
  End If

[NeoPa]

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 :

Private Sub cmdCompact_Click()
    Dim strWork As String, strDBs As String
    Dim intPos As Integer, intLen As Integer
    Dim tblThis As TableDef
 
    strWork = Replace(conWarning, "%1", conStub)
    If Not CheckOp(strWork, conJob) Then Exit Sub
 
    Call SwitchMode("Process")
    strDBs = ""
    For Each tblThis In CurrentDb.TableDefs
        strWork = ConnectName(tblThis.Name)
        If strWork > "" Then
            strWork = """" & strWork & """" & vbCrLf
            If InStr(1, strDBs, strWork) = 0 Then _
                strDBs = strDBs & strWork
        End If
    Next tblThis
    intPos = 2
    Do
        intLen = InStr(intPos, strDBs, vbCrLf) - intPos - 1
        If CompactDb(Mid(strDBs, intPos, intLen)) Then
            intPos = intPos + intLen + 4
        Else
            strDBs = Left(strDBs, intPos - 2) & _
                        Mid(strDBs, intPos + intLen + 3)
        End If
    Loop Until intPos > Len(strDBs)
    strWork = MultiReplace("The following databases were compacted:%1%2", _
                           "%1", vbCrLf, _
                           "%2", strDBs)
    Call ShowMsg(strMsg:=strWork, strTitle:=conJob, intButtons:=vbInformation)
 
    With cmdExit
        .Default = True
        Call .SetFocus
    End With
    Call SwitchMode("View")
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 :

'CompactDb compacts a remote Access database.
Public Function CompactDb(ByVal strDB As String, _
                          Optional ByVal strDBTo As String = "", _
                          Optional strPW As String = "", _
                          Optional blnZip As Boolean = False) As Boolean
    Dim strCmd As String, strLocale As String, strZip As String
    Dim strSrcDB As String, strDstDB As String
    Dim blnTmpDB As Boolean
 
    On Error GoTo ErrorCDB
    Call Echo(True, "Compacting """ & strDB & """.")
    strSrcDB = strDB
    strDstDB = IIf(strDBTo = "", Replace(strDB, ".Mdb", "New.Mdb"), strDBTo)
    If Exist(strDstDB) Then Kill strDstDB
    If strDB = CurrentDb.Name Then
        strSrcDB = Environ("Temp") & "\" & _
                   Split(strDB, "\")(UBound(Split(strDB, "\")))
        strCmd = MultiReplace("CMD.EXE /C ""Copy %F %T""", _
                              "%F", strDB, _
                              "%T", strSrcDB)
        Call ShellWait(strCmd)
    End If
    If strPW <> "" Then strLocale = ";pwd=" & Scramble(strPW)
    Call DBEngine.CompactDatabase(SrcName:=strSrcDB, _
                                  DstName:=strDstDB, _
                                  DstLocale:=strLocale, _
                                  SrcLocale:=strLocale)
    If strDBTo = "" Then
        Kill strDB
        Name strDstDB As strDB
        strDstDB = strDB
    ElseIf strDB = CurrentDb.Name Then
        Kill strSrcDB
    End If
    If blnZip Then
        strZip = Replace(strDstDB, ".Mdb", ".Zip")
        Call Zip(strZip, strDstDB)
    End If
    Call Echo(True, """" & strDB & """ compacted.")
    CompactDb = True
    Exit Function
 
ErrorCDB:
    CompactDb = False
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.

[iheartvba]

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

[NeoPa]

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 ;)

[NeoPa]

@iheartvba
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.

[iheartvba]

Thanks,

P.S. You guys are doing a great job here, no other forum even comes near bytes.

[NeoPa]

Thank you on behalf of all of us.

We do like to help if we can :)

[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!

[dara123]

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

[NeoPa]

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.

[darekk]

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:

Private Track_Name_AutoCorrect_Info As Boolean, Perform_Name_AutoCorrect As Boolean, Log_Name_AutoCorrect_Changes As Boolean
 
Private Sub Form_Open(Cancel As Integer)
 
On Error Resume Next 'just in case
With Application
Track_Name_AutoCorrect_Info = .GetOption("Track Name AutoCorrect Info")
If Track_Name_AutoCorrect_Info Then
    Perform_Name_AutoCorrect = .GetOption("Perform Name AutoCorrect")
    If Perform_Name_AutoCorrect Then
        Log_Name_AutoCorrect_Changes = .GetOption("Log Name AutoCorrect Changes")
        .SetOption "Log Name AutoCorrect Changes", False
    End If
    .SetOption "Perform Name AutoCorrect", False
End If
.SetOption "Track Name AutoCorrect Info", False
End With
On Error GoTo 0
 
End Sub
 
Private Sub Form_Close()
 
On Error Resume Next 'just in case
With Application
.SetOption "Track Name AutoCorrect Info", Track_Name_AutoCorrect_Info
If Track_Name_AutoCorrect_Info Then
    .SetOption "Perform Name AutoCorrect", Perform_Name_AutoCorrect
    If Perform_Name_AutoCorrect Then _
        .SetOption "Log Name AutoCorrect Changes", Log_Name_AutoCorrect_Changes
End If
End With
On Error GoTo 0
 
End Sub

Obviously you can do it manually, permamently.
The only thing very, very disturbing here is detaile reson of that slow down.

[darekk]

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.