John Colby
jcolby at colbyconsulting.com
Sun Nov 2 07:57:21 CST 2003
Gustav,
I was under the impression that Access stored query results etc internal to
the FE. What happens if you write protect the FE? You say temporary data
will be written to temporary files? What does this mean?
John W. Colby
www.colbyconsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Sunday, November 02, 2003 5:12 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] ACC97 - Repair and Compact
Hi Tony
> I have a program designed for a client that runs basically 24 hours a
> day, 7 days a week collecting and processing data. Is it possible to
> schedule a once weekly repair and compact operation on both the frontend
> and backend (or even for now just the backend). There is a lag/idle time
> between 1AM and 4AM, when no data is collected or processed. Any
> direction to available code, ideas or programs would be appreciated.
> Thank you.
1. If you write protect the front end database file (before launching
it) there's no need to compact it as, in that case, Access's temporary
data always will be written to external files.
2. If you don't have any open connections from the front end to the
back end, you can safely open the back end in a separate workspace and
compact it. To make sure that no other app has open connections to the
back end, let your compacting app open the back end exclusively; if
this fails don't continue.
3. Don't apply Repair for routine maintenance. This is a general
advice not invented by me, but I've never seen a need for it except
for corrupted database files and then JetComp.exe is a better first
attempt.
Re. 1. Write protecting an app will cause a message about this to pop
up at launch. This can be avoided by running the app from a runtime
installation.
Re. 2. The difficult part is not to compact the database file - it's a
couple of code lines only. The critical part is to create and keep
track of backup files in case something fails.
Here's a function which handles this. It is a part of a solution which
has run for several years and also handles archiving to a cab or zip
file and ftp'ing this to a remote server. The backup routine is
triggered by a timer function on the main form.
<code>
Public Function DatabaseCompact( _
ByVal strDataFile As String) As Boolean
Const cbooDbOpenExclusive As Boolean = True
Const cbooDbOpenReadOnly As Boolean = True
Const cbytDbFileExtLength As Byte = 3
Const cstrDbFileExtBackup As String * cbytDbFileExtLength = "mdk"
Const cstrDbFileExtTemp As String * cbytDbFileExtLength = "tmp"
Dim wks As Workspace
Dim dbs As Database
Dim strBackFile As String
Dim strTempFile As String
Dim strDataFilePath As String
Dim strDataFileName As String
Dim intDataFileName As Integer
Dim booClosed As Boolean
Dim booSuccess As Boolean
On Error GoTo Err_DatabaseCompact
strDataFileName = Dir(strDataFile, vbNormal)
intDataFileName = Len(strDataFileName)
If intDataFileName = 0 Then
' No data file to process.
Else
' Data file exists.
strDataFilePath = Left(strDataFile, Len(strDataFile) - intDataFileName)
strBackFile = Left(strDataFile, Len(strDataFile) - cbytDbFileExtLength)
& cstrDbFileExtBackup
strTempFile = Left(strDataFile, Len(strDataFile) - cbytDbFileExtLength)
& cstrDbFileExtTemp
Set wks = DBEngine(0)
Set dbs = wks.OpenDatabase(strDataFile, cbooDbOpenExclusive,
cbooDbOpenReadOnly)
' No error. Database file can be opened exclusively, thus no open
connections.
' Keep it open to prevent access from other users while compacting it.
If Len(Dir(strBackFile, vbNormal)) > 0 Then
' Delete old backup file.
Kill strBackFile
End If
If Len(Dir(strBackFile, vbNormal)) > 0 Then
' Old backup file could not be deleted.
Else
' Create copy for further processing.
FileCopy strDataFile, strBackFile
If Len(Dir(strBackFile, vbNormal)) > 0 Then
If Len(Dir(strTempFile, vbNormal)) > 0 Then
' Kill left-over temp file.
Kill strTempFile
End If
If Len(Dir(strTempFile, vbNormal)) > 0 Then
' Old temp file could not be deleted.
Else
' Compact backup file to a temporary file.
DBEngine.CompactDatabase strBackFile, strTempFile, dbLangNorwDan
If Len(Dir(strTempFile, vbNormal)) = 0 Then
' Compact failed somehow.
Else
' Temporary file was created.
' Close database and delete current data file leaving backup
file.
dbs.Close
booClosed = True
Kill strDataFile
If Len(Dir(strDataFile, vbNormal)) > 0 Then
' Current data file was not deleted.
' Clean up.
Kill strTempFile
Else
' Current data file is gone.
' Rename temporary data file as new data file.
Name strTempFile As strDataFile
If Len(Dir(strDataFile, vbNormal)) > 0 Then
' Temporary file was renamed successfully.
' Check that the data file can be opened and recognized.
Set dbs = wks.OpenDatabase(strDataFile, cbooDbOpenExclusive,
cbooDbOpenReadOnly)
booClosed = False
dbs.Close
booClosed = True
booSuccess = True
End If
End If
End If
End If
End If
End If
If booClosed = False Then
dbs.Close
End If
If booSuccess = False Then
' For some reason something failed.
If Len(Dir(strDataFile, vbNormal)) = 0 Then
' Data file is lost.
' Copy back the backup file if it exists.
If Len(Dir(strBackFile, vbNormal)) > 0 Then
FileCopy strBackFile, strDataFile
End If
End If
End If
End If
Set dbs = Nothing
Set wks = Nothing
DatabaseCompact = booSuccess
Exit_DatabaseCompact:
Exit Function
Err_DatabaseCompact:
MsgBox CStr(Err.Number) & ": " & Err.Description, vbCritical + vbOKOnly,
"Database Compact"
Resume Exit_DatabaseCompact
End Function
</code>
As always, beware of line breaks.
/gustav
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com