SLOW Access 2007 MESSAGE Updating tables. Press and hold ESC to stop.

See if any of this helps (you can compare these issues with your other databases).:

Reduce Corruption, Speed up database
All databases are heavy network users. To reduce the risk of corruption and speed up the database, these are things to do for any database:

1: Make sure your hardware is in top shape, all computers are routinely vacuumed to remove dust and lint and your network is set up correctly. Have all the machines on the same domain.

2: Have individual front ends on each machine. I created a database that checks the server for a newer copy of the front end, downloads it if needed, then runs it. You can get a copy of it at: http://www.thenelson.name/, Updated Front End

3: Create a mapped drive for the backend or place the backend folder as close to the root folder and use only UNC path to the backend. Some people (and Microsoft) say the mapped drive is better, some say the UNC path is better. I have found it depends on the network setup.

4: Have the name of the backend and the name of the folders in the UNC path to it as short as possible meeting dos 8.3 naming specs and with only alpha numeric characters. \\server1\C:\db\db1_be.mdb is better than \\Database Server 601\C:\My Database Folder\Database Backends\My Database Backend_be.mdb

5: Don't run a local copy of the front end on the machine that has the backend -- best to have the backend on a true server running a domain with all workstations subscribed to the domain. You might get by running home version for two computers, maybe three sharing a very small, simple Access database on the home version although you may run into sharing problems. In a work situation, running a multi user Access database, all workstations should be running the pro version. It you have less than about five workstations, you might get away with the backend being on a system running pro but more than that or with a complicated database, you need a domain and the server running a server OS. Get up to a dozen heavy users or a couple of dozen light users and the server should be a true server, be dedicated to only serving the one database and the backend should be SQL Server (or SQL Server Express)

All of this is cutting down the number of times accesses need to be qualified, files need to be opened and closed and reduces the complexity of parsing the path all of which reduces the chance of corruption and speed up the database.

We need to have the db FE and BE on a server. Have you seen this message before?

I don't have A 2007 so not have not seen that message.

if you have multiple users sharing a FE, you can get a situation exactly as you describe. Access jet is extremely poor at sharing the FE.  If you need to have the FE on the server, have a separate copy for each user in a of different folder. This will still slow things down as it triples network traffic.   to test this, try running the db with only one person signed in in exclusive mode.

Having teh FE on the server pretty much defeats the purpose of having the FE in the first place.  
If the problem is that you want to make sure that the FE is up to date for every user, you can put a routine in the FE so that it checks the release information on the FE on the server and, if it needs to update the local copy, it triggers a script that downloads the new version of the FE and restarts the FE.  Of course, an even simpler technique is to have the local copy of the FE check the network copy and simply refuse to go past a message that informas the user that they need to download the latest copy  (and close the FE on the OK button being clicked).
 

Here is a utility that updates the FE automatically:  www.thenelson.name/#CheckForUpdatedFe

Thanks but I really need someone who has seen this problem. This is a police app and must run on the server. We have been using it just fine in 2003 and 2002. The City Clerk decided to upgrade the City to 2007 now we have this issue. It was speedy-fast and fine before. Has anyone seen this problem?

gaynes,

Then without seeing the code that is "Updating the tables", this will be difficult to troubleshoot.

You stated that: "It is at SP3"
Office 2007 is currently (as of today) at SP2, so this is a bit confusing.

Please remember that Access 2003 and 2007 are totally different formats.
So upgrading to 2007 is unlike upgrading in previous versions.
There are many things that Access, let you "get away with" in Access 2003, that are now not allowed in Access 2007.
For example, undeclared variables and the way you reference properties of some objects.
The coding syntax seems to be "Tightened up" a lot more in Access 2007.

Also make sure the sub datasheet Name property is set to None. (not Auto)

Whenever you convert from Access 2003 to 2007:
1. Run the compact repair utility on the db *In Access 2003*
2. Create a Blank Access 2007 db and import all of the objects from the Access 2003 db into the New, Blank

(Also try converting this to the Access 2007 format, but make a backup first)

See this incredible link:
http://www.granite.ab.ca/access/performancefaq.htm
Please read the entire link as almost everything is applicable, even though it may be for a different version.
Again, many of the things you did in Access 2003 may just work poorly in Access 2007.

But again, without you providing the code, this will be difficult to diagnose.

JeffCoachman

This is the first mention that it is a A 2003 db running in A 2007. Before trying Jeff's # 2 suggestion of importing objects into a 2007 blank db which bus been helpful in many cases (but time consuming), try decompiling/recompiling in A2007.

