[AccessD] CopyFile (was DSNs)

Dan Waters dwaters at usinternet.com
Thu Jul 19 08:12:54 CDT 2007


Thanks Jurgen!

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Thursday, July 19, 2007 7:58 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] CopyFile (was DSNs)

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

    On Error Resume Next
    Exit Function
    With Err
        Select Case .Number
            Case Else
                MsgBox .Number & vbCrLf & .Description, vbInformation, 
"Error - Copy"
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
End Function

Parameters are:

FileSrc :  Full path and file name.  Accepts UNC or Mapped.  The caller 
verifies the existence of the source file with Len(Dir(FileSrc)) prior to 
calling Copy.

FileDst:  Full path and file name.  As above except the caller creates the 
target path if it doesn't exist by calling a function called 
fnCreateBasePath (below).

NoOverWrite is an optional boolean parameter that if left out will not allow

the function to overwrite an existing file.  You must pass a false if you 
wish it to overwrite a preexisting target file.

To ensure the creation of the target path, I use a couple of functions.  I 
check available drives and once the target drive is verified, the caller of 
the Copy procedure also calls:

Public Function fnCreateBasePath(strCreatePath As String) As Boolean
    On Error GoTo ErrorHandler

    Dim strPath As String
    Dim lngPos As Long

    strCreatePath = Trim$(strCreatePath)
    If Right$(strCreatePath, 1) <> "\" Then strCreatePath = strCreatePath & 
    lngPos = 7
    Do Until lngPos = 1
        lngPos = InStr(lngPos + 1, strCreatePath, "\")
        If lngPos Then
            strPath = Left$(strCreatePath, lngPos - 1)
            If Not Len(Dir(strPath, vbDirectory)) > 0 Then
                MkDir strPath
            End If
        End If
        lngPos = lngPos + 1
    fnCreateBasePath = True

    On Error Resume Next
    Exit Function
    With Err
        Select Case .Number
            Case Else
                MsgBox .Number & vbCrLf & .Description & vbCrLf & vbCrLf & _
                " Error in creating Folder:  '" & strCreatePath & "'", _
                vbInformation, "Error - fnCreateBasePath"
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
End Function

In my environment I am guaranteed that the lower base path inicluding the 
drive letter willl exceed 7 characters once the drive has been verified 
which is why lngPos starts at an initial value of 7.  If any part of the 
procedure fails, it reports failure to the caller and the caller aborts with

an error message to the  user.  Because it is synchronous, there is no 
misreporting on old data that failed to be overwritten.

For certain kinds of files, I verify that no one has the source file open by

calling a file Open (help file says Open pathname For mode [Access access] 
[lock] As [#]filenumber [Len=reclength]) and attempting to open exclusive.  
Alternatively, if you've set  up your file structure like mine where files 
related to a record are in or below a single folder, you can check whether 
anyone is working in any files related to the record by attempting to rename

the folder first.

I'm certain the filesystemobject will let you do these things as well but it

requires a reference to some scripting library that is not enabled in my 
users' environment.  In any event, the built in VBA file and folder methods 
are fast and totally reliable and don't require loading external libraries 
so without a need to use the FSO, I'll leave things as they are.

J|rgen Welz
Edmonton, Alberta
jwelz at hotmail.com

>From: "Dan Waters" <dwaters at usinternet.com>
>Could you post your CopyFile wrapper procedure?
>Dan Waters
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
>Sent: Wednesday, July 18, 2007 8:26 PM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] DSNs
>I call the CopyFileA API from Lib "kernel32" that gives me an overwrite
>parameter and that proceeds synchronously.  Alternatively I could call a
>ShellWait procedure as follows:
>Public Sub ShellWait(pathname As String, Optional WindowStyle As Long)
>     Dim start As STARTUPINFO
>     Dim ret As Long
>     With start
>         .cb = Len(start)
>         If Not IsMissing(WindowStyle) Then
>             .dwFlags = STARTF_USESHOWWINDOW
>             .wShowWindow = WindowStyle
>         End If
>     End With
>     ret& = CreateProcessA(0&, pathname, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS,
>0&, 0&, start, proc)
>     ret& = WaitForSingleObject(proc.hProcess, INFINITE)
>     CloseHandle proc.hProcess
>End Sub
>but this has not been necessary as my copyfile wrapper waits until the copy
>completes before returning success or fail.  No need for a timer kludge.
>This is the method I've used since Access97 was brand spanking new with 5 
>6 users and it works just as well with over 40.  I've never known this
>approach to fail.  In fact, every estimate we've done with Timberline in 
>past 9 years has used this method and there are 10s of thousands of these
>files.  The links for reporting that have been created must be in the 100s
>of thousands.
>J|rgen Welz
>Edmonton, Alberta
>jwelz at hotmail.com
> >From: "Hale, Jim" <Jim.Hale at fleetpride.com>
> >
> >Isn't this process asynchronous, so there is a danger the user or code 
> >move to other activities before the files have finished copying?  I have
> >had issues where dos copies or deletes haven't finished before the Access
> >code tries to use the file. Solutions posted here from time to time 
> >than a primitive timer loop) to get Access to pause have not worked for 
> >
> >Jim Hale
> >
> >-----Original Message-----
> >From: accessd-bounces at databaseadvisors.com
> >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
> >Sent: Wednesday, July 18, 2007 4:18 PM
> >To: accessd at databaseadvisors.com
> >Subject: Re: [AccessD] DSNs
> >
> >In my environment security renders me unable to create, modify or delete
> >DSNs.  Access connects to data in a series of files in a couple of 
> >in order to read or update information stored by something called a
> >'Pervasive Database Engine'.
> >
> >I tried relinking to different files at one time when I had rights for 
> >purpose of testing and the time taken to relink was over a minute.  What 
> >do now instead is swap the files in the linked location and rename them 
> >the required name.  The file system can move the files in a second or two
> >and I can simply use the linked tables with the existing DSN.  My DSN
> >points
> >to a series of files in a specific folder and I have a prelinked file 
> >a
> >name based on the logged in UserID so it is useable in a multiuser
> >envrionment.  I created the initial link and took the time hit at that
> >point.
> >
> >When a user, whose ID is 6, doubleclicks a file named '\...\...\PetSmart
> >Expansion - Delta.pee' in one of my lists, that file and some 60 files
> >below
> >it in a subfolder named PVData get copied to the DSN folder overwriting 
> >file 6.pee and all the files below PVData (also renamed).  Since the 
> >tables already exist in the Access applicaiton, the code immediately 
> >a
> >recordset and reads, processes and reports on or updates the file.  For
> >reporting, I just leave the file in place and overwrite it.  For 
> >the code copies the file back to its source location so it can be opened 
> >its source application.  When we create new takeoff files, we use Access
> >data to prefill in the names of the Architects, Engineers, Suppliers,
> >Owners, Contractors, Estimator, our company information, bid closing 
> >closing date, location, contacts and a ton of other information that we
> >track in Access.  This saves our users a ton of time and helps ensure 
> >the information has been validated in Access rather than entered via an
> >application that does not permit validation.
> >
> >This approach was faster and painless in that it was easier to circumvent
> >security restrictions that it would have been to try to change them.
> >
> >Ciao
> >J|rgen Welz
> >Edmonton, Alberta
> >jwelz at hotmail.com
> >
> >***********************************************************************
> >The information transmitted is intended solely for the individual or
> >entity to which it is addressed and may contain confidential and/or
> >privileged material. Any review, retransmission, dissemination or
> >other use of or taking action in reliance upon this information by
> >persons or entities other than the intended recipient is prohibited.
> >If you have received this email in error please contact the sender and
> >delete the material from any computer. As a recipient of this email,
> >you are responsible for screening its contents and the contents of any
> >attachments for the presence of viruses. No liability is accepted for
> >any damages caused by any virus transmitted by this email.
> >
> >--
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.c
>AccessD mailing list
>AccessD at databaseadvisors.com
>Website: http://www.databaseadvisors.com

Windows Live Hotmail with drag and drop, you can easily move and organize 
your mail in one simple step. Get it today! 

More information about the AccessD mailing list