Jürgen Welz
jwelz at hotmail.com
Wed Jan 18 22:01:55 CST 2006
I forgot the 'Copy' routine referenced in the example. It uses the API copy
routine because I like the option of specifying whether or not to overwrite
an existing file. NoOverWrite is passed as false, a double negative, so it
guarantees that any file with the exact name will be overwritten. Compact
is one of the procedures called in my startup.
Private Declare Function CopyFileA Lib "kernel32" (ByVal ExistingFileName As
String, _
ByVal NewFileName As String, ByVal FailIfExists As Long) As Long
Public Function Copy(FileSrc As String, FileDst As String, Optional
NoOverWrite As Boolean = True) _
As Boolean
On Error GoTo ErrorHandler
Copy = CopyFileA(FileSrc, FileDst, NoOverWrite) = 1
ExitRoutine:
On Error Resume Next
Exit Function
ErrorHandler:
With Err
Select Case .Number
Case Else
MsgBox .Number & vbCrLf & .Description, vbInformation,
"Error - Copy"
End Select
End With
'Resume 0
Resume ExitRoutine
End Function
Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com
>From: "Jürgen Welz" <jwelz at hotmail.com>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Read External File for Path String
>Date: Wed, 18 Jan 2006 20:42:38 -0700
>MIME-Version: 1.0
>X-Originating-IP: [198.53.207.165]
>X-Originating-Email: [jwelz at hotmail.com]
>X-Sender: jwelz at hotmail.com
>Received: from databaseadvisors.com ([209.135.140.44]) by
>bay0-mc1-f5.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.211); Wed, 18
>Jan 2006 19:43:10 -0800
>Received: from databaseadvisors.com (databaseadvisors.com
>[209.135.140.44])by databaseadvisors.com (8.11.6/8.11.6) with ESMTP id
>k0J3gaV22247;Wed, 18 Jan 2006 21:42:36 -0600
>Received: from hotmail.com (bay113-f9.bay113.hotmail.com [65.54.168.19])by
>databaseadvisors.com (8.11.6/8.11.6) with ESMTP id k0J3gWV22171for
><accessd at databaseadvisors.com>; Wed, 18 Jan 2006 21:42:32 -0600
>Received: from mail pickup service by hotmail.com with Microsoft
>SMTPSVC;Wed, 18 Jan 2006 19:42:39 -0800
>Received: from 65.54.168.200 by by113fd.bay113.hotmail.msn.com with
>HTTP;Thu, 19 Jan 2006 03:42:38 GMT
>X-Message-Info: N4u0pqWW+O2m+Q9YFz/olxxlTiooEfJ3vTshhc4HOUo=
>X-OriginalArrivalTime: 19 Jan 2006 03:42:39.0002
>(UTC)FILETIME=[64EEAFA0:01C61CAA]
>X-BeenThere: accessd at databaseadvisors.com
>X-Mailman-Version: 2.1.6-xhtml
>Precedence: list
>List-Id: Access Developers discussion and problem
>solving<accessd.databaseadvisors.com>
>List-Unsubscribe:
><http://databaseadvisors.com/mailman/listinfo/accessd>,<mailto:accessd-request at databaseadvisors.com?subject=unsubscribe>
>List-Archive: <http://databaseadvisors.com/pipermail/accessd>
>List-Post: <mailto:accessd at databaseadvisors.com>
>List-Help: <mailto:accessd-request at databaseadvisors.com?subject=help>
>List-Subscribe:
><http://databaseadvisors.com/mailman/listinfo/accessd>,<mailto:accessd-request at databaseadvisors.com?subject=subscribe>
>Errors-To: accessd-bounces at databaseadvisors.com
>Return-Path: accessd-bounces at databaseadvisors.com
>
>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
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com