Another anomaly? (Access2003)

Topic

New Reply

Ok, here is another one. Although I’m not really sure it is cause by Access itself.

One of my clients is a software house, and they just released a new version of their software, installing it at several sites for final testing before the general release. At one site, they ran into 2 bad problems:
– The backend MDB file experienced regular and severe bloating, going from 55MB to 2GB in a matter of hours!
– One particular form was taking a loooong(!) time loading; although sometimes fairly quickly, other times it took seemingly forever.

The backend MDB is sitting on a Windows 2003 server. All users were using Access2003; but some were local to the server, while others came into the server via Remote Desktop. Everyone had own copy of FE. As best as we could determine, everything had latest versions and patches.

In the database, there was already a Persons table and an Activities table, and the new version added an Experience table. For a small subset of all Persons (some 40 out of 7000), there was 1 record in the Experience table for each Activity (a classic “resolver” table to handle a many-to-many situation).

The form in question was doing a “just-in-case” adding of activity records to the resolver table (since there were several ways new Activities could be added), relying on the Unique Key of PersonID and ActivityID to keep duplicates out of the table. Apparently it was this query that was causing all the problems! For some reason it was causing Access to try to get more space, and the process of getting this space delayed the form from opening.

Here is what I have surmised. The query basically determined there were some 12,000+ records that would be added to the Experience table (each of 40 persons and some 300+ activities). Access basically then went out to get enough room to add all these records. Only after getting the space and actually trying to add the records did Access realize that not all the records would be added. But the space had already been asked for and allocated; and this happened each time the someone opened this form and the query ran.

I have a vague recollection of how Access allocates space. I believe starting in Access97, Access no longer just added all new records to the last sector in the file (this often caused a contention problem). When new space is allocated, it essentially includes a buffer area to allow for future records being added to these areas rather than always at the end.

So it appears that new space was constantly being asked for, but then never used! And apparently Access didn’t know it, which is why it kept asking for more. Now, I don’t know if it merely Access2003, or the combination of Access2003 with Windows Server and/or with the use of Remote Desktop.

Anyway, I rewrote the query to only add records not already in the Experience table, and we haven’t had any more problems with bloating or performance.

So, this is just something else you can keep in the back of your mind!

Reply | Quote

Replies

Access 2000 and above can bloat very quickly, and 2002 and above format files grow even quicker since they contain a system table that does NOT compact and that does not exist in the 2000 file format. Under the hood, Access creates a lot of temporary queries and that results in all that space being allocated. No version of Access has ever released allocated space without a compact being run.

Reply | Quote

This was the backend bloating, not the frontend, and aren’t the temp queries in the frontend? And the BE is in Access2000 format. And it isn’t a question of releasing the space, it almost seems as though Access doesn’t even seem to realize it has it and can use it. (More please!)

Reply | Quote

Yes, the temp queries should be in the front end and that sounds extremely weird. I don’t have 2003 so I can’t guess what might be going on there unless their code is actually performing queried in the back end for some obscure reason.

Reply | Quote

I think your description of how space is allocated in Access/Jet is essentially correct, especially if multiple users are doing it concurrently. You might also check to see if the OnCurrent event is contributing to the grief. I’ve seen sets of code get run many times in short order – and very often that event will trigger at least twice. Just an outside possibility.

Reply | Quote

Hi Mark:

Since you seem to understand this Access bloating phenomenon, perhaps you can advise me on how to roll it back!

I have a split front-end back-end application in production. In January, we “upgraded” from Access 2000 to Access 2003. Since then, the backend has bloated from 34meg to 700 meg, compacted, although it is still in Access 2000 format. We are running on XP and Windows 2003 X64 server.

The front end presents some user forms allowing users to run customized reports on the backend tables. The backend imports tables from a commercial SQL server database via an .adp file plus a second commercial software that uses an .mdb backend, using the transferdatabase method. So every day, my backend code deletes its 36 imported tables, reimports them from the source databases, sets indices and relationships on the imported tables to the permanent tables, and performs calculations via DAO and VBA code and update queries. There are about 80 permanent tables, of which about half are regularly emptied and filled bythe update procedures, and 15 modules, none of which is over 64 k.

Everything works fine, but I am getting increasingly perturbed by the bloat, which increases steadily and does not go away with compiling. I have added code to the imported tables to set the text fields’ unicode compression to true and the tables’ subdatasheet name to [None]. I have added code to index fields that are used in queries (both back-end and front-end) to speed things up. I went through all my dao code and explicitly set all my recordset and database objects to close and set their variables to nothing at the end of each procedure. I have set the backend to compact on close and run a decompile, compact , compile and re-compact weekly. I’ve also used the compact and repair utility, but it doesn’t seem to have any effect.

Can you suggest how else this bloating can be brought under control? Also, do you know if the 2 gig limit on .mdb files has changed? I feel like a lemming heading for a cliff!

Thank you, in advance, for your help.

Reply | Quote

I don’t know a real solution for your problem, but you can often reduce the size of a database by importing all database objects into a blank new database (make sure to import the relationships too), then compacting the new database. If it helps, replace the old database with the new one.

BTW I assume that your backend database contains tables only. If so, decompiling and recompiling will have no effect – it applies to VBA code only.

Reply | Quote

Thanks, Hans, but I’ve already tried the new database idea, using the compact and repair utility and also creating a blank db and importing everything via the wizard. And yes, my back-end db has LOTS of vba code, in 15 modules.

Any other ideas?

Thanks for your help!

Reply | Quote

