[AccessD] Read External File for Path String

I have used both a global constant and a property appended to the database 
properties collection to store this kind of information.  As an example of a 
property defined by a user, I create a property called 'CompactDate' and use 
it to create a back up and automatically back up a BE every fourth day 
(people are forced out after midnight after two hours of inactivity).  If 
the property doesn't exist, the error handler creates the property by 
calling the AddProperty sub routine.  The Compact routine compacts the 
linked database containing a specific named table and creates a backup 
prefixed with the backup date and the BE name.  You could easily create a 
'ServerPath' property.

Private Sub Compact()
'  Opens BE db exclusive (to preempt other users) and inspects a custom db 
property "CompactDate"
'  Compares "CompactDate" property to current date and if more than 4 day 
disparity, needs to compact
'  Closes BE so that compact may take place.
'  Backs Up database before compacting
'  Compacts BE to new file.
'  If successful, deletes old file and renames the compacted BE to the BE in 
the connect string
    On Error GoTo ErrorHandler

    Dim NewDBName As String
    Dim strDBName As String
    Dim db As DAO.Database
    Dim strProp As String
    Dim strDate As String
    Dim strPropertyName As String

    Set db = CurrentDb
    strDBName = Mid$(db.TableDefs("tblCompany").Connect, 11)
    If Dir(Left$(strDBName, Len(strDBName) - 3) & "ldb") = "" Then
        'then the ldb file isn't present so the mdb is not open and can be 
compacted
        Set db = OpenDatabase(strDBName, True) ' true places it in 
'exclusive mode
        strPropertyName = "CompactDate"
        strProp = db.Properties(strPropertyName)
        'get the compactDate property
        strDate = Format$(Date, "yymmdd")
        If Format$(Date - 4, "yymmdd") > strProp Then
            'if it's more than 4 days since last compact
            db.Close
            'it was open exclusive.  Must close to work with it
            Set db = Nothing
           If Copy(strDBName, Left$(strDBName, Len(strDBName) - 4) & "BackUp 
" & strDate & ".mdb", _
             False) Then
                'backup successful
                NewDBName = Left$(strDBName, Len(strDBName) - 4) & strDate & 
".mdb"
                If Dir(NewDBName) <> "" Then Kill NewDBName
                DBEngine.CompactDatabase strDBName, NewDBName
                Kill strDBName
                Name NewDBName As strDBName
                Set db = OpenDatabase(strDBName)
                db.Properties(strPropertyName) = strDate
            End If
        End If
    End If

ExitRoutine:
    On Error Resume Next
    db.Close
    Set db = Nothing
    Exit Sub
ErrorHandler:
    With Err
        Select Case .Number
            Case 76, 68
                Resume ExitRoutine
            Case 3270
                AddProperty strPropertyName, db
                Resume
            Case Else
                MsgBox .Number & vbCrLf & .Description, vbInformation, 
"Error - Compact"
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
End Sub

Private Sub AddProperty(strPropertyName As String, db As DAO.Database)
    Dim prp As Property

    Set prp = db.CreateProperty(strPropertyName, dbText, False)
    db.Properties.Append prp
End Sub



Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: "Dan Waters" <dwaters at usinternet.com>
>
>Hello Everyone,
>
>I just spent half the day struggling with the movement of a system from one
>server to another.  The server path is hardcoded in one place in a startup
>form.  What would have helped me a lot is to have the path in a text file 
>or
>ini file external to the database and stored in the same folder.
>
>Security is not an issue here, just my convenience, as my plans for the day
>were pretty much blown up!
>
>Using a file for information like this sounds like it might be a fairly
>typical solution.  Does anyone do this or something like it?
>
>Thanks!
>Dan Waters