[AccessD] DSNs

Jurgen Welz jwelz at hotmail.com
Wed Jul 18 16:17:46 CDT 2007

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 

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.

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

>From: "jwcolby" <jwcolby at colbyconsulting.com>
>I am working with Access as the FE to processing orders where I need to
>export to a fixed width file because Access stores the format, which is
>convenient until I come up to speed on how to do something similar in SQL
>Server.  The issue is that the DSN is specific to a database name.  How can
>I edit that to point to a different database?
>John W. Colby
>Colby Consulting
>AccessD mailing list
>AccessD at databaseadvisors.com
>Website: http://www.databaseadvisors.com

New Windows Live Hotmail is here. Upgrade for free and get a better look. 

More information about the AccessD mailing list