Referring to Back End Database - Dynamic possible?

[Marc T]

Hi All,

I have a split database which has the following VBA macro to compact/repair
the back end database:

Private Sub Form_Unload(Cancel As Integer)

Dim strBackupBackend As String
Dim strCurrBackend As String
Dim strCurrLockFile As String

strCurrBackend = "H:\Data\Doc Tracker Master 00000_be.mdb"
strCurrLockFile = "H:\Data\Doc Tracker Master 00000_be.ldb"
strBackupBackend = "H:\Data\Doc Tracker Master 00000_be.bak"

If Len(Dir(strCurrLockFile)) > 0 Then
MsgBox strCurrBackend & " is still in use. " & _
"It cannot be compacted."
Else
If Len(Dir(strBackupBackend)) > 0 Then
Kill strBackupBackend
End If
Name strCurrBackend As strBackupBackend
DBEngine.CompactDatabase strBackupBackend, strCurrBackend
End If

End Sub



This works a treat, but as there are numerous copies of the database, I was
wondering if there was a way to set strCurrBackEnd, strCurrLockFile &
strBackupBackend dynamically to save time?
To make things easier, each database follows the following naming convention
(with the 01234 part changing):

Frontend - "Doc Tracker Master 01234.mdb"
Backend - "Data\Doc Tracker Master 01234_be.mdb"
Lock File - "Data\Doc Tracker Master 01234_be.ldb"


Any help would be much appreciated as ever!
Marc

 

[Douglas J. Steele]

Your requirement makes me wonder whether your database is properly
normalized, but in the event that it is,

Dim strBackupBackend As String
Dim strCurrBackend As String
Dim strCurrLockFile As String
Dim strVersion As String

strVersion = "01234"
strCurrBackend = "H:\Data\Doc Tracker Master " & _
strVersion & "00000_be.mdb"
strCurrLockFile = "H:\Data\Doc Tracker Master " & _
strVersion & "_be.ldb"
strBackupBackend = "H:\Data\Doc Tracker Master " & _
strVersion & "_be.bak"

 

[Marc T]

Hi Doug,

thanks for the reply. I have managed to resolve with the following:

strCurrBackend = CurrentProject.Path & "\Data\" & Left(CurrentProject.Name,
Len(CurrentProject.Name) - 4) & "_be.mdb"
strCurrLockFile = CurrentProject.Path & "\Data\" &
Left(CurrentProject.Name, Len(CurrentProject.Name) - 4) & "_be.ldb"
strBackupBackend = CurrentProject.Path & "\Data\" &
Left(CurrentProject.Name, Len(CurrentProject.Name) - 4) & "_be.bak"

Just curious, what is it that makes you suspect the database isn't properly
normalized?
The backend is constantly growing in size due to the regular (several times
daily) importing and deleting of data from an external Oracle database. Each
occurence of the database stores thousands of document records for an
individual project.

Marc

 

[Douglas J. Steele]

Having multiple databases that are essentially the same is usually
indicative of improper normalization.

How big is the backend? Unless you're talking move than 1.5 Gb, why make
multiple versions?