Dan Waters
dwaters at usinternet.com
Thu Jul 19 08:12:54 CDT 2007
Excellent!
Thanks Jurgen!
Dan
-----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
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
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
Loop
fnCreateBasePath = True
ExitRoutine:
On Error Resume Next
Exit Function
ErrorHandler:
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.
Ciao
J|rgen Welz
Edmonton, Alberta
jwelz at hotmail.com
>From: "Dan Waters" <dwaters at usinternet.com>
>
>Jurgen,
>
>Could you post your CopyFile wrapper procedure?
>
>Thanks,
>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 proc As PROCESS_INFORMATION
> 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
>or
>
>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
>the
>
>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.
>
>
>
>
>Ciao
>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
>will
>
> >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
>(other
> >than a primitive timer loop) to get Access to pause have not worked for
>me.
> >
> >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
>folders
> >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
>the
> >purpose of testing and the time taken to relink was over a minute. What
>I
> >do now instead is swap the files in the linked location and rename them
>to
> >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
>with
> >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
>the
> >file 6.pee and all the files below PVData (also renamed). Since the
>linked
> >tables already exist in the Access applicaiton, the code immediately
>opens
> >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
>updating,
> >the code copies the file back to its source location so it can be opened
>in
> >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
>time,
> >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
>that
> >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
>http://databaseadvisors.com/mailman/listinfo/accessd
>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!
www.newhotmail.ca?icid=WLHMENCA153