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
![Sad :(](./images/smilies/icon_sad.gif)
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
}