Translate

2014年3月28日 星期五

[VBA] Cautions of processing very large volume of data in MS ACCESS 2003

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