1) ALWAYS CREATE A BACKUP OF THE DATABASE BEFORE DECOMPILING. (Just in case)

2) decompile: in run: msaccess "dbPathName.mdb" /decompile
   Compact/repair:  in run: msaccess "dbPathName.mdb" /compact
   compile: in VB editor: debug> compile
   Compact/repair
   http://www.granite.ab.ca/access/decompile.htm


Because of A 2007's security features, it is more intolerant of sharing the FE.  

This database uses Access security so it has to run in "legacy mode" It cannot be upgraded completely to 2007. I changed all the tables to "none" and will see how that fares. I keep thinking that I am missing a setting somewhere. I suppose the only option after this is SQL Server.

https://www.experts-exchange.com/questions/24405351/Frequent-Updating-tables-message-in-front-end-using-Access-2007.html#discussion

has the same issue

Is there a reason why you have not posted this mysterious "Update tables" code yet?

We are all just guessing without it.

The message comes from Access. It appears on the lower left of the screen and everything slows down. I am not updating tables, it is I guess.

<It appears on the lower left of the screen...>
...after you do what?

After a few people are using the app. There doesn't seem to be anything in particular.

The I am at a loss

Can you post a sample of this database?

JeffCoachman

This is a police app and must run on the server.
Are your users connected via VPN, Remote Desktop, or an Intranet (i.e. local network) connection?  
We have been using it just fine in 2003 and 2002. The City Clerk decided to upgrade the City to 2007 now we have this issue.
Welcome to the wonderful world of Access development.  This is precisely why I use Access for protoyping but not for production databases.  Every time there is a new version of Access, there are uissues with apps that have been created on older versions, in my experience.
I suppose the only option after this is SQL Server.
That is definitely one option.  You can convert your tables to a SQL Server database (I think, because everything is running from the server, you may even be able to use SS Express) with the same table names and then link the FE to the databases and eveything should basically work as though it were an Access back end.  It should be noted that there may be some slight issues with the way Access handles table access but it shouldn't (meaning, probably/maybe won't ;-) be a big problem.
To more direcctly address your problem, some additional questions:

• Has there been an increase in the number of users accessing the FE (and, therefore, the BE)?
• Have there been any changes to the FE or the BE (other than the Access version upgrade)?
• Are there any update queries that automatically get run when someone logs into or out of the database, exits a form, or does anything else?
• Is it possible that people are running reports?
• Is it possible that there is some sort of bulk load of data that may be occurring?

I cannot put out a sample. The app has not changed. There is no bulk load of data. They do run reports - printing the police forms- 1-3 pages. Users numbers have not increased.

I added a table to the main menu for a persistent connection. I changed all tables to "none". It seems to have helped. I will let you know how it is going. I have other apps that are Access/SQL so that is the next attempt if no one comes up with any other ideas.

<I cannot put out a sample.>
We don't need your actual data, the key word is "Sample".
Just load it with dummy data.

I can't do that. It is a commercial/custom app.

Monitoring shows it taking almost all resources.
Hmmm, by this, do you mean that SQL Server is taking almost all of the resources or the Access Fe or BE?
I added a table to the main menu for a persistent connection.
Was this before or after the problem arose?
I changed all tables to "none".
????? Can you expand upon this?

This is not using SQL Server. I changed subtable properties to [none] from auto. I added the persistent connection to keep the FE and BE constantly talking. These are per suggestions for speeding up I have found. When it gets the message, the server shows 90% CPU usage.

I really was hoping to find someone who has seen this problem and fixed it. I was hoping it was a setting I missed.

Sorry about that, I was working with someone else with a similar slow-down issue invovling a SQL Server database and I guess I sort of cross-wired my thinking.
I am going to bowout of this discussion because, quite frankly, I have never dealt much with situations that have both the FE and BE on a server in a luti-user environment.  The one time I did, we had a terrible time keeping the BE from getting corrupted until we moved the FE to local machines and forced it into a username/password for sign in condition. (Having the FE on a local machine shouldn't really be that big of a security issue if the FE is built right. ;-)

I have many other apps running this way with no issue. Again, I am looking for someone who has seen this before with Access 2007 and has fixed it.

The specific issue you are having is a manifetation of something else.

If Access senses that it will take a while to do something, it displays this message.
For example, if Access has to update 2 million records, you will get this message.
There is nothig to "Fix".
It is going to take a long time to update 2 million records.

In your case the goal is not to avoid then message, but figure out why you are getting it.

Again, without the actual Application, we have little to go on.

Of the top of my head I will suggest that you index the most commonly used fields, and make sure you are upto date with all the service packs *and* hotfixes, for Windows, as well as Office.

For more hints on how to speed things up, see here.
http://www.fmsinc.com/MicrosoftAccess/Performance.html

Perhapse one of these things Access 2007 is just more sensitive to than it was in previous versions.

Beyond that, your other option is to Call Microsoft directly.
;-)

