Access – Slow Design View

Recently, I’ve been doing a lot of work using Access 2010 and have been finding it slow when doing any design work.

Let be clear about my setup, I have already ensured an optimal development environment:

  • All the files are local
  • Subdatasheet are set to None
  • AutoCorrect is turned off
  • Running off of an SSD
  • Access is fully patched
  • Plenty of CPU/RAM/HD …
  • and so on …

Regardless of the above, Access is plain slow.  It is slow to

  • Open an object in design view
  • Select subforms
  • Switch between design and SQL view when trying to edit a record source
  • Open the Existing Fields dialog
  • …

So what can be done?

The typical answer is to work with the files locally, but I am already doing this!

So what now?  So the only thing I have found is to create a persistent connection as you normally do when running the database normally.  By simply opening a table, any table, and creating a persistent connection, the design process slowness goes away completely.

God only knows what the root cause of the issue is, but the solution appears to be to open a table to establish a persistent connection between the FE and BE, and that even during the development cycle!

8 responses on “Access – Slow Design View”

  1. erwin leyes

    Also had almost the same problem with A2010 FE /sqlserver 2008r2 BE and A2010FE/ mysql 5.7 backend both in windows 10 32bit machine. The connection is so slow, it eat’s my time troubleshooting and I ended up reformatting the PC and the problem got fixed. I still don’t know the cause but MS had a huge update I think last month.

  2. George Hepworth

    I believe this has to do with the locking file created when the back end is opened.
    With the persistent connection, it remains open, pointing to your FE. Without it, Access has to invest cycles in determining if it’s there, if it’s locked by someone else, opening it if necessary, and closing it again.

    While that may not be the entire story, I’m fairly sure that’s the primary factor.

    1. Daniel Pineault Post author

      George,

      I agree, and that’s why the workaround works, but it still doesn’t explain why one database has the slowness and another doesn’t, nor why the issue never existed (at least I never experienced it) prior to 2010.

  3. Othmar

    I have the same problem. My database and frotend are in one .accdb-File so the permanent connection should always be given.

    Once I solved the problem by choosing “repair” in the system control panel.

    Next time I was necessary to reject the feature “access” in the office-installation and then to reinstall it. Problem here was, that MSAIN.DLL was deleted by uninstalling access. So you should make a copy of it before doing the procedure.

    Perhaps I have to uninstall the hole office and to reinstall it. Or to change the notebook I use.

  4. LH

    I have the same problem very often, and i run setup to repair ms office and to add/remove without adding or removing, and usually helps, but many times i need to do it more than once for it to work, and of course only after rebooting.

  5. Mike Halmo

    I have chased this problem for years and finally found the cause and the solution.
    MS Access is a little like OneDrive where it boggs when syncing to too much data, in MS Access it is syncing to every table linked to the application. If you safe off a copy and delete most of your linked tables, the speed with radically improve. Ok, that proves the problem, but not the solution. The solution involves creating a table of table names and connection strings, then you dynamically add or remove what you do or do not need. I have a button on the main form called “Remove Add On Demand Tables” This deletes tables not needed at program launch. All other linked tables are added as the user enters one module or the next. Heres some sample code to get you started.
    Sub UpdateSharePointOnlineConnection()
    ‘ Specify the name of the linked SharePoint list
    Dim linkedTableName As String
    linkedTableName = “YourLinkedTableName”

    ‘ Specify the SharePoint Online site URL
    Dim sharePointURL As String
    sharePointURL = “https://your-sharepoint-site-url.com”

    ‘ Build the new connection string
    Dim newConnectionString As String
    newConnectionString = “ODBC;DRIVER=Microsoft SharePoint List ” & _
    “Driver; ” & _
    “SHAREPOINT; ” & _
    “LIST=” & linkedTableName & “; ” & _
    “STUBNAME=LIST_” & linkedTableName & “; ” & _
    “DATABASE=” & sharePointURL & “;”

    ‘ Update the connection string for the linked table
    Dim db As DAO.Database
    Set db = CurrentDb
    db.TableDefs(linkedTableName).Connect = newConnectionString
    db.TableDefs(linkedTableName).RefreshLink

    ‘ Release the database object
    Set db = Nothing
    End Sub

  6. David

    My problem is worse. This is the situation

    Access 2010 64 bits
    New database (database1.accdb)
    New table (Table1) with two fields: ID, Fld1
    Table1 contains 27 records
    I open Table1 in design view
    From design view I try to open it in datasheet view. It takes about two minutes to be opened
    If I open it directly in datasheet view from the objects pane, there is no problem.

    I have tried to repair and reinstall Office, uninstalled some other programs, stoped some services…

    Do you have any idea?

    Thank you in advance

    1. Daniel Pineault Post author

      If it were me, I’d import everything into a new db and see if that made any change.

      If not, I’d import everything except for that one table and recreate it, and see if the behavior persisted.