VB6-Access2003 Compact Repair Archive/Backup

Greetings,

I've created a VB front end to Access 2003 database application. Everything
works great, but I want to add database maintenace routines to the
application. Now to do the maintenace routines directly from Access seems
simple enough, built in Access menu choices. But my question is, what is
the best school of thought for database maintenance, directly from Access
or write maintenance procedures into my VB front end?

If VB is the best choice, I'm not sure how to go about it, what are my
options?

Thanks for your help.

fall

fallguy.RemoveThis@easystreet.com wrote:
 > Greetings,
 >
 > I've created a VB front end to Access 2003 database application.
Everything
 > works great, but I want to add database maintenace routines to the
 > application. Now to do the maintenace routines directly from Access
seems
 > simple enough, built in Access menu choices. But my question is,
what is
 > the best school of thought for database maintenance, directly from
Access
 > or write maintenance procedures into my VB front end?
 >
 > If VB is the best choice, I'm not sure how to go about it, what are
my
 > options?
 >
 > Thanks for your help.
 >
 > fall


Personally, I would use VB from your app just for sake of ease.
Compacting from VB is very easy.

1. Make a reference to Microsoft DAO 3.6 Object Library. We will be
using the DBEngine.
3. Dimension 2 strings to hold the FQN of the database you want to
compact and a temporay FQN name to call it during compaction. In the
code sample below, I am going to compact a database in my C:\Program
Files\ABS directory called ABSInfo.mdb.

Dim strDB1 as String, strDB2 as String
strDB1 = "C:\Program Files\ABS\ABSInfo.mdb"
strDB2 = "C:\Program Files\ABS\ABSInfo_Temp.mdb"

3. Now, if you use user level security on your database, you will need
to pass in the path to your workgroup file (system.mdw) as shown below.
If you do not use user level security, you can skip the code block
below.

With DBEngine
.SystemDB = "C:\Program Files\ABS\system.mdw"
.DefaultUser = "ABSAdmin"
.DefaultPassword = "ABSADMIN_Password"
End With

4. Next, the following line of code will compact the database in strDB1
into a new database with the name in strDB2.

DBEngine.CompactDatabase strDB1, strDB2

5. Finally, remove the database listed in strDB1 and rename the
database in strDB2 back to the name assigned to strDB1.

Kill strDB1
Name strDB2 As strDB1

6. Make sure to use proper error handling during this procedure to make
sure that the database listed in strDB1 is not deleted unless the
compaction is successful. This can be done with either an On Error Goto
statement at the begining of the function or On Error Resume Next at
the begining with an "If Err.Number = 0 then" statement before "Kill
strDB1" and the End If after the Name statement.

I also always recommend that a database be compacted before doing a
nightly backup (a database should always be backed up anytime a change
is made). Hope this helps.

David Hodgkins
MCSD, MCDBA, MCSE