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:

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
End 
Sub

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!


Copyright© GeeksEngine.com