Access 2010 DB Exploding in Size
up vote
0
down vote
favorite
I have an Access database that I created from an Excel spreadsheet.
The DB contains a main table with 58 fields, three other tables that have about 10 fields, and a last table with two fields. Most of the new information is going into the main table.
After a days use (two users), the database is exploding from 20MB to 50MB. Some new records are being added, but not enough to generate 30MB. My main table had 4000 records after I finished my import from Excel and compacted the database.
The users have not added more than a few dozen new records and the database drops back down to around 20MB after a compact. Is this something that is common in 2010? Never experienced this with 2000/2003.
Thanks,
Westley
ms-access-2010
asked Apr 20 '11 at 23:32
Westley
111
add a comment |
up vote
0
down vote
favorite
I have an Access database that I created from an Excel spreadsheet.
The DB contains a main table with 58 fields, three other tables that have about 10 fields, and a last table with two fields. Most of the new information is going into the main table.
After a days use (two users), the database is exploding from 20MB to 50MB. Some new records are being added, but not enough to generate 30MB. My main table had 4000 records after I finished my import from Excel and compacted the database.
The users have not added more than a few dozen new records and the database drops back down to around 20MB after a compact. Is this something that is common in 2010? Never experienced this with 2000/2003.
Thanks,
Westley
ms-access-2010
asked Apr 20 '11 at 23:32
Westley
111
Is there a lot of editing/deleting of rows
– rerun
Apr 20 '11 at 23:34
Terrible advice @Aaron Kempf: the last thing you want to be doing with SQL Server is auto shrinking a database!
– Mitch Wheat
Apr 4 '17 at 9:24
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have an Access database that I created from an Excel spreadsheet.
The DB contains a main table with 58 fields, three other tables that have about 10 fields, and a last table with two fields. Most of the new information is going into the main table.
After a days use (two users), the database is exploding from 20MB to 50MB. Some new records are being added, but not enough to generate 30MB. My main table had 4000 records after I finished my import from Excel and compacted the database.
The users have not added more than a few dozen new records and the database drops back down to around 20MB after a compact. Is this something that is common in 2010? Never experienced this with 2000/2003.
Thanks,
Westley
ms-access-2010
asked Apr 20 '11 at 23:32
Westley
111
I have an Access database that I created from an Excel spreadsheet.
The DB contains a main table with 58 fields, three other tables that have about 10 fields, and a last table with two fields. Most of the new information is going into the main table.
After a days use (two users), the database is exploding from 20MB to 50MB. Some new records are being added, but not enough to generate 30MB. My main table had 4000 records after I finished my import from Excel and compacted the database.
The users have not added more than a few dozen new records and the database drops back down to around 20MB after a compact. Is this something that is common in 2010? Never experienced this with 2000/2003.
Thanks,
Westley
ms-access-2010
ms-access-2010
asked Apr 20 '11 at 23:32
Westley
111
asked Apr 20 '11 at 23:32
Westley
111
asked Apr 20 '11 at 23:32
Westley
111
asked Apr 20 '11 at 23:32
Westley
111
asked Apr 20 '11 at 23:32
Westley
111
111
Is there a lot of editing/deleting of rows
– rerun
Apr 20 '11 at 23:34
Terrible advice @Aaron Kempf: the last thing you want to be doing with SQL Server is auto shrinking a database!
– Mitch Wheat
Apr 4 '17 at 9:24
add a comment |
Is there a lot of editing/deleting of rows
– rerun
Apr 20 '11 at 23:34
Terrible advice @Aaron Kempf: the last thing you want to be doing with SQL Server is auto shrinking a database!
– Mitch Wheat
Apr 4 '17 at 9:24
Is there a lot of editing/deleting of rows
– rerun
Apr 20 '11 at 23:34
Is there a lot of editing/deleting of rows
– rerun
Apr 20 '11 at 23:34
Terrible advice @Aaron Kempf: the last thing you want to be doing with SQL Server is auto shrinking a database!
– Mitch Wheat
Apr 4 '17 at 9:24
Terrible advice @Aaron Kempf: the last thing you want to be doing with SQL Server is auto shrinking a database!
– Mitch Wheat
Apr 4 '17 at 9:24
add a comment |
2 Answers
2
active
oldest
votes
up vote
4
down vote
Yes, it is common.
The most common causes of Access
database bloat are over-use of
temporary tables and over-use of
non-querydef SQL. Whenever you code an
SQL string outside of a querydef,
Access must "bind" that statement
EVERY time it is run. This process
takes a small amount of time and uses
a large amount of workspace that is
not recovered until you compact the
db. Querydef's are "bound" when they
are saved and when the db is
compacted. Ref.
Lots of insertions/deletes will also cause growth.
ACC: Database Grows Rapidly When You Modify SQL Statements
Of interest?: Growing MS Access File Size problem
edited May 23 '17 at 11:46
Community♦
11
answered Apr 20 '11 at 23:53
Mitch Wheat
252k35404498
add a comment |
up vote
0
down vote
Pictures in reports can also cause the database to grow in size
I have found another way that an access database can increase in size. If you have a report (and possibly also a form?) where there are pictures that are inserted from an external file then one of the "picture type" options is 'shared'. If this option is used then Access stores a new local copy of the picture every time a different picture is used. My 1MByte Access database ended up with 500MByte of internally stored pictures. There does not appear to be any way of flushing them and compacting the database does not clear them.
The solution is to use the 'linked' "picture type" setting. I copied the database contents to a new database, and it has stayed at 1MByte.
answered Nov 22 at 12:40
Nigel Dyer
1
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f5737823%2faccess-2010-db-exploding-in-size%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
4
down vote
Yes, it is common.
The most common causes of Access
database bloat are over-use of
temporary tables and over-use of
non-querydef SQL. Whenever you code an
SQL string outside of a querydef,
Access must "bind" that statement
EVERY time it is run. This process
takes a small amount of time and uses
a large amount of workspace that is
not recovered until you compact the
db. Querydef's are "bound" when they
are saved and when the db is
compacted. Ref.
Lots of insertions/deletes will also cause growth.
ACC: Database Grows Rapidly When You Modify SQL Statements
Of interest?: Growing MS Access File Size problem
edited May 23 '17 at 11:46
Community♦
11
answered Apr 20 '11 at 23:53
Mitch Wheat
252k35404498
add a comment |
up vote
4
down vote
Yes, it is common.
The most common causes of Access
database bloat are over-use of
temporary tables and over-use of
non-querydef SQL. Whenever you code an
SQL string outside of a querydef,
Access must "bind" that statement
EVERY time it is run. This process
takes a small amount of time and uses
a large amount of workspace that is
not recovered until you compact the
db. Querydef's are "bound" when they
are saved and when the db is
compacted. Ref.
Lots of insertions/deletes will also cause growth.
ACC: Database Grows Rapidly When You Modify SQL Statements
Of interest?: Growing MS Access File Size problem
edited May 23 '17 at 11:46
Community♦
11
answered Apr 20 '11 at 23:53
Mitch Wheat
252k35404498
add a comment |
up vote
4
down vote
up vote
4
down vote
Yes, it is common.
The most common causes of Access
database bloat are over-use of
temporary tables and over-use of
non-querydef SQL. Whenever you code an
SQL string outside of a querydef,
Access must "bind" that statement
EVERY time it is run. This process
takes a small amount of time and uses
a large amount of workspace that is
not recovered until you compact the
db. Querydef's are "bound" when they
are saved and when the db is
compacted. Ref.
Lots of insertions/deletes will also cause growth.
ACC: Database Grows Rapidly When You Modify SQL Statements
Of interest?: Growing MS Access File Size problem
edited May 23 '17 at 11:46
Community♦
11
answered Apr 20 '11 at 23:53
Mitch Wheat
252k35404498
Yes, it is common.
The most common causes of Access
database bloat are over-use of
temporary tables and over-use of
non-querydef SQL. Whenever you code an
SQL string outside of a querydef,
Access must "bind" that statement
EVERY time it is run. This process
takes a small amount of time and uses
a large amount of workspace that is
not recovered until you compact the
db. Querydef's are "bound" when they
are saved and when the db is
compacted. Ref.
Lots of insertions/deletes will also cause growth.
ACC: Database Grows Rapidly When You Modify SQL Statements
Of interest?: Growing MS Access File Size problem
edited May 23 '17 at 11:46
Community♦
11
answered Apr 20 '11 at 23:53
Mitch Wheat
252k35404498
edited May 23 '17 at 11:46
Community♦
11
edited May 23 '17 at 11:46
Community♦
11
edited May 23 '17 at 11:46
Community♦
11
11
answered Apr 20 '11 at 23:53
Mitch Wheat
252k35404498
answered Apr 20 '11 at 23:53
Mitch Wheat
252k35404498
answered Apr 20 '11 at 23:53
Mitch Wheat
252k35404498
252k35404498
add a comment |
add a comment |
up vote
0
down vote
Pictures in reports can also cause the database to grow in size
I have found another way that an access database can increase in size. If you have a report (and possibly also a form?) where there are pictures that are inserted from an external file then one of the "picture type" options is 'shared'. If this option is used then Access stores a new local copy of the picture every time a different picture is used. My 1MByte Access database ended up with 500MByte of internally stored pictures. There does not appear to be any way of flushing them and compacting the database does not clear them.
The solution is to use the 'linked' "picture type" setting. I copied the database contents to a new database, and it has stayed at 1MByte.
answered Nov 22 at 12:40
Nigel Dyer
1
add a comment |
up vote
0
down vote
Pictures in reports can also cause the database to grow in size
I have found another way that an access database can increase in size. If you have a report (and possibly also a form?) where there are pictures that are inserted from an external file then one of the "picture type" options is 'shared'. If this option is used then Access stores a new local copy of the picture every time a different picture is used. My 1MByte Access database ended up with 500MByte of internally stored pictures. There does not appear to be any way of flushing them and compacting the database does not clear them.
The solution is to use the 'linked' "picture type" setting. I copied the database contents to a new database, and it has stayed at 1MByte.
answered Nov 22 at 12:40
Nigel Dyer
1
add a comment |
up vote
0
down vote
up vote
0
down vote
Pictures in reports can also cause the database to grow in size
I have found another way that an access database can increase in size. If you have a report (and possibly also a form?) where there are pictures that are inserted from an external file then one of the "picture type" options is 'shared'. If this option is used then Access stores a new local copy of the picture every time a different picture is used. My 1MByte Access database ended up with 500MByte of internally stored pictures. There does not appear to be any way of flushing them and compacting the database does not clear them.
The solution is to use the 'linked' "picture type" setting. I copied the database contents to a new database, and it has stayed at 1MByte.
answered Nov 22 at 12:40
Nigel Dyer
1
Pictures in reports can also cause the database to grow in size
I have found another way that an access database can increase in size. If you have a report (and possibly also a form?) where there are pictures that are inserted from an external file then one of the "picture type" options is 'shared'. If this option is used then Access stores a new local copy of the picture every time a different picture is used. My 1MByte Access database ended up with 500MByte of internally stored pictures. There does not appear to be any way of flushing them and compacting the database does not clear them.
The solution is to use the 'linked' "picture type" setting. I copied the database contents to a new database, and it has stayed at 1MByte.
answered Nov 22 at 12:40
Nigel Dyer
1
answered Nov 22 at 12:40
Nigel Dyer
1
answered Nov 22 at 12:40
Nigel Dyer
1
answered Nov 22 at 12:40
Nigel Dyer
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f5737823%2faccess-2010-db-exploding-in-size%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Is there a lot of editing/deleting of rows
– rerun
Apr 20 '11 at 23:34
Terrible advice @Aaron Kempf: the last thing you want to be doing with SQL Server is auto shrinking a database!
– Mitch Wheat
Apr 4 '17 at 9:24