I was inserting data into a MS Access database using JDBC-ODBC driver. The blank mdb file was 2KB. After populating this database, the size grew to 155MB. Then I was deleting the data. But I found the size of mdb remains the same as 155MB. I don't get any errors. But is it normal this way? I would expect the file size reduces. If it is designed in this way, what is the idea behind it? Thanks
我使用JDBC-ODBC驱动程序将数据插入MS Access数据库。空白的mdb文件是2KB。填充此数据库后,大小增加到155MB。然后我删除了数据。但我发现mdb的大小仍然与155MB相同。我没有任何错误。但这种方式是正常的吗?我希望文件大小减少。如果以这种方式设计,背后的想法是什么?谢谢
5 个解决方案
#1
37
MS Access doesn't reclaim the space for records until you have compacted the database.
在压缩数据库之前,MS Access不会回收记录空间。
This is something you should do to an access database as part of your regularly maintenance otherwise you will end up with some pretty painful problems.
作为定期维护的一部分,您应该对访问数据库执行此操作,否则您将遇到一些非常痛苦的问题。
You can compact a database either through the MS Access UI (Tools -> Database Utilities ->
Compact and Repair Database) of you can use the command prompt using:
您可以通过MS Access UI(工具 - >数据库实用程序 - >压缩和修复数据库)压缩数据库,您可以使用命令提示符:
msaccess.exe "target database.accdb" /compact
N.B. the /Compact switch must be after the target database
注: / Compact开关必须位于目标数据库之后
#2
19
MS Access doesn't free up space used by records even after they are deleted. You can free the space manually when you need to or automatically each time you close the application.
即使删除了记录,MS Access也不会释放记录使用的空间。您可以在需要时手动释放空间,或在每次关闭应用程序时自动释放空间。
To do it manually, use the Compact and Repair utility:
要手动执行此操作,请使用“压缩和修复”实用程序:
-
Backup your database, as there is a bug in Access 2007 that may delete your database during the compacting procedure.
备份数据库,因为Access 2007中存在可能在压缩过程中删除数据库的错误。
-
If you are compacting a multiuser (shared) database that is located on a server or shared folder, make sure that no one else has it open.
如果要压缩位于服务器或共享文件夹上的多用户(共享)数据库,请确保没有其他人打开它。
-
On the Tools menu, point to Database Utilities, and then click Compact and Repair Database.
在“工具”菜单上,指向“数据库实用工具”,然后单击“压缩和修复数据库”。
To do it automatically when you close the application:
要在关闭应用程序时自动执行此操作:
-
Open the database that you want MS Access to compact automatically.
打开您希望MS Access自动压缩的数据库。
-
On the Tools menu, click Options, and then choose the General tab.
在“工具”菜单上,单击“选项”,然后选择“常规”选项卡。
-
Select the Compact On Close check box.
选中Compact On Close复选框。
After deleting the data and compacting the database don't be surprised if is still larger than 100 KB. There is a certain amount of overhead that cannot be removed after you add data the first time.
删除数据并压缩数据库后,如果仍然大于100 KB,请不要感到惊讶。第一次添加数据后,有一定数量的开销无法删除。
Also, beware that AutoNumber field values behave differently than advertised after the compacting procedure: According to the MS Access 2000 documentation, if you delete records from the end of a table that has an AutoNumber field, compacting the database resets the AutoNumber value. So the AutoNumber value of the next record you add will be one greater than the AutoNumber value of the last undeleted record in the table.
此外,请注意自动编号字段值的行为与压缩过程后公布的不同:根据MS Access 2000文档,如果从具有“自动编号”字段的表的末尾删除记录,则压缩数据库会重置“自动编号”值。因此,您添加的下一条记录的AutoNumber值将大于表中最后一条未删除记录的AutoNumber值。
I have not found this to be the case: If you have 100 Autonumbered records and delete the last 50, the next AutoNumber record (according to the documentation) should be numbered "51". But in my experience it is numbered "101", instead.
我没有发现这种情况:如果您有100个自动编号记录并删除最后50个,则下一个自动编号记录(根据文档)应编号为“51”。但根据我的经验,它编号为“101”。
#3
2
The first stop, as mentioned should be attempting to compact/repair the database. However you can also get some size saving past that by creating a new database and importing all of the objects from the old.
如上所述,第一站应该尝试压缩/修复数据库。但是,通过创建新数据库并从旧数据库导入所有对象,您还可以节省一些大小。
#4
1
You can compact the database from code using JRO. See: http://support.microsoft.com/kb/230501
您可以使用JRO从代码压缩数据库。请参阅:http://support.microsoft.com/kb/230501
#5
-2
The first stop, as mentioned should be attempting to compact/repair the database. However you can also get some size saving past that by creating a new database and importing all of the objects from the old. Past that, converting it to an MDE should get you a hair more. As always, don't play around with your production copy. Also if you go with an MDE, make sure you have properly split the database first. (And of course keep a copy of the source MDB should you need to make modifications in the future.)
如上所述,第一站应该尝试压缩/修复数据库。但是,通过创建新数据库并从旧数据库导入所有对象,您还可以节省一些大小。过去,将它转换为MDE可以让你更多。与往常一样,不要使用您的生产副本。此外,如果您使用MDE,请确保首先正确拆分数据库。 (当然,如果您将来需要进行修改,请保留源MDB的副本。)
#1
37
MS Access doesn't reclaim the space for records until you have compacted the database.
在压缩数据库之前,MS Access不会回收记录空间。
This is something you should do to an access database as part of your regularly maintenance otherwise you will end up with some pretty painful problems.
作为定期维护的一部分,您应该对访问数据库执行此操作,否则您将遇到一些非常痛苦的问题。
You can compact a database either through the MS Access UI (Tools -> Database Utilities ->
Compact and Repair Database) of you can use the command prompt using:
您可以通过MS Access UI(工具 - >数据库实用程序 - >压缩和修复数据库)压缩数据库,您可以使用命令提示符:
msaccess.exe "target database.accdb" /compact
N.B. the /Compact switch must be after the target database
注: / Compact开关必须位于目标数据库之后
#2
19
MS Access doesn't free up space used by records even after they are deleted. You can free the space manually when you need to or automatically each time you close the application.
即使删除了记录,MS Access也不会释放记录使用的空间。您可以在需要时手动释放空间,或在每次关闭应用程序时自动释放空间。
To do it manually, use the Compact and Repair utility:
要手动执行此操作,请使用“压缩和修复”实用程序:
-
Backup your database, as there is a bug in Access 2007 that may delete your database during the compacting procedure.
备份数据库,因为Access 2007中存在可能在压缩过程中删除数据库的错误。
-
If you are compacting a multiuser (shared) database that is located on a server or shared folder, make sure that no one else has it open.
如果要压缩位于服务器或共享文件夹上的多用户(共享)数据库,请确保没有其他人打开它。
-
On the Tools menu, point to Database Utilities, and then click Compact and Repair Database.
在“工具”菜单上,指向“数据库实用工具”,然后单击“压缩和修复数据库”。
To do it automatically when you close the application:
要在关闭应用程序时自动执行此操作:
-
Open the database that you want MS Access to compact automatically.
打开您希望MS Access自动压缩的数据库。
-
On the Tools menu, click Options, and then choose the General tab.
在“工具”菜单上,单击“选项”,然后选择“常规”选项卡。
-
Select the Compact On Close check box.
选中Compact On Close复选框。
After deleting the data and compacting the database don't be surprised if is still larger than 100 KB. There is a certain amount of overhead that cannot be removed after you add data the first time.
删除数据并压缩数据库后,如果仍然大于100 KB,请不要感到惊讶。第一次添加数据后,有一定数量的开销无法删除。
Also, beware that AutoNumber field values behave differently than advertised after the compacting procedure: According to the MS Access 2000 documentation, if you delete records from the end of a table that has an AutoNumber field, compacting the database resets the AutoNumber value. So the AutoNumber value of the next record you add will be one greater than the AutoNumber value of the last undeleted record in the table.
此外,请注意自动编号字段值的行为与压缩过程后公布的不同:根据MS Access 2000文档,如果从具有“自动编号”字段的表的末尾删除记录,则压缩数据库会重置“自动编号”值。因此,您添加的下一条记录的AutoNumber值将大于表中最后一条未删除记录的AutoNumber值。
I have not found this to be the case: If you have 100 Autonumbered records and delete the last 50, the next AutoNumber record (according to the documentation) should be numbered "51". But in my experience it is numbered "101", instead.
我没有发现这种情况:如果您有100个自动编号记录并删除最后50个,则下一个自动编号记录(根据文档)应编号为“51”。但根据我的经验,它编号为“101”。
#3
2
The first stop, as mentioned should be attempting to compact/repair the database. However you can also get some size saving past that by creating a new database and importing all of the objects from the old.
如上所述,第一站应该尝试压缩/修复数据库。但是,通过创建新数据库并从旧数据库导入所有对象,您还可以节省一些大小。
#4
1
You can compact the database from code using JRO. See: http://support.microsoft.com/kb/230501
您可以使用JRO从代码压缩数据库。请参阅:http://support.microsoft.com/kb/230501
#5
-2
The first stop, as mentioned should be attempting to compact/repair the database. However you can also get some size saving past that by creating a new database and importing all of the objects from the old. Past that, converting it to an MDE should get you a hair more. As always, don't play around with your production copy. Also if you go with an MDE, make sure you have properly split the database first. (And of course keep a copy of the source MDB should you need to make modifications in the future.)
如上所述,第一站应该尝试压缩/修复数据库。但是,通过创建新数据库并从旧数据库导入所有对象,您还可以节省一些大小。过去,将它转换为MDE可以让你更多。与往常一样,不要使用您的生产副本。此外,如果您使用MDE,请确保首先正确拆分数据库。 (当然,如果您将来需要进行修改,请保留源MDB的副本。)