JeffCoachman

JeffCoachman

Hi gaynes.
I know exactly how you feel. I've been having this problem ever since I upgraded to 2007 (SP2) from XP. The database is a lot slower in 2007, and I've just noticed this 'updating tables' message, where if you press the escape key, its back to normal speed.
I don't have an answer... but am continuing to look around and try out things. I thought I should post to let you know that these problems do exist!

Hopefully one of us can get an answer and help the other...

I'm also unable to post a sample of this problem. I think when people move to 2007 themselves, they will then experience problems. It certainly hasn't been an easy step up (like all the others before). I think the only people that can help with these kind of 2007 problems are those who use it.  :(

Exactly Gary! While I appreciate the people with general posts they think will help, it really does me no good. I am an Access developer and have seen and fixed most everything. I do get great help from here when I am stuck or have "brain freeze." 2007 is a whole new animal. I am not a fan. I think Microsoft is trying to push us off of Access because it doesn't make them any money like SQL Server does. However, for most small business with just a few users using a database, (which is a huge market), it is the perfect solution. This is my niche. I enjoy creating productivity tools.  

If I find a solution, I will let you know. Taking the [auto] off of all the tables seemed to help. They now compact every morning too. I will probably pull them to a SQL Server back end soon.

gaynes,

You bring up great points, as you see I tried not to "Guess" at what the issue might be.

I personally have not experienced anything like this in upgrading my apps to 2007.
But this is a common question here.
So I too am curious as to what the common thread mght be.

Here is what most developers reccommend when converting.
Compact/Repair the DB in the Original Access version.
Create a new blank Access 2007 format database.
Import all the objects fron the previous DB into the the new DB.

At least then you know you are starting out fresh.

Will you be posting a simple sample that exibits this issue?

JeffCoachman

sort of related... may help... scold me if this is a bad noob post

I am having this issue also... 'updating tables' message, where if you press the escape key, its back to normal speed. It happens every time you attempt to look up a record no matter what form you are on.  It only happens on one of my 40 workstations and of course it belongs to a VIP. This leads me to believe it is a workstation issue. Everyone else uses the same Access 2007 front end with out this particular issue.

On the Workstation WinXP sp 3 and Office 2007 sp2

The server is 2k3 64bit sp 2 and running SQL 2005 sp3 32bit

Access 2007 is the frontend used by the user. They use a shortcut and open the Access 2007 file via a mapped drive.

Gigabit Ethernet links everything up on my network.

Copying the access accdb file to the desktop does seem to help. It has only been one hour for the user so maybe it is too early to tell.  :-) I know my issue may be unrelated to the original poster but it seems very similar. His FE and BE issues are different. SO...

Consider making your shortcut UNC and using a mapped drive to compare anecdotal results.
\\servernam\share\file vs. H:\share\database

My DBA has a call in to MS support for a host of performance issues and I will post any enlightenment I receive if anyone is interested...

BlackJeep,

With a post like this, *All* posts are welcome.
;-)

Again, I have not had this issue...*Yet*
:-O

So I too am interested in the resolution.

;-)

Jeff

I will give it a try. Thanks for posting - it does sound like the same issue.

Did you ever resolve this?  We are having the same problem with the updating tables message and then program crashing.  We recently upgraded from XP and Office 2002 to Vista and Office 2007.  For the first 3.5 days everything worked great.  Then on Tuesday afternoon we started getting these problems.  Did your problems start right away or was there a delay.  Help!!

We were instructed by Microsoft to do the following things to make everything run faster. We did everything they suggested and had all of our problems evaporate.

1. Use UNC naming paths for shortcuts \\servername\share and not mapped drives W:\ this made a noticeable difference. Mapped drives are not good according to the support tech we spoke to (He was a higher level tech based in the US)

2. Use a copy on your desktop of the front end instead of a shortcut to the server. Another performance boost. But this may not be possible for the original poster... sorry but this makes a huge difference! We are using an .accdr file to keep the users from messing things up and a logon script that copies the file to the desktop when they log on.

3. Update your Office 2007 to SP2 this fixed the updating tables issue and keeps the front end from bloating in size on the server. Our shared frontend would grow 5 times in size over the course of the day prior to the update.

