In MS ACCESS 2003 of before version, there is about 2GB maximum limit in the size of the MDB file.
It is very suck that the MDB will corrupt when VBA is running so that the size of the MDB increase over the 2GB limit. The VBE of the corrupted MDB cannot be opened in anyway, so that any VB code or modules are not able to be exported outside for recovery. Therefore, pls notice below cautions.
1. Always make a copy of your newest MDB.
2. Always create large tables outside the working MDB, below is an example using my VbaUtilities.
Dim Db_Sample_path As String
Db_Sample_path = "./Sample.mdb"
DelTable ("Sample")
DoCmd.TransferDatabase acLink, "Microsoft Access", Db_Sample_path, acTable, "Sample", "Sample", True
Dim Db_Sample_l_path As String
Db_Sample_l_path = CurrentProject.Path & "\" & "Sample_local.mdb"
Kill Db_Sample_l_path
Call DBEngine.CreateDatabase(Db_Sample_l_path, dbLangGeneral)
Dim SQL_cmd As String
SQL_cmd = "SELECT * " & vbCrLf & _
"INTO [MS Access;DATABASE=" & Db_Sample_l_path & ";].[Sample_local]" & vbCrLf & _
"FROM [Sample]" & vbCrLf & _
";"
RunSQL_CmdWithoutWarning (SQL_cmd)
DelTable ("Sample_local")
DoCmd.TransferDatabase acLink, "Microsoft Access", Db_Sample_l_path, acTable, "Sample_local", "Sample_local", True