Method to compact backend via VBA

  • Thread starter Thread starter Alp
  • Start date Start date
A

Alp

Hi Experts,

Is there a way to compact backend databases via VBA within the front-end or
do I have to shell out?

Thanks in advance.

Alp
 
You can use the CompactDatabase method of the DBEngine object (This requires
you to have a reference set to DAO)

I typically rename the existing backend (eg. by changing the .mdb to .bak),
and compact the renamed database to the "correct" database name:
 
Hi Doug,

Thank you for your help. What I tried initially was the following (which
didn't work):
Public Function CompactBE()
'be'nin yeri-adi
Const strQueryName = "q_tip_tablo"
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(strQueryName)
Do While Not rs.EOF
'BE
isim = rs![beDeki]
'new name
yeniisim = isim & "z"
'compact
Application.DBEngine.CompactDatabase isim, yeniisim
'start cleanup
Set objScript = CreateObject("Scripting.FileSystemObject")
'copy
objScript.CopyFile yeniisim, isim, True
'delete new name
objScript.DeleteFile yeniisim
'next one
rs.MoveNext
Loop
rs.Close
db.Close
End Function

Would you mind telling me which/where to alter? I'm not much familiar with
the DBEngine stuff, meaning haven't used it much yet!

Thank you for your patience.

Alp
 
I'm sorry, I can't figure out what that function is supposed to be doing!

What is the query q_tip_tabio supposed to return? Why are you trying to
compact multiple times?

Remember that everything's contained in a single file. You only have to
compact the database, not each individual table in it.

Typically, I'll do something like the following (note, though, that I'm
typing from memory, as opposed to cutting-and-pasting, so it's possible
there are errors in this code!):

Sub CompactBackendDatabase()
Dim strBackendDatabase As String
Dim strBackupDatabase As String

' BackendDatabase is a custom function that returns the
' full path to the backend database. If you know where the
' backend is located, you can simply put the string here.
strBackendDatabase = BackendDatabase()

' Strip off the ".mdb" from the end of strBackendDatabase
' and replace it with ".bak"
strBackupDatabase = Left$(strBackendDatabase, _
Len(strBackendDatabase) - 4) & ".bak

' Does strBackendDatabase already exist?
' If so, delete it.
If Len(Dir(strBackupDatabase)) > 0 Then
Kill strBackupDatabase
End If

' Rename the Backend database
Name strBackendDatabase As strBackupDatabase

' Compact the database
DBEngine.CompactDatabase strBackupDatabase, _
strBackendDatabase

End Sub

Hopefully I've typed that so that there won't be any wordwrap!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Alp said:
Hi Doug,

Thank you for your help. What I tried initially was the following (which
didn't work):
Public Function CompactBE()
'be'nin yeri-adi
Const strQueryName = "q_tip_tablo"
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(strQueryName)
Do While Not rs.EOF
'BE
isim = rs![beDeki]
'new name
yeniisim = isim & "z"
'compact
Application.DBEngine.CompactDatabase isim, yeniisim
'start cleanup
Set objScript = CreateObject("Scripting.FileSystemObject")
'copy
objScript.CopyFile yeniisim, isim, True
'delete new name
objScript.DeleteFile yeniisim
'next one
rs.MoveNext
Loop
rs.Close
db.Close
End Function

Would you mind telling me which/where to alter? I'm not much familiar with
the DBEngine stuff, meaning haven't used it much yet!

Thank you for your patience.

Alp

Douglas J. Steele said:
You can use the CompactDatabase method of the DBEngine object (This requires
you to have a reference set to DAO)

I typically rename the existing backend (eg. by changing the .mdb to .bak),
and compact the renamed database to the "correct" database name:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



front-end
or
Click to expand...
Click to expand...
 
Hi Doug,

Thanks for your help. OK, here're some answers;
-I have links to 3 seperate BE mdb's and that's why I have to do it multiple
times (once for each)
-The q_tip_tablo returns the fully qualified names for these mdb files from
MSys (based on type 6 and grouped)

I added DOA. to both the Database and the Recordset and just removed the
"Application." from the statement and it now works! :-)

Thanks again for pointing me in the right direction and making me think a
bit further.

Alp


Douglas J. Steele said:
I'm sorry, I can't figure out what that function is supposed to be doing!

What is the query q_tip_tabio supposed to return? Why are you trying to
compact multiple times?

Remember that everything's contained in a single file. You only have to
compact the database, not each individual table in it.

Typically, I'll do something like the following (note, though, that I'm
typing from memory, as opposed to cutting-and-pasting, so it's possible
there are errors in this code!):

Sub CompactBackendDatabase()
Dim strBackendDatabase As String
Dim strBackupDatabase As String

' BackendDatabase is a custom function that returns the
' full path to the backend database. If you know where the
' backend is located, you can simply put the string here.
strBackendDatabase = BackendDatabase()

' Strip off the ".mdb" from the end of strBackendDatabase
' and replace it with ".bak"
strBackupDatabase = Left$(strBackendDatabase, _
Len(strBackendDatabase) - 4) & ".bak

' Does strBackendDatabase already exist?
' If so, delete it.
If Len(Dir(strBackupDatabase)) > 0 Then
Kill strBackupDatabase
End If

' Rename the Backend database
Name strBackendDatabase As strBackupDatabase

' Compact the database
DBEngine.CompactDatabase strBackupDatabase, _
strBackendDatabase

End Sub

Hopefully I've typed that so that there won't be any wordwrap!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Alp said:
Hi Doug,

Thank you for your help. What I tried initially was the following (which
didn't work):
Public Function CompactBE()
'be'nin yeri-adi
Const strQueryName = "q_tip_tablo"
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(strQueryName)
Do While Not rs.EOF
'BE
isim = rs![beDeki]
'new name
yeniisim = isim & "z"
'compact
Application.DBEngine.CompactDatabase isim, yeniisim
'start cleanup
Set objScript = CreateObject("Scripting.FileSystemObject")
'copy
objScript.CopyFile yeniisim, isim, True
'delete new name
objScript.DeleteFile yeniisim
'next one
rs.MoveNext
Loop
rs.Close
db.Close
End Function

Would you mind telling me which/where to alter? I'm not much familiar with
the DBEngine stuff, meaning haven't used it much yet!

Thank you for your patience.

Alp

Douglas J. Steele said:
You can use the CompactDatabase method of the DBEngine object (This requires
you to have a reference set to DAO)

I typically rename the existing backend (eg. by changing the .mdb to .bak),
and compact the renamed database to the "correct" database name:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi Experts,

Is there a way to compact backend databases via VBA within the front-end
or
do I have to shell out?

Thanks in advance.

Alp
Click to expand...
Click to expand...
Click to expand...
 
Back
Top