Compress Access DB

[Comet Developer]

I would like to compress my Access database from within my VB6 Aplication I
am writing.

Anyone know how?

Comet

[Björn Holmgren]

Re: Compress Access DB

If you mean compacting then the following code should do the trick:

Dim oAccess As Object
Set oAccess = CreateObject("A ccess.Applicati on")
oAccess.Compact Repair "<source db>", "<dest db>", True
oAccess.Quit
Set oAccess = Nothing


--
Björn Holmgren
Guide Konsult AB

"Comet Developer" <developer@come t-online.co.za> wrote in message
news:blj4qa$pk3 $1@ctb-nnrp2.saix.net. ..[color=blue]
> I would like to compress my Access database from within my VB6 Aplication[/color]
I[color=blue]
> am writing.
>
> Anyone know how?
>
> Comet
>
>[/color]

[Comet Developer]

Re: Compress Access DB

Yes sorry compact! One question...can <source db> and <dest db> be the same
file?
Thanks for the help!

[Pásztor, Zoltán]

Re: Compress Access DB

Try DBEngine.Compac tDatabase (DAO). The database must be closed.

--
PZ


Comet Developer wrote:[color=blue]
> I would like to compress my Access database from within my VB6
> Aplication I am writing.
>
> Anyone know how?
>
> Comet[/color]

[Björn Holmgren]

Re: Compress Access DB

No, you have to rename the files after compacting (or remove the source db
and rename the dest db).

--
Björn Holmgren
Guide Konsult AB

"Comet Developer" <developer@come t-online.co.za> wrote in message
news:bljd05$2p3 $1@ctb-nnrp2.saix.net. ..[color=blue]
> Yes sorry compact! One question...can <source db> and <dest db> be the[/color]
same[color=blue]
> file?
> Thanks for the help!
>
>[/color]

[Comet Developer]

Re: Compress Access DB

Found a piece of code to do it on the MSDN site:

Dim jro As jro.JetEngine

Set jro = New jro.JetEngine

jro.CompactData base "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=d:\nwind 2.mdb;Jet OLEDB:Database Password=test", _
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=d:\abbc2 .mdb;Jet OLEDB:Engine
Type=4;Jet OLEDB:Database Password=test"

[Paul Clement]

Re: Compress Access DB

On Fri, 3 Oct 2003 08:27:11 +0200, "Comet Developer" <developer@come t-online.co.za> wrote:

¤ I would like to compress my Access database from within my VB6 Aplication I
¤ am writing.
¤
¤ Anyone know how?

Access is not required for the following:

HOWTO: Compact Microsoft Access Database Through ADO

http://support.microsoft.com/default.aspx?scid=kb;en-us;230501

Paul ~~~ pclement@amerit ech.net
Microsoft MVP (Visual Basic)

[Matt]

Re: Compress Access DB

"Comet Developer" <developer@come t-online.co.za> wrote in message news:<blj4qa$pk 3$1@ctb-nnrp2.saix.net> ...[color=blue]
> I would like to compress my Access database from within my VB6 Aplication I
> am writing.
>
> Anyone know how?[/color]

Bjorn's method requires the user to have Access on their machine, and
Zoltan's method requires you to be using DAO. My method requires none
of those. However, you may want to do some checking to find out what
version of odvccp32.dll the user needs, if everyone has it, what OS's
it's suppported on, etc. In my case, all machines will be Win2k, and
it works fine. I haven't done testing on WinXP.

By the way, I have NO idea where this came from, and I'm sorry to the
original person that I got this from. If anyone knows, please post
the author and I'll be sure to give them credit.


Private Declare Function SQLConfigDataSo urce Lib "odbccp32.d ll" (ByVal
hwnd As Long, ByVal fRequest As Integer, ByVal cDriver As String,
ByVal cAttributes As String) As Boolean
Private Const ODBC_ADD_DSN = 1


Public Function CompactAccess(s DBPath, sNewPath, hSomeHandle As Long)
As Boolean
Dim bResult As Boolean
Dim sDriver As String
Dim sAttribute As String

' ----------------------------------------------------------
sDriver = "Microsoft Access Driver (*.mdb)"
' ----------------------------------------------------------

' ----------------------------------------------------------
' compact database
' ----------------------------------------------------------
sAttribute = "COMPACT_DB =""" & sDBPath & """ """ & sNewPath & """
General"
bResult = SQLConfigDataSo urce(hSomeHandl e, ODBC_ADD_DSN, sDriver,
sAttribute)
' ----------------------------------------------------------

' ----------------------------------------------------------
CompactAccess = bResult
' ----------------------------------------------------------
End Function



And call it like this, from a form in my example:

If CompactAccess(s DBLocalPath, sDBLocalPath, Me.hwnd) = False Then
' Compact didn't work -- what reasons would cause this?
End If