[AccessD] Creating a DSN via code

First of all, I would advise against system/user DSNs.  Access 2000 and
greater allow the use of file DSNs, where the connection information is
embedded within the table/query connection string.  Fewer configuration
issues, and no chance of the user screwing up your application by playing
around with control panel settings.

That being said, there are a few valid reasons to use a system DSN (legacy
access apps, for example).  I have accomplished this by exporting the
appropriate registry settings to a textfile ending in .reg.  Here is an
example of creating a System DSN:

-----
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MyApp]
"Driver"="C:\\WINNT\\System32\\SQLSRV32.dll"
"Description"="My App Connection"
"Server"="MYSERVER"
"Database"="MyDatabaseName"
"LastUser"=""
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"MyApp"="SQL Server"
-----

When you double click on the file, it prompt you and import the settings
into the registry.  It can be run in silent mode by typing regedit /s
<filename>.reg from a command line/run window.

Are we done?  Not quite!  This is a minor issue for most users, if you want
to use this connection with a 16-bit application, you also need to edit the
ODBC.INI file.  This can be done using the following code:

Private Declare Function WritePrivateProfileString Lib "kernel32" Alias
"WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal
lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long

Public Sub AddDSN(ByVal strDSNName As String)

    WritePrivateProfileString "ODBC 32 bit Data Sources" & Chr$(0), _
        strDSNName & Chr$(0), _
        "SQL Server (32 bit)" & Chr$(0), _
        "ODBC.INI" & Chr$(0)

    WritePrivateProfileString strDSNName & Chr$(0), _
        "Driver32" & Chr$(0), _
        "C:\WINNT\System32\sqlsrv32.dll" & Chr$(0), _
        "ODBC.INI" & Chr$(0)

End Sub

HTH,
Brett Barabash, MCP 
Tappe Construction, Co. 
Eagan, MN 
bbarabash at tappeconstruction.com 
(651) 256-6831 

"One thing a computer can do that most humans can't is be sealed up in a
cardboard box and sit in a warehouse."  -Jack Handey



-----Original Message-----
From: Jeffrey Majchrzak [mailto:jmajchrz at nycap.rr.com]
Sent: Friday, April 09, 2004 10:03 AM
To: accessD at databaseadvisors.com
Subject: [AccessD] Creating a DSN via code


I have an application that is going to have the backend upsized to SQL
Server 2000.  This application sits on about 50 desktops.  Would anyone have
any code examples that I can view to create a DSN connection?  The DSN will
have the same username and password for all users so I would like the code
to auto fill the username and password when creating the DSN.  

 

Thank you in advance for all your help.

 

Jeffrey Majchrzak

Empire Computer Consulting

-- 

--------------------------------------------------------------------------------------------------------------------
The information in this email may contain confidential information that 
is legally privileged. The information is only for the use of the intended 
recipient(s) named above. If you are not the intended recipient(s), you 
are hereby notified that any disclosure, copying, distribution, or the taking 
of any action in regard to the content of this email is strictly prohibited.  If 
transmission is incorrect, unclear, or incomplete, please notify the sender 
immediately. The authorized recipient(s) of this information is/are prohibited 
from disclosing this information to any other party and is/are required to 
destroy the information after its stated need has been fulfilled.

Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of Tappe Construction Co.

This footer also confirms that this email message has been scanned
for the presence of computer viruses.Scanning of this message and
addition of this footer is performed by SurfControl E-mail Filter software
in conjunction with virus detection software.