Compacting from code doesn't work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the following code to compact Database A while in Database B.

Dim strTmp As String
Dim strPath As String
Dim strFile As String
Dim strFull As String


' Lookup the path and database to set up as the template
' Path
strPath = DLookup("[ExportDirectory]", "tblMaster", "[MasterID] = 1")
' Database
strFile = DLookup("[ExportTemplateDatabase]", "tblMaster", "[MasterID] = 1")
' Full path and name
strFull = strPath & strFile

strTmp = Trim(strFull)
strTmp = Left(strTmp, Len(strTmp) - 4)
strTmp = strTmp & "BAK.mdb"

' Compact the database into a temporary database.
DBEngine.CompactDatabase strFull, strTmp
Kill strFull
Name strTmp As strFull

This code does not work. Can anyone tell me why it doesn't work or what I am
doing wrong?
 
SHIPP said:
I am using the following code to compact Database A while in Database
B.

Dim strTmp As String
Dim strPath As String
Dim strFile As String
Dim strFull As String


' Lookup the path and database to set up as the template
' Path
strPath = DLookup("[ExportDirectory]", "tblMaster", "[MasterID] =
1") ' Database
strFile = DLookup("[ExportTemplateDatabase]", "tblMaster",
"[MasterID] = 1") ' Full path and name
strFull = strPath & strFile

strTmp = Trim(strFull)
strTmp = Left(strTmp, Len(strTmp) - 4)
strTmp = strTmp & "BAK.mdb"

' Compact the database into a temporary database.
DBEngine.CompactDatabase strFull, strTmp
Kill strFull
Name strTmp As strFull

This code does not work. Can anyone tell me why it doesn't work or
what I am doing wrong?
Click to expand...

Not unless you tell us what "doesn't work" means. In what way does it
not work? If an error is raised, what is it?

If you inspect the value of strFull and strTmp before you call
DBEngine.CompactDatabase, what do you see?
 
Sorry for not being more clear. There aren't any errors raised. It just
doesn't compact the database. If I use the VB code the database remains at
it's original size. However, if I compact it the conventional way through the
menu option within the database itself it compacts to 1/10th of its' original
size. I have stepped through the code and strFull is the original path and
name of the database I am trying to compact. strTmp is the name and path of
the temporary file with BAK added on to the name of the file.

Dirk Goldgar said:
SHIPP said:
I am using the following code to compact Database A while in Database
B.

Dim strTmp As String
Dim strPath As String
Dim strFile As String
Dim strFull As String


' Lookup the path and database to set up as the template
' Path
strPath = DLookup("[ExportDirectory]", "tblMaster", "[MasterID] =
1") ' Database
strFile = DLookup("[ExportTemplateDatabase]", "tblMaster",
"[MasterID] = 1") ' Full path and name
strFull = strPath & strFile

strTmp = Trim(strFull)
strTmp = Left(strTmp, Len(strTmp) - 4)
strTmp = strTmp & "BAK.mdb"

' Compact the database into a temporary database.
DBEngine.CompactDatabase strFull, strTmp
Kill strFull
Name strTmp As strFull

This code does not work. Can anyone tell me why it doesn't work or
what I am doing wrong?
Click to expand...

Not unless you tell us what "doesn't work" means. In what way does it
not work? If an error is raised, what is it?

If you inspect the value of strFull and strTmp before you call
DBEngine.CompactDatabase, what do you see?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
As you step through the code, does it actually create the strTmp
database? That is, if you put a breakpoint on the line
Kill strFull
Click to expand...

which immediately follows the call to CompactDatabase, (a) does
execution reach that line, and (b) if you actually look for the temp
file using Windows Explorer, do you see it there in the folder where it
should be?

There are a number of things that could cause your attempt to compact
the database to fail, such as any user -- your own front-end included --
having an open connection to the file at the time, but I would expect
you to get an error if that were the case. I have to trust that you do
have error-handling enabled here, and haven't executed the statement "On
Error GoTo Next".

There is also a difference between the Access compact method and the
Jet/DAO compact method in A2K and later, such that the Access method (as
invoked via the user interface) will compact the Access objects such as
forms, reports, modules, etc., while the Jet method won't. So it's
*conceivable* that the database is actually being compacted by your code
and the difference you see is because of that. However, I find it very
hard to believe, especially since, if this is a back-end you're
compacting, there wouldn't likely be any Access objects or code in the
database.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

SHIPP said:
Sorry for not being more clear. There aren't any errors raised. It
just doesn't compact the database. If I use the VB code the database
remains at it's original size. However, if I compact it the
conventional way through the menu option within the database itself
it compacts to 1/10th of its' original size. I have stepped through
the code and strFull is the original path and name of the database I
am trying to compact. strTmp is the name and path of the temporary
file with BAK added on to the name of the file.

Dirk Goldgar said:
SHIPP said:
I am using the following code to compact Database A while in
Database B.

Dim strTmp As String
Dim strPath As String
Dim strFile As String
Dim strFull As String


' Lookup the path and database to set up as the template
' Path
strPath = DLookup("[ExportDirectory]", "tblMaster", "[MasterID] =
1") ' Database
strFile = DLookup("[ExportTemplateDatabase]", "tblMaster",
"[MasterID] = 1") ' Full path and name
strFull = strPath & strFile

strTmp = Trim(strFull)
strTmp = Left(strTmp, Len(strTmp) - 4)
strTmp = strTmp & "BAK.mdb"

' Compact the database into a temporary database.
DBEngine.CompactDatabase strFull, strTmp
Kill strFull
Name strTmp As strFull

This code does not work. Can anyone tell me why it doesn't work or
what I am doing wrong?
Click to expand...

Not unless you tell us what "doesn't work" means. In what way does
it not work? If an error is raised, what is it?

If you inspect the value of strFull and strTmp before you call
DBEngine.CompactDatabase, what do you see?
Click to expand...
Click to expand...
 
Back
Top