在Access 2007中绝对难倒在前端膨胀 [英] Absolutely stumped on front end bloating in Access 2007
问题描述
对不起，很长的帖子，我已经很努力地解决了这个问题，但是我很难/ b $ b刚被卡住了！
我们'多年来，网络共享上有一个多用户.mdb文件。我知道
它不太理想，但它从Access 2000到2003都运行良好。自从我们升级到2007年以来，我们已经注意到了
一些严重的腹胀。
当我们在2003年，在一个新的契约之后，数据库将是大约20 MB的b $ b。正常使用后，它将增长到大约25 MB然后
显着减慢。在过去的8个月中，它只增加了6 MB。
但是，当我们升级到Office 2007的那一天，数据库膨胀了/>
高达60 MB并计数。它似乎永远不会停止，我已经看到它b $ b高达90 MB。如果我压缩它，它会回到大约20 MB。
我已经尝试了几乎所有我能想到的东西;转换它
到2007格式（accdb），拆分它，做一个accde，反编译和
重新编译代码，导出每个对象作为文本并重建
前端，从头开始重建后端，删除了所有代码，
删除了所有索引，删除了所有临时querydef等等。无论我是什么，我都是
，它会膨胀。
膨胀发生在前端。在一个新的紧凑型后，
前端将大约4 MB。它将在
小时内轻松增长到20+ MB。它也受到其中用户数量的影响。 b / b
并发用户越多，它就会越快膨胀。
我尽可能地将其剥离，一直到了
约1 MB后端，700k前端。我让3个用户测试前端（网络驱动器上的共享accdb），并且在一小时内
它爆炸了近3 MB（4x大小） 。
我不使用临时表，很少使用附加查询，永远不会删除
查询。当我通过VBA进行任何操作时，我总是关闭记录集。
当我完成它们时，总是将变量设置为空。没有
任何嵌入的图像或文件。
然而，我使用了几个更新查询。但是当我完全禁用它们时它甚至臃肿（在重建的前面甚至没有它们 -
结束）。
我尝试将前端放在某人的PC上（希望启用
Compact on Close），但性能非常糟糕。当我们将后端移动到SQL时，我们将最终到达那里
，但那可能需要几个月才能实现。
我已经看到了一些关于这个的抱怨，这是2007年的一个错误吗？它b / b
看起来应该是如此之快，特别是在前端的b
。看起来很正常。
有什么想法吗？谢谢！
Sorry for the long post, I''ve tried so hard to solve this one but I''m
just stuck!
We''ve had a multiuser .mdb file on a network share for years. I know
it''s not ideal, but it''s worked well from Access 2000 to 2003. Ever
since we upgraded to 2007, we''ve noticed some serious bloating.
When we were on 2003, after a fresh compact, the database would be at
about 20 MB. After normal use it would grow to about 25 MB and then
significantly slow down. It only grew a total of 6 MB in the last 8
months.
However, the very day we upgraded to Office 2007, the database bloated
up to 60 MB and counting. It doesn''t ever seem to stop, I''ve seen it
as high as 90 MB. If I compact it, it goes back down to about 20 MB.
I have tried almost everything I can possibly think of; converted it
to 2007 format (accdb), split it, made an accde, decompiled and
recompiled the code, exported every object as text and rebuilt the
front-end, rebuilt the back-end from scratch, removed all code,
deleted all indexes, deleted all temp querydefs, etc. No matter what I
do, it bloats.
The bloating is happening in the front-end. After a fresh compact, the
front-end will be about 4 MB. It will easily grow to 20+ MB within an
hour. It also is affected by the number of users in it. The more
concurrent users, the faster it will bloat.
I stripped it down as much as physically possible, all the way down to
about a 1 MB back-end, and a 700k front-end. I asked 3 users to test
the front-end (shared accdb on the network drive), and within an hour
it blew up to nearly 3 MB (4x size).
I don''t use temp tables, rarely use append queries, never delete
queries. I always close recordsets when I do any manipulation via VBA.
Always set variables to nothing when I''m done with them. Don''t have
any embedded images or files.
I do, however, use several update queries. But it even bloated when I
completely disabled them (didn''t even have them in the rebuilt front-
end).
I tried putting the front-end on someone''s PC (with the hope to enable
Compact on Close), but the performance was terrible. We will
eventually get there when we move the back-end to SQL, but that''s
probably months out.
I''ve seen a few complaints about this, is it a bug with 2007? It
doesn''t seem normal that it should bloat so big so fast, especially in
the front-end.
Any thoughts? Thanks!
推荐答案
Brian Nelson写道：
Brian Nelson wrote:
对不起，很长的帖子，我我已经非常努力地解决了这个问题，但是我已经停止了这个问题。
我们有一个多用户.mdb文件网络份额多年。我知道
它不太理想，但它从Access 2000到2003都运行良好。自从我们升级到2007年以来，我们已经注意到了
一些严重的腹胀。
当我们在2003年，在一个新的契约之后，数据库将是大约20 MB的b $ b。正常使用后，它将增长到大约25 MB然后
显着减慢。在过去的8个月中，它只增加了6 MB。
但是，当我们升级到Office 2007的那一天，数据库膨胀了/>
高达60 MB并计数。它似乎永远不会停止，我已经看到它b $ b高达90 MB。如果我压缩它，它会回到大约20 MB。
我已经尝试了几乎所有我能想到的东西;转换它
到2007格式（accdb），拆分它，做一个accde，反编译和
重新编译代码，导出每个对象作为文本并重建
前端，从头开始重建后端，删除了所有代码，
删除了所有索引，删除了所有临时querydef等等。无论我是什么，我都是
，它会膨胀。
膨胀发生在前端。在一个新的紧凑型后，
前端将大约4 MB。它将在
小时内轻松增长到20+ MB。它也受到其中用户数量的影响。 b / b
并发用户越多，它就会越快膨胀。
我尽可能地将其剥离，一直到了
约1 MB后端，700k前端。我让3个用户测试前端（网络驱动器上的共享accdb），并且在一小时内
它爆炸了近3 MB（4x大小） 。
我不使用临时表，很少使用附加查询，永远不会删除
查询。当我通过VBA进行任何操作时，我总是关闭记录集。
当我完成它们时，总是将变量设置为空。没有
任何嵌入的图像或文件。
然而，我使用了几个更新查询。但是当我完全禁用它们时它甚至臃肿（在重建的前面甚至没有它们 -
结束）。
我尝试将前端放在某人的PC上（希望启用
Compact on Close），但性能非常糟糕。当我们将后端移动到SQL时，我们将最终到达那里
，但那可能需要几个月才能实现。
我已经看到了一些关于这个的抱怨，这是2007年的一个错误吗？它b / b
看起来应该是如此之快，特别是在前端的b
。看起来很正常。
有什么想法吗？谢谢！
Sorry for the long post, I''ve tried so hard to solve this one but I''m
just stuck!
We''ve had a multiuser .mdb file on a network share for years. I know
it''s not ideal, but it''s worked well from Access 2000 to 2003. Ever
since we upgraded to 2007, we''ve noticed some serious bloating.
When we were on 2003, after a fresh compact, the database would be at
about 20 MB. After normal use it would grow to about 25 MB and then
significantly slow down. It only grew a total of 6 MB in the last 8
months.
However, the very day we upgraded to Office 2007, the database bloated
up to 60 MB and counting. It doesn''t ever seem to stop, I''ve seen it
as high as 90 MB. If I compact it, it goes back down to about 20 MB.
I have tried almost everything I can possibly think of; converted it
to 2007 format (accdb), split it, made an accde, decompiled and
recompiled the code, exported every object as text and rebuilt the
front-end, rebuilt the back-end from scratch, removed all code,
deleted all indexes, deleted all temp querydefs, etc. No matter what I
do, it bloats.
The bloating is happening in the front-end. After a fresh compact, the
front-end will be about 4 MB. It will easily grow to 20+ MB within an
hour. It also is affected by the number of users in it. The more
concurrent users, the faster it will bloat.
I stripped it down as much as physically possible, all the way down to
about a 1 MB back-end, and a 700k front-end. I asked 3 users to test
the front-end (shared accdb on the network drive), and within an hour
it blew up to nearly 3 MB (4x size).
I don''t use temp tables, rarely use append queries, never delete
queries. I always close recordsets when I do any manipulation via VBA.
Always set variables to nothing when I''m done with them. Don''t have
any embedded images or files.
I do, however, use several update queries. But it even bloated when I
completely disabled them (didn''t even have them in the rebuilt front-
end).
I tried putting the front-end on someone''s PC (with the hope to enable
Compact on Close), but the performance was terrible. We will
eventually get there when we move the back-end to SQL, but that''s
probably months out.
I''ve seen a few complaints about this, is it a bug with 2007? It
doesn''t seem normal that it should bloat so big so fast, especially in
the front-end.
Any thoughts? Thanks!
我不知道如何处理一个应用程序，除非你永远不会更新，否则在A97之后不会拆分成前后/后端
。
我建议你去Tony Toews网站阅读他的东西分裂
mdbs。我发现有用的，除了后端的持久链接，
确保我的文件名遵循8.3 DOS格式。那真的是
加速了我。
链接到Tony的网站。 http://www.granite.ab.ca/accsmstr.htm。
http：//www.granite.ab。 ca / access / performancefaq.htm
链接到Allen Browne的网站 http://www.allenbrowne.com/tips.html 。一些
2007的东西。 http://www.allenbrowne.com/tips.html
I don''t know how one can work with an application not split into
front/backend after A97 unless you never make updates.
I suggest you go to Tony Toews site and read his stuff on splitting
mdbs. What I found helpful, besides a persistent link to the backend,
was making sure my filenames followed the 8.3 DOS format. That really
speeded it up for me.
Link to Tony''s site. http://www.granite.ab.ca/accsmstr.htm.
http://www.granite.ab.ca/access/performancefaq.htm
Link to Allen Browne''s site http://www.allenbrowne.com/tips.html. Some
2007 stuff. http://www.allenbrowne.com/tips.html
8月11日，1：49 * pm，Salad< o ... @ vinegar.comwrote：
On Aug 11, 1:49*pm, Salad <o...@vinegar.comwrote:
Brian Nelson写道：
Brian Nelson wrote:
很抱歉这篇长篇文章，我已经努力解决这个问题，但我很好
刚刚卡住！
Sorry for the long post, I''ve tried so hard to solve this one but I''m
just stuck!
多年来我们在网络共享上有一个多用户.mdb文件。我知道
它不太理想，但它从Access 2000到2003都运行良好。自从我们升级到2007年以来，我们已经注意到了
一些严重腹胀。
We''ve had a multiuser .mdb file on a network share for years. I know
it''s not ideal, but it''s worked well from Access 2000 to 2003. Ever
since we upgraded to 2007, we''ve noticed some serious bloating.
当我们在2003年，在一个新的契约之后，数据库将在
大约20 MB。正常使用后，它将增长到大约25 MB然后
显着减慢。在过去的8个月里，它只增长了6 MB。
When we were on 2003, after a fresh compact, the database would be at
about 20 MB. After normal use it would grow to about 25 MB and then
significantly slow down. It only grew a total of 6 MB in the last 8
months.
但是，在我们升级到Office 2007的那一天，数据库膨胀了多达60 MB的b $ b和数量。它似乎永远不会停止，我已经看到它b $ b高达90 MB。如果我压缩它，它会回到大约20 MB。
However, the very day we upgraded to Office 2007, the database bloated
up to 60 MB and counting. It doesn''t ever seem to stop, I''ve seen it
as high as 90 MB. If I compact it, it goes back down to about 20 MB.
我已经尝试了几乎所有我能想到的东西;转换它
到2007格式（accdb），拆分它，做一个accde，反编译和
重新编译代码，导出每个对象作为文本并重建
前端，从头开始重建后端，删除了所有代码，
删除了所有索引，删除了所有临时querydef等等。无论我是什么，我都是
，它膨胀。
I have tried almost everything I can possibly think of; converted it
to 2007 format (accdb), split it, made an accde, decompiled and
recompiled the code, exported every object as text and rebuilt the
front-end, rebuilt the back-end from scratch, removed all code,
deleted all indexes, deleted all temp querydefs, etc. No matter what I
do, it bloats.
膨胀发生在前端。在一个新的紧凑型后，
前端将大约4 MB。它将在
小时内轻松增长到20+ MB。它也受到其中用户数量的影响。并发用户越多，b / b越多，它的膨胀就越快。
The bloating is happening in the front-end. After a fresh compact, the
front-end will be about 4 MB. It will easily grow to 20+ MB within an
hour. It also is affected by the number of users in it. The more
concurrent users, the faster it will bloat.
我尽可能地将其剥离，一直到大约1 MB后端的
，以及一个700k的前端。我让3个用户测试前端（网络驱动器上的共享accdb），并且在一小时内
它爆炸了近3 MB（4x大小） 。
I stripped it down as much as physically possible, all the way down to
about a 1 MB back-end, and a 700k front-end. I asked 3 users to test
the front-end (shared accdb on the network drive), and within an hour
it blew up to nearly 3 MB (4x size).
我不使用临时表，很少使用附加查询，永远不会删除
查询。当我通过VBA进行任何操作时，我总是关闭记录集。
当我完成它们时，总是将变量设置为空。没有
任何嵌入的图像或文件。
I don''t use temp tables, rarely use append queries, never delete
queries. I always close recordsets when I do any manipulation via VBA.
Always set variables to nothing when I''m done with them. Don''t have
any embedded images or files.
但是，我会使用多个更新查询。但是当我完全禁用它们时它甚至臃肿（在重建的前面甚至没有它们 -
结束）。
I do, however, use several update queries. But it even bloated when I
completely disabled them (didn''t even have them in the rebuilt front-
end).
我尝试将前端放在某人的电脑上（希望启用
Compact on Close ），但表现很糟糕。当我们将后端移动到SQL时，我们将最终到达那里
，但那可能需要几个月才能实现
。
I tried putting the front-end on someone''s PC (with the hope to enable
Compact on Close), but the performance was terrible. We will
eventually get there when we move the back-end to SQL, but that''s
probably months out.
我看过一些关于此事的投诉，这是2007年的一个错误吗？它b / b
看起来应该是如此之快，特别是前端的b $ b。
I''ve seen a few complaints about this, is it a bug with 2007? It
doesn''t seem normal that it should bloat so big so fast, especially in
the front-end.
有什么想法吗？谢谢！
Any thoughts? Thanks!
我不知道如何处理一个应用程序，除非你从未做过更新，否则在A97之后不会拆分成前后/后端
。
我建议你去Tony Toews网站阅读他的东西分裂
mdbs。 *我发现有用的，除了后端的持久链接，
确保我的文件名遵循8.3 DOS格式。 *真的
加速了我。
链接到Tony的网站。 * http://www.granite.ab.ca/accsmstr.ht...ormancefaq.htm
链接到Allen Browne的sitehttp：// www .allenbrowne.com / tips.html请。 *一些
2007的东西。 * http://www.allenbrowne.com/tips.html- 隐藏引用的文字 -
- 显示引用的文字 -
I don''t know how one can work with an application not split into
front/backend after A97 unless you never make updates.
I suggest you go to Tony Toews site and read his stuff on splitting
mdbs. *What I found helpful, besides a persistent link to the backend,
was making sure my filenames followed the 8.3 DOS format. *That really
speeded it up for me.
Link to Tony''s site. *http://www.granite.ab.ca/accsmstr.ht...ormancefaq.htm
Link to Allen Browne''s sitehttp://www.allenbrowne.com/tips.html. *Some
2007 stuff. *http://www.allenbrowne.com/tips.html- Hide quoted text -
- Show quoted text -
哦是的，去过Tony'和Allen'' s网站很多次。 :-)
我从来没有遇到过共享.mdb的任何问题，而且很容易更新
，所以我从来没有需要拆分它。但不管怎么说，我发誓要把它分开来面对这个膨胀的问题并且它没有帮助。
我从未想过8.3格式，但是难道不会混淆
Access，因为新的文件扩展名是.accdb吗？
我确定新版本的Access不会我喜欢过去做过的事情，不管是一个新的保留字段名称，一个已弃用的
函数，还是其他类似的东西。如果没有完全重写，我就不会发现它不喜欢的东西。
我见过几个帖子各个网站描述了相同的问题，但是到目前为止，似乎没有人能够弄清楚导致它的是什么'
。
Oh yeah, been to Tony''s and Allen''s sites many times. :-)
I never had any problems with the shared .mdb, and it was easy enough
to update, so I never had a need to split it. But regardless, I DID
try splitting it to confront this bloating problem and it didn''t help.
I never thought about the 8.3 format, but wouldn''t that confuse
Access, being that the new file extension is .accdb?
I''m sure that the new version of Access doesn''t like something I''ve
done in the past, be it a new reserved field name, a deprecated
function, or something along those lines. Short of a total rewrite, I
can''t seem to find what it doesn''t like.
I''ve seen several threads on various sites describing the same
problem, but so far nobody seems to be able to figure out what''s
causing it.
Brian Nelson< bn ************ @ gmail.comwrote：
Brian Nelson <bn************@gmail.comwrote:
>当我们上班时2003年，经过一个新的契约，数据库将在大约20 MB。正常使用后，它将增长到大约25 MB，然后显着减慢。在过去的8个月里，它只增长了6 MB。
>When we were on 2003, after a fresh compact, the database would be at
about 20 MB. After normal use it would grow to about 25 MB and then
significantly slow down. It only grew a total of 6 MB in the last 8
months.
这与我和我的客户的经历有关。虽然它更像是一块200亿美元，但是在压缩后的第一天，它将增长到225 Mb，并且每天增加100 kb
，直到下一次压缩。
That was about my experience as well with my clients. Although it was more like a
200 Mb BE would grow to 225 Mb in the first day after compacting and grow 100 kb per
day until the next compacting.
>然而，在我们升级到Office 2007的那一天，数据库膨胀了高达60 MB并且还在计算中。它似乎永远不会停止，我已经看到它高达90 MB。如果我压缩它，它会回到大约20 MB。
>However, the very day we upgraded to Office 2007, the database bloated
up to 60 MB and counting. It doesn''t ever seem to stop, I''ve seen it
as high as 90 MB. If I compact it, it goes back down to about 20 MB.
我怀疑除了拆分之外你不会得到答案。我可以看到
ACE（DAO的升级）在节省磁盘空间方面效率低于Jet 4.0或Jet
3.5。
例如，如果更新了包含多个记录的页面，则该页面中的所有记录将被移动到其中单独存储的其他页面。因此，一个4 kb页面上的
现在需要20个4 kb的页面。请注意，我不是说这是
的情况，只有一种可能性。
我真的不知道。
Tony
-
Tony Toews，Microsoft Access MVP
请仅在新闻组中回复，以便其他人可以
阅读整个消息主题。
Microsoft Access Links，Hints，Tips&会计系统
http://www.granite.ab.ca /accsmstr.htm
Tony的Microsoft Access博客 - http://msmvps.com/blogs/access/
I suspect you''re not going to get an answer other than splitting. I can see the
ACE (upgrade of DAO) being less efficient at saving disk space than Jet 4.0 or Jet
3.5.
For example if a page containing multiple records is updated then all the records in
that page are moved to other pages where they are individually stored. Thus what
was on one 4 kb page now takes 20 4 kb pages. Note that I''m not stating this is the
case, just one possibility.
Really I have no idea.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony''s Microsoft Access Blog - http://msmvps.com/blogs/access/
这篇关于在Access 2007中绝对难倒在前端膨胀的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
