Access tables and databases size limitation

[Antonio Linares]

Rick,

I googled for this and found that it seems as 2GB is the size limit for Access databases.

I appreciate your comments, thanks

[Rick Lipkin]

Antonio

YES, 2gb seems to be the maximum size. The largest ( single ) .Mdb I have in production is well under 100mg. I will say that one irritating feature of Ms Access is that you must Compact and Repair occasionally because when you delete a record the size of the database does not get smaller and to permanently expunge deleted records, you must run the Compact and Repair utility

With that said, you can use multiple Access databases and have multiple connections to link databases, so theoretically size is not an issue.

I do like Ms Access for projects that have 10-50 concurrent users ( for small business ), but for Enterprise applications, I prefer Ms Sql Server or the free version of Sql Express that has a 10gb limitation 'per database' and of course you can have multiple databases per instance.

Here is my Compact and Repair utility for Ms Access that can be incorporated into any FiveWin ADO Application. If I am not mistaken, Enrico helped me with some of this code.

Rick Lipkin

Code: Select all  Expand view

//-- RepairUm.prg

#INCLUDE "FIVEWIN.CH"

//----------------------
Func _RepairUm()

LOCAL SAYING,oBtn1,oBtn2
Local oSay1,oSay2,oFontB,cLine,oLine
Local lOk,cDefa,cSay1,cSay2,oRs,cSql,oErr
Local oBmp

If xDatabase = "A"
Else
   Saying := "Compacting and Repair only Applies to Ms Access"
   MsgInfo( Saying )
   Return(.f.)
Endif

oFontB := TFont():New("Ms Sans Serif",,-6,.F.,.T. ,,,,.F. )
cDefa  := set(7)

xConnect:CLose() // close global connection
StandardGrad()

If File( cDefa+"\BillingNew.Mdb" )
   Ferase( cDefa+"\BillingNew.Mdb" )
Endif

cSay1 := "  "+chr(10)
cSay1 += "Compact and Repair Database attemts to remove "
cSay1 += "Deleted records and shrink the size of the database. "

cSay2 := "  "+chr(10)
cSay2 += "This routine will need EXCLUSIVE rights to run and "
cSay2 += "No One Else can be logged in at the same time."+chr(10)

DEFINE BITMAP oBmp RESOURCE "SETUP"
DEFINE DIALOG oDlg RESOURCE "REPAIRUM"            ;
       TITLE "Compact and Repair Utility"         ;

    REDEFINE SAY oSay1 var cSay1 ID 115 of oDlg UPDATE
       oSay1:SetFont( oFontB )
       oSay1:SetColor( nRgb(7,7,224)) // blue
    REDEFINE SAY oSay2 var cSay2 ID 113 of oDlg UPDATE
       oSay2:SetFont( oFontB )
       oSay2:SetColor( nRgb(CLR_HRED)) // red

    REDEFINE SAY oLINE var cLINE MEMO ID 129 of oDLG UPDATE
        oLine:SetFont( oFontB )

    REDEFINE BTNBMP oBtn1 ID 111 of oDlg   ;
         RESOURCE "OK", "DOK", "DOK" ;
         PROMPT "  &Run   " LEFT 2007;
         ACTION ( lOk := _doit(@cLine,oLine,oDlg,oBtn1,oBtn2))


    REDEFINE BTNBMP oBtn2 ID 112 of oDlg   ;
         RESOURCE "CANCEL", "DCANCEL", "DCANCEL" ;
         PROMPT "&Cancel   " LEFT 2007;
         ACTION ( lOK := .t., oDlg:ENd() )


    ACTIVATE DIALOG oDlg ;
         ON PAINT (PalBmpDraw( hDC, 0, 0, oBmp:hBitmap )) ;
         VALID (!GETKEYSTATE( 27 ))   // do not allow esc key here


RELEASE FONT oFontB
LightGreyGrad()

SysReFresh()

// re-establish global connection
TRY
   xConnect:Open( xString )
CATCH oErr
   Saying := "Could not open a Global Connection to Database "+xSource
   MsgInfo( Saying )
   RETURN(.F.)
END TRY

oBmp:End()

RETURN( lOk )

//----------------------
Static FUNC _Doit(cLine,oLine,oDlg,oBtn1,oBtn2)

Local Saying,cCn1,cCn2,cDefa,yConnect,ySource
Local oJro

SysReFresh()

cDefa := set(7)

oBtn1:Hide()
oBtn2:Hide()

cLine := "Checking for Exclusive Use to Database"+chr(10)
cLine += xSource
oLine:ReFresh()
SysReFresh()
SysWait(1)

Ferase( cDefa+"\Billing.Ldb" )
SysWait(2)

If File( cDefa+"\Billing.Ldb" )
   cLine := "Checking for Exclusive Use to Database "+chr(10)
   cLine += xSource+"   Failed..."+chr(10)
   cLine += "This Process may Re-Start"+chr(10)
   oLine:ReFresh()
   SysReFresh()
   SysWait(5)

   oDlg:End()
   Return(.f.)
Endif


ySOURCE  := cDEFA+"\BillingNew.mdb"

cLine := "Atempting to create JRO.JetEngine"
oLine:ReFresh()
SysReFresh()
SysWait(2)

Try
   oJro := CREATEOBJECT( "JRO.JetEngine" )
Catch
   cLine := "Atempting to create JRO.JetEngine    Failed"
   oLine:ReFresh()
   SysReFresh()
   SysWait(.5)

   Msginfo( "Error in Creating JRO.JetEngine" )
   oDlg:End()
   Return(.t.)
End Try

cLine := "Compacting Database to "+chr(10)
cLine += ySource+chr(10)
oLine:ReFresh()
SysReFresh()
SysWait(2)

