[AccessD] DSNs

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.