If importing into a blank new database, then compacting doesn’t help, your backend really IS that big. I don’t think there is anything else you can do (except perhaps going back to Access 97, which is not very attractive).

Reply | Quote

Access 97 always worked for me, but it’s a bit late now . . .
How could an mdb grow from 34 meg to 700 meg in 4 months using basically the same code?

Reply | Quote

I must mean that there are more data, e.g. in the imported tables.

Reply | Quote

Okay, I answered my own question. One of the sql server tables that gets imported has been expanding rapidly. I tried deleting it from a copy of my back-end file, and it shrank from 700 meg to 74!

You were right, as usual, Hans!

Reply | Quote

Is there a reason why you can’t simply link to the SQL Server tables using ODBC instead of importing them? That gives you current data all the time, and your database won’t bloat like it currently does. Also, 2GB is still the MDB limit and isn’t expected to change.

Reply | Quote

Well, that’s a thought. I didn’t try it initially because of three factors; first, my application has a number of permanent tables that would have to be linked to the SQL Server tables; second, I’m also using tables from a second commercial software with an .mdb back-end (which would also have to be linked) and third, the powers that be around here are very nervous about my doing anything that could affect the commercial software or its data. I thought that having a front-end reporting database that links to a backend which links to two other back-ends, one on the SQL server, would be unwieldy. And finally, I would rather run update code at night on the back-end than have users creating record locks on the commercial software’s tables during business hours, so that would mean using disconnected recordsets, which is a lot more development work in an area I haven’t tried before. And one always has to think about performance if users are pulling records via local queries on a networked back-end that pulls its records from two other sources.

How would you suggest going about it, and what do you think the performance results would be?

Reply | Quote

We routinely build applications that link to SQL Server tables, which usually gives dramatically improved performance. For example we have a table with some 3 million records that links to a people table with about 400,000 records, and in an Access front-end we get sub-second response time when displaying the records from the large table linked to a specific record in the people table. My perspective on your concerns:

• I don’t understand your issue with linking permanent Access tables to the SQL Server tables – the Access permanent tables would be in the Access back-end database. However there is a concern over performance where Access tables are joined to SQL Server ODBC linked tables, as the ODBC driver often assumes it has to pull the entire table in to run a query.
• Are you importing the commercial application .mdb tables into your Access backend at this point or are they linked? You might consider exporting them to SQL Server tables if that’s an option. (I’m presuming that the commercial application is not associated with the SQL Server tables.)
• When you link to a table that is already linked to another database, you actually create a link directly to the .mdb that contains the table, not to the database that alreadly links to the table. Thus your front-end would have direct links to SQL Server, to the commercial application assuming you choose to work that way, and to the permanent tables in your own back-end .mdb.
• Unless users have write permissions to the tables, locks on the commercial application tables shouldn’t be a problem. If you implement Access User Security, you can specify that they only have read permissions on those tables. In general we configure Access to run with no locks and deal with conflicts when they arrise.
• Finally, I would give serious consideration to moving all of the tables to SQL Server if you have access to it for design work. For example, I might have a process to import all of the commercial application tables into SQL Server on a periodic basis, perhaps at night. I would also move the permanent tables from Access to SQL Server – it would be desirable to put all of them in the same SQL Server database as the tables you are using. The ODBC driver converts most simple queries into a query which SQL Server understands, but if it involves joining tables in two different SQL Server databases, even if they are on the same physical server, it decides it needs to do the query in Access, and brings both tables across.[/list]Hope this makes sense – if you have further questions or issues we’ll attempt to help.

Reply | Quote

Thanks for your help.

RE: “concern over performance where Access tables are joined to SQL Server ODBC linked tables” – that is my performance concern. But I may link the bloat table to avoid duplicating data.

I’m importing SQL server tables from one commercial app and .mdb tables from a different commercial app, linking them to permanent Access tables. My report queries pull (calculated) data from the permanent tables, but also basic stuff from the imported tables, so they need to be linked. I hesitate to put the whole thing onto SQL server because I use Access SQL to do a lot of powerful things via DAO, whereas I’m not very familiar with T-SQL or how to get what I want out of SQL server’s text-based fields (i.e., setting date parameters without having to run a CONVERT function on every record, which slows querying to a halt!) Can you recommend a T-SQL for Access developers book?

I’ve implemented Access security; this is one of the main reasons I was asked to write the application, to provide restrictions on certain fields in some tables for user groups plus some other granularity that the commercial software doesn’t provide.

The users have write permissions on the SQL Server tables via the commercial GUI, but not in my app! it’s just for generating custom reports, spreadsheets, pdfs, etc.

RE: “When you link to a table that is already linked to another database, you actually create a link directly to the .mdb that contains the table, not to the database that alreadly links to the table. Thus your front-end would have direct links to SQL Server, to the commercial application assuming you choose to work that way, and to the permanent tables in your own back-end .mdb.” – Thank you for this information! (Is it somewhere in Microsoft documentation land???) But this raises another concern. If I link SQL Server tables into my Access .mdb back-end, could my users see the SQL Server data based on my userid and permissions? I don’t want them to have to use 2 logins, one for Access security and a second for the SQL Server. But I need to keep them out of the SQL Server tables.

RE: ” we have a table with some 3 million records that links to a people table with about 400,000 records, and in an Access front-end we get sub-second response time when displaying the records from the large table linked to a specific record in the people table. ” Are both tables in the same SQL Server database, or are you talking about linking via ODBC in Access?

Thanks again!

Reply | Quote

I would not import these into the backend. I think Hans suggested importing them into a blank database. I’d have a separate database just for these temp tables.

Reply | Quote