Custom Search

Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)

My Access database had been doing well for the last 10 years but just the other day it came up with this error. The database uses linked tables to remotely retrieve data from SQL Server database on the network. The error was caused by a complex Make Table Query that joins two local Access tables with a linked SQL Server table. Over the years, the SQL Server table has been building up its size dramatically so the query uses more and more temporary resources on the local PC trying to process the query until it couldn't bear anymore with 2 GB maximum limit reached.

The Access error in text:

The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result.

To solve this problem, my first thought was that it might be a good idea to convert the query to a Pass-Through query. But after investigation, it turned out to be it's impossible to pass local Access tables to the remote SQL Server for data processing as used in the SQL join. Access can bring down remote data but can't send local data.

Then the second idea came to my mind. I could split the query into two queries. First, bring down the relevant data from SQL Server to a local Access table. Second, do the join with local tables. After some test, this approach worked as expected, but there is one problem that makes this approach less than perfect - I have to bring about 2 million records from SQL Server to the local Access database and it has blown the database size by about 200 MB each time. Considering that the maximum Access database size is 2 GB, it'll be good if Access can compact itself after the query completes.

I tried some VBA code found on the net that compact Access database. They worked well but have limitations. The VBA code can only compact another Access database, but can't compact the database it sits in.

Here is the VBA code.

Private Sub Command16_Click()
'Microsoft Jet and Replication objects

Dim objJE As New JRO.JetEngine

Dim strSource As String

Dim strTarget As String

strSource = "C:\MyDB\MonthlyData.mdb"
strTarget = "C:\ MyDB\MonthlyData_Compact.mdb"

objJE.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSource & ";",
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strTarget & ";Jet OLEDB:Engine Type=5;"

'Engine type:
'Access 97 = 4
'Access 2000 = 5

To make this VBA code work, make sure you have selected the Microsoft Jet and Replication Objects library.

VBA Editor -> Tools -> References

Eventually, after some intensive search, I abandoned the VBA approach and opted to an extremely simple way to do the compact. There is an option in Access that compacts the database when you close it.

To enable this option in Access, click File -> Options -> Current Database, then tick "Compact on Close".

On last thing that is crucial for the compact to work, I have to use VBA to delete the local table that stores the SQL Server data after the join query completes its mission. Otherwise, the compact would have achieved nothing. Here is the code.

DoCmd.DeleteObject acTable, "monthly_sales_local_version"

I choose to delete this table rather than deleting all records in it (so kept the table) because the query is a Make Table Query, I don't have to worry about creating the table, the query would create it automatically. Deleting 2 million records in the table would take quite some time but deleting a table is instant.

If you do feel there is a need to use the VBA compact code, you can create another database that is used solely for query processing of large queries and then compact. After all is done, you bring the result into the main Access database for further processing. This way, the second database works as a centralized staging area. If one day the second database is oversized (2 GB), you can build a third Access database for this purpose, or consider upgrading your application to use an enterprise DBMS system such SQL Server, Oracle, etc., if that's possible.

Happy Coding!


Other Recent Articles from the MS Access category:

1.How to easily get a list of field names in MS Access
2.How to count distinct records in MS Access
3.How to do transaction based processing in MS Access
4.How to open a document (local/network file or web page) from MS Access
5.How to use ADOX to create unique composite index - the VBA approach
6.How to do cross-table update queries in MS Access - the right way
7.Three efficient ways to get the number of records by using VBA
8.How to create a composite unique index (not as a primary key) in MS Access
9.Use VBA to get the correct number of records in a Recordset object
10.Disable Access Prompt when a record is changed, table deleted, or action queries run
11.How to hide and unhide a MS Access object
12.How to return multiple values from a VBA function (Part 3)
13.How to return multiple values from a VBA function (Part 2)
14.How to return multiple values from a VBA function (Part 1)
15.Three ways to programmatically duplicate a table in MS Access by VBA
16.Create a DLL by CSharp or VB.Net for VBA
17.How to correctly reference and call a DLL
18.How to register a C# or VB.Net DLL
19.Email address validation by Regular Expressions using VBA
20.Fix MS Access error: Query must have at least one destination field
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

Home | Feedback | Terms of Use | Privacy Policy