.net 如何增加 MS Access 2007 数据库大小？

How to increase MS Access 2007 database size?

提问by sandeep nagabhairava

I developed a windows application, back end DB is Access 2007. I heard that max limit of Access 2007 is 2GB.

我开发了一个 windows 应用程序，后端数据库是 Access 2007。听说 Access 2007 的最大限制是 2GB。

Now my question is, is there any way to increase the size beyond that limit? How to create more than one db for a application to increase size and performance?

现在我的问题是，有什么方法可以将大小增加到超过该限制？如何为应用程序创建多个数据库以增加大小和性能？

回答by HansUp

You can partition your data into one or more additional database files, then create links to the satellite tables from your main application database.

您可以将数据划分为一个或多个附加数据库文件，然后从主应用程序数据库创建到卫星表的链接。

Although that strategy could allow you to use more than 2 GB of data stored in Access, it is not a great choice. One pitfall is that you can't enforce referential integrity between tables in different db files; that fact alone could make partitioning a non-starter for many applications.

尽管该策略可以让您使用存储在 Access 中的超过 2 GB 的数据，但这并不是一个很好的选择。一个陷阱是你不能在不同 db 文件中的表之间强制执行参照完整性；仅凭这一事实就可能使分区成为许多应用程序的不便之处。

Another issue is temporary work space. When the db engine needs a disk file for work space, it uses a temporary file. And that temporary file is also restricted to a max of 2 GB. So if you have 6 GB of data spread between 4 db files of 1.5 GB each, and you need to do something which requires working with more than 2 GB of that data, you could get an error message complaining about lack of disk space. It doesn't mean the disk is full; it means the required temporary work space is greater than 2 GB.

另一个问题是临时工作空间。当数据库引擎需要一个磁盘文件作为工作空间时，它使用一个临时文件。并且该临时文件也被限制为最大 2 GB。因此，如果您有 6 GB 的数据分布在 4 个 1.5 GB 的 db 文件之间，并且您需要做一些需要处理超过 2 GB 的数据，您可能会收到一条错误消息，抱怨磁盘空间不足。这并不意味着磁盘已满；这意味着所需的临时工作空间大于 2 GB。

Having actually done that partitioning once in the past, I don't want to ever do it again. Asking Access to manage that much data is just unreasonable; I had to spend way too much time waiting on it to do nearly anything I asked. It's much better to off-load that work to a more capable database system. So it's not only an issue of total storage capacity, but also how well the storage database can cope with large data sets. Your Access application could become a client to a client-server database.

过去确实做过一次分区，我不想再做一次。要求 Access 管理这么多数据是不合理的；我不得不花太多时间等待它来完成我要求的几乎任何事情。将这些工作卸载到功能更强大的数据库系统会更好。所以这不仅是总存储容量的问题，也是存储数据库对大数据集的处理能力的问题。您的 Access 应用程序可以成为客户端-服务器数据库的客户端。

I also think you should critically examine what you're storing. For example, some folks like to store images. I don't. Instead I store the path to an image file. Same for other file types. Perhaps you might find you can live without storing BLOBs, you can reduce your db size comfortably below the 2 GB limit, and continue with Access as storage.

我还认为您应该批判性地检查您存储的内容。例如，有些人喜欢存储图像。我不。相反，我存储图像文件的路径。其他文件类型也一样。也许您可能会发现不存储 BLOB 也可以生存，您可以轻松地将 db 大小降低到 2 GB 限制以下，并继续使用 Access 作为存储。

回答by Dariusz

The maximum database size is 2GB minus space required for system objects, but you can try one workaround: by splitting database. For more informations see:

最大数据库大小为 2GB 减去系统对象所需的空间，但您可以尝试一种解决方法：通过拆分数据库。有关更多信息，请参阅：

Office.com - Split an Access database

Office.com - 拆分 Access 数据库

MS KB304932 - How to manually split Access database

MS KB304932 - 如何手动拆分 Access 数据库

回答by user2450064

I had the same problem when my DB reaches 2GB upon importing my external data in the tables. I just disable Cache on the settings

当我的数据库在表中导入外部数据时达到 2GB 时，我遇到了同样的问题。我只是在设置中禁用缓存

Go to File > Options > Current Database

转到文件 > 选项 > 当前数据库

Under Caching Web Service and Sharepoint Tables

在缓存 Web 服务和 Sharepoint 表下

Check 'Use the cache format that is compatible with Microsoft Access 2010 and later'

选中“使用与 Microsoft Access 2010 及更高版本兼容的缓存格式”

Check 'Clear Cache on Close'

勾选“关闭时清除缓存”

Check 'Never Cache'

选中“从不缓存”

回答by EA4388

I had the same problem on a db that had a lot of input over 17 years and was closing on the 2GB limit.It was already split so nothing could be done there. In the end I found by fiddling with the biggest table that the problem was embedded JPG images of which there was a very large number - It was popular for example to use an embedded image of employees or workers in tables in access. The solution which I am now implementing is going well. All the picures are in a file anyway so devise a new form that uses hyperlink to link to the pictures with a little code. delete the embedded images in the backend.I have already simulated this on a DB copy. It reduces size from 1.6 GB to less than 300 MB- so befor resorting to SQL you could look at that as a solution

我在一个 db 上遇到了同样的问题，该数据库在 17 年间有大量输入，并且接近 2GB 的限制。它已经被拆分，所以在那里什么也做不了。最后，我通过摆弄最大的表格发现问题在于嵌入的 JPG 图像数量非常多 - 例如，在访问中使用表格中的员工或工人的嵌入图像是很流行的。我现在正在实施的解决方案进展顺利。无论如何，所有图片都在一个文件中，因此设计一种新形式，使用超链接通过少量代码链接到图片。删除后端中的嵌入图像。我已经在数据库副本上模拟了这一点。它将大小从 1.6 GB 减少到小于 300 MB-因此在求助于 SQL 之前，您可以将其视为解决方案