4. Make sure your database does not have a scroll bar on it when you display it full page. There is a weird redraw error that occurs.

Also- I am running SQL 2005 as my back end and made some changes on the server / workststions

Freed up memory so that SQL can use more than 4 gigs...
http://technet.microsoft.com/en-us/library/ms179301%28SQL.90%29.aspx 
(note: you must reboot after giving the account new permissions or the script you run will give you an error message)

Used the SQL native driver for SQL 2005 found as part of Feature Pack
 Microsoft SQL Server 2005 - February 2007 located at:
 http://www.microsoft.com/downloads/details.aspx?FamilyId=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

There is also a SQL 2008 version as well if your are running that:
Microsoft SQL Server 2008 Feature Pack, August 2008
http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

I am preparing to run a SQL maintenance script found here:
http://sqlfool.com/2009/06/index-defrag-script-v30/

Since all of these changes all of my users are now happy... yes happy! I know this is rare so I will cherish this joyous time. I hope this helps some of you. :-)

I wrote a batch file that updated to SP2 for Office, updated the native SQL 2005 database connectors, defragged their harddrive etc...  for users and can post it if anyone is interested.

When I set the subdatasheet in properties of tables to [none] from [auto] it helped. I also set a persistent connrction from the main menu.

2. Use a copy on your desktop of the front end instead of a shortcut to the server. Another performance boost. But this may not be possible for the original poster
This is essentially one of my first suggestions/recommendations. Although, I cannot imagine why the OP cannot run a copy of the FE on the user's local box, I am forced to accept that, for whatever reasons, thatis the case, which means that the OP may never solve that aspect of contention. ;-)
Taking the [auto] off of all the tables seemed to help.
I have just found (via Google) references to the "auto" and "none" setting for tables and that it references subdatasheets.  Had the OP been more forthcoming with that bit of information, I would have pointed out that the simple act of opening access to a table could easily cascade the subdatasheets with the resultant resource requirements and, therefore, the resultant over all performance degradation.
One of the keys to getting answers is to provide as much information as possible.  Ignoring requests for information may well preclude assistance.

You might want to check the following to see if it presents ideas you haven't tested.
http://groups.google.com/group/comp.database.ms-access/browse_thread/thread/91351868b020b9d 

I forgot to mention...

Under my own assumption that sometimes people read through these posts after they are closed, looking for answers or ideas... I know I have! :-) I was just putting down everything I had done as a comprehensive list (understanding there were repeats of suggestions) I was just making a helpful reference of the kitchen sink type...

Also that "#4 Make sure your database does not have a scroll bar on it when you display it full page. There is a weird redraw error that occurs..."     Is not listed anywhere I could find but it made a huge difference. We had some of our users change their screen resolution from 800x600 to 1024x768 and maximize the access window. It eliminated the scroll bar for the entire Access window. We actually got a free support call out of it from Microsoft because they had nothing posted a year ago when we ran into this.

If this is a police dept app and it is run on old monitors that the city can afford... or your users like 800x600 because they need help seeing a screen... you may run into this.  :-)

MS could not explain why it happened or if they were going to fix it when we talked about it with them a year ago...

Good luck and thanks for keeping us safe! :-)

Does the scrollbar problem apply to the subforms too?

Most likely

a lot of my subforms have vertical scrolls - I haven't had issues with any other dbs

Yes, they indicated scrolling was not preferred when ever possible. Of course this is thier tech and our systems... your mileage may vary. But as a principal of design it makes sense as well.

with the idea being that you do not have to scroll to see any displayed information. We played with screen resolution, tabs, and buttons that opened up sub forms when you needed them for a record and font sizes. It helped a lot on our slowest workstations.

We made it much better in terms of loading what was needed, when needed, and displaying on screen without scrolling. Most everyone should do this as normal practice but it is easy to add new features and capture more data and forget about this as your database evolves.  :-) (been there, done that)

Note MS said it did not happen to everyone and they did not know why it was an issue with Access 2007.

The scroll bars were about the entire form or Access 2007 Window when you opened it, not a field withing the form (say for example a VarChar or text field where you put notes about a customers account). Sorry if I lack the proper nomenclature.

It was a buggy thing that made no sense to us when we were told.  But it worked. We honestly thought we were being told a big one. It is just turned out to be one of those goofy things that defies common sense.

Quitting now... so I am not thread jacking (anymore) :-) .

I have not had this problem.  Nevertheless, you should look at the hotfix at this link:
                                                  http://support.microsoft.com/kb/962214#top

I wonder if this is included in SP2?