is it possible to put a compact db procedure in the autexec of a db?



i would like the db to compact every time a user opens it.



Thanks

This is a multi-part message in MIME format.



------=_NextPart_000_0049_01C1A56C.F2C48160

Content-Type: text/plain;

	charset="US-ASCII"

Content-Transfer-Encoding: 7bit



With Access 2K and Access XP, you can program it to compact when the db

closes.  BUT, it will only compact if  no other users have it open.



Open your db and from the top-line menu select Tools > Options.  Select

the General Tab and then select Compact on Close







John Ruff - The Eternal Optimist J



 





-----Original Message-----

From: Mike [mailto:mike.day@o...]

Sent: Friday, January 25, 2002 2:53 PM

To: Access

Subject: [access] Compact Db on open





is it possible to put a compact db procedure in the autexec of a db?



i would like the db to compact every time a user opens it.



Thanks

This is a multi-part message in MIME format.



------=_NextPart_000_00D2_01C1A5F6.8A7FE560

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Yeah Mike, why compact it on open?  It is more practical to compact it 

in close and besides it's the only option Access has provided :)

  ----- Original Message -----

  From: Mike

  To: Access

  Sent: Friday, January 25, 2002 2:52 PM

  Subject: [access] Compact Db on open





  is it possible to put a compact db procedure in the autexec of a db?



  i would like the db to compact every time a user opens it.



  Thanks






$subst('Email.Unsub').










_________________________________________________________



Do You Yahoo!?



Get your free @yahoo.com address at http://mail.yahoo.com

i know i can compact it, but is there a way i can code this procedure automatically??

thanks

oops, forget above reply!!



Thanks John

Mike,



I've created, in VB6, an executable that takes as a parameter the MDB name

and compacts it. I use the scheduler in W2K Pro to periodically compact my

MDB. You are welcome to it and its source, if you wish. Note: The .EXE must

be in the same folder with the MDB.



Walt

Mike 



I would be interested in that to



David



-----Original Message-----

From: Walt Morgan [mailto:wmorgan@s...]

Sent: Saturday, January 26, 2002 4:42 AM

To: Access

Subject: [access] RE: Compact Db on open





Mike,



I've created, in VB6, an executable that takes as a parameter the MDB name

and compacts it. I use the scheduler in W2K Pro to periodically compact my

MDB. You are welcome to it and its source, if you wish. Note: The .EXE must

be in the same folder with the MDB.



Walt

Actually you can do it with a batch file.  The syntax is:



"C:\Program Files\Microsoft Office\Office\msaccess.exe" "D:\Oracle

Reporting\Oracle Data.mdb" /compact

"C:\Program Files\Microsoft Office\Office\msaccess.exe" "D:\Oracle

Reporting\Data\Warehouse Data.mdb" /compact



Ignore the line wrap done by the message system.  I haven't done it but I

would expect that a batch file can be run from the scheduler also.



>-----Original Message-----

>From: David Chapman [mailto:luckychap@b...]

>Sent: Saturday, January 26, 2002 2:08 AM

>To: Access

>Subject: [access] RE: Compact Db on open

>

>

>Mike

>

>I would be interested in that to

>

>David

>

>-----Original Message-----

>From: Walt Morgan [mailto:wmorgan@s...]

>Sent: Saturday, January 26, 2002 4:42 AM

>To: Access

>Subject: [access] RE: Compact Db on open

>

>

>Mike,

>

>I've created, in VB6, an executable that takes as a parameter the MDB name

>and compacts it. I use the scheduler in W2K Pro to periodically compact my

>MDB. You are welcome to it and its source, if you wish. Note: The .EXE must

>be in the same folder with the MDB.

>

>Walt

>

>

>




>

>




>

Leo,



If I understand correctly your solution depends on msaccess.exe; if this

file is not available, e.g., a VB application using .MDBs without Access,

then one would need an alternative way to compact the MDB. That's why I

created the VB routine.



Thanks,



Walt

This is a multi-part message in MIME format.



------=_NextPart_000_004C_01C1A653.390686F0

Content-Type: text/plain;

	charset="US-ASCII"

Content-Transfer-Encoding: 7bit



You can cut and paste this code to either vb or Access.  Then run it

from the task scheduler to perform scheduled compacting of your db.  You

can modify it so that you can schedule any number of databases to

compact at any time.



Public Sub CompactDB_JR()

'---------------------------------------------------------

' Created by: John Ruff

' Date: 2/17/2000

' Purpose: Compact a database

' Special Instructions: Reference the Microsoft Jet and Replication

Objects X.X

'                       Library. (The X.X must be greater or equal to

2.1)

'---------------------------------------------------------

    Dim objJRO As JRO.JetEngine

    Dim strSourcePath As String

    Dim strTempPath As String

On Error Resume Next



   ' Create an instance of the jro jet engine

    Set objJRO = New JRO.JetEngine

   

    ' set the source and temp paths and database names

    ' replace these with yours

    strSourcePath = "C:\Temp\NorthWind.mdb"

    strTempPath = "c:\temp\Temp.mdb"

   

    ' compact the source database to a new name

    objJRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=" & _

                strSourcePath, "Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=" & _

                strTempPath & ";Jet OLEDB:Engine Type=4"



    ' Delete the original database

    Kill strSourcePath

   

    ' Copy the temp databse to the new name

    FileCopy strTempPath, strSourcePath

   

    ' Delete the temp database

    Kill strTempPath

 

    ' Release the variable

    Set objJRO = Nothing

 

End Sub



I hope this helps







John Ruff - The Eternal Optimist J



 



 





-----Original Message-----

From: Walt Morgan [mailto:wmorgan@s...]

Sent: Saturday, January 26, 2002 10:04 AM

To: Access

Subject: [access] RE: Compact Db on open





Leo,



If I understand correctly your solution depends on msaccess.exe; if this

file is not available, e.g., a VB application using .MDBs without

Access, then one would need an alternative way to compact the MDB.

That's why I created the VB routine.



Thanks,



Walt