By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Compress Access DB

P: n/a
I would like to compress my Access database from within my VB6 Aplication I
am writing.

Anyone know how?

Comet
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
If you mean compacting then the following code should do the trick:

Dim oAccess As Object
Set oAccess = CreateObject("Access.Application")
oAccess.CompactRepair "<source db>", "<dest db>", True
oAccess.Quit
Set oAccess = Nothing
--
Björn Holmgren
Guide Konsult AB

"Comet Developer" <de*******@comet-online.co.za> wrote in message
news:bl**********@ctb-nnrp2.saix.net...
I would like to compress my Access database from within my VB6 Aplication I am writing.

Anyone know how?

Comet

Nov 12 '05 #2

P: n/a
Yes sorry compact! One question...can <source db> and <dest db> be the same
file?
Thanks for the help!
Nov 12 '05 #3

P: n/a
Try DBEngine.CompactDatabase (DAO). The database must be closed.

--
PZ
Comet Developer wrote:
I would like to compress my Access database from within my VB6
Aplication I am writing.

Anyone know how?

Comet

Nov 12 '05 #4

P: n/a
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" <de*******@comet-online.co.za> wrote in message
news:bl**********@ctb-nnrp2.saix.net...
Yes sorry compact! One question...can <source db> and <dest db> be the same file?
Thanks for the help!

Nov 12 '05 #5

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

Dim jro As jro.JetEngine

Set jro = New jro.JetEngine

jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\nwind2.mdb;Jet OLEDB:Database Password=test", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\abbc2.mdb;Jet OLEDB:Engine
Type=4;Jet OLEDB:Database Password=test"

Nov 12 '05 #6

P: n/a
On Fri, 3 Oct 2003 08:27:11 +0200, "Comet Developer" <de*******@comet-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...b;en-us;230501
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 12 '05 #7

P: n/a
"Comet Developer" <de*******@comet-online.co.za> wrote in message news:<bl**********@ctb-nnrp2.saix.net>...
I would like to compress my Access database from within my VB6 Aplication I
am writing.

Anyone know how?


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 SQLConfigDataSource Lib "odbccp32.dll" (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(sDBPath, 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 = SQLConfigDataSource(hSomeHandle, ODBC_ADD_DSN, sDriver,
sAttribute)
' ----------------------------------------------------------

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

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

If CompactAccess(sDBLocalPath, sDBLocalPath, Me.hwnd) = False Then
' Compact didn't work -- what reasons would cause this?
End If
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.