cCn1 := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
cCn1 += cDefa+"\Billing.mdb;"
cCn1 += "Jet OLEDB:Database Password="+xPassword
cCn2 := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
cCn2 += cDefa+"\BillingNew.mdb;"
cCn2 += "Jet OLEDB:Database Password="+xPassword

Try
  oJro:CompactDatabase(cCn1,cCn2 )
Catch
   cLine := "Compacting Database to "+chr(10)
   cLine += ySource+"  Failed .. "
   cLine += "This Process may Re-Start"+chr(10)
   oLine:ReFresh()
   SysReFresh()
   oDlg:End()
   Return(.f.)
End Try

cLine := "Compacting Database to "+chr(10)
cLine += ySource+"  Successful"+chr(10)
oLine:ReFresh()
SysReFresh()
SysWait(3)

If File( cDefa+"\BillingNew.mdb" )

   cLine := "Deleating Old Database backup file "+chr(10)
   cLine += cDefa+"\Billing._db"+chr(10)
   oLine:ReFresh()
   SysReFresh()
   SysWait(2)

   Ferase( cDefa+"\Billing._db" )

   cLine := "Copying "+cDefa+"\Billing.Mdb to "+chr(10)
   cLine += cDefa+"\Billing._db"+chr(10)
   oLine:ReFresh()
   SysReFresh()
   SysWait(2)

   Copy File( cDefa+"\Billing.Mdb" ) to ( cDefa+"\Billing._db" )

   cLine := "Checking for new backup File"+chr(10)
   cLine += cDefa+"\Billing._db"+chr(10)
   oLine:ReFresh()
   SysReFresh()
   SysWait(2)

   If file( cDefa+"\Billing._db" )
      cLine := "Checking for new backup File"+chr(10)
      cLine += cDefa+"\Billing._db .. Success"+chr(10)
      oLine:ReFresh()
      SysReFresh()
      SysWait(2)

   Else
      cLine := "Checking for new backup File"+chr(10)
      cLine += cDefa+"\Billing._db  .. Failed"+chr(10)
      oLine:ReFresh()
      SysReFresh()
      SysWait(2)
      oDlg:End()
      Return(.f.)
   Endif

   cLine := "Deleting Old Database File"+chr(10)
   cLine += cDefa+"\Billing.Mdb"+chr(10)
   oLine:ReFresh()
   SysReFresh()
   SysWait(2)

   Ferase( cDefa+"\Billing.mdb" )

   If File( cDefa+"\Billing.mdb" )
      cLine := "Deleting Old Database File"+chr(10)
      cLine += cDefa+"\Billing.Mdb ... Failed"+chr(10)
      oLine:ReFresh()
      SysReFresh()
      SysWait(2)
      oDlg:End()
      Return(.f.)
   Else
      cLine := "Deleting Old Database File"+chr(10)
      cLine += cDefa+"\Billing.Mdb ... Success"+chr(10)
      oLine:ReFresh()
      SysReFresh()
      SysWait(2)
   Endif

   cLine := "Renaming New Repaired Database File"+chr(10)
   cLine += cDefa+"\BillingNew.Mdb to"+chr(10)
   cLine += cDefa+"\Billing.Mdb"+chr(10)
   oLine:ReFresh()
   SysReFresh()
   SysWait(2)

   Rename ( cDefa+"\BillingNew.Mdb" ) to ( cDefa+"\Billing.Mdb" )

   If File( cDefa+"\Billing.Mdb" )
      cLine := "Renaming New Repaired Database File"+chr(10)
      cLine += cDefa+"\BillingNew.Mdb to"+chr(10)
      cLine += cDefa+"\Billing.Mdb   ... Success"+chr(10)
      oLine:ReFresh()
      SysWait(2)
      SysReFresh()
   Else
      cLine := "Renaming New Repaired Database File"+chr(10)
      cLine += cDefa+"\BillingNew.Mdb to"+chr(10)
      cLine += cDefa+"\Billing.Mdb   ... Failed"+chr(10)
      cLine += "Please contact your Administrator"+chr(10)
      oLine:ReFresh()
      SysWait(10)
      SysReFresh()
   Endif

   cLine := "Completed Repair and Compacted Database Routine"+chr(10)
   cLine += "Success !!! "+chr(10)
   oLine:ReFresh()
   SysReFresh()
   SysWait(5)

Else

   cLine := "Sorry .. for some Strange reason the Repaired Database"+chr(10)
   cLine += cDefa+"\BillingNew.Mdb"+chr(10)
   cLine += "Could not be Retrieved .. Failure"+chr(10)
   oLine:ReFresh()
   SysReFresh()
   SysWait(10)

Endif

oDlg:End()

Return(.t.)

// end RepairUm.prg
 

RepairUm.Rc

Code: Select all  Expand view

REPAIRUM DIALOG 28, 52, 199, 256
STYLE DS_MODALFRAME | WS_POPUP | WS_CAPTION
FONT 8, "Microsoft Sans Serif"
{
 CONTROL "&Ok", 111, "TBtnBmp", 32 | WS_CHILD | WS_VISIBLE | WS_TABSTOP, 103, 214, 41, 25
 CONTROL "&Cancel", 112, "TBtnBmp", 32 | WS_CHILD | WS_VISIBLE | WS_TABSTOP, 150, 214, 41, 25
 CONTROL "", 115, "STATIC", SS_CENTER | SS_NOPREFIX | WS_GROUP, 8, 31, 184, 51
 LTEXT "", 129, 8, 141, 184, 64, SS_NOPREFIX | WS_GROUP
 CONTROL "", 113, "STATIC", SS_CENTER | SS_NOPREFIX | WS_GROUP, 8, 82, 184, 51
}
 

[Antonio Linares]

Rick,

many thanks

[bpd2000]

Rick,
Thank you for sharing