Creating a System DSN - Example

AndyP · Post by **AndyP** » Tue Dec 02, 2008 2:06 pm

Please see forum thread http://forums.runrev.com/phpBB2/viewtopic.php?t=2382 for the history to this item

The Problem:

Ok so you've got a client who insists on using Access, he's asked you to come up with a solution which uses their existing database over their network and this has to be deployed on multiple computers.

Solution

Create a System DSN on each computer on program first run.

First an extract from Microsoft as an explination to System DSN

System DSN
Unlike a User DSN, a System DSN is not user-specific. A System DSN is stored locally and is not dedicated to a particular user. Any user who logs on to a computer that has permission to access the data source can use a System DSN. Some programs, such as Microsoft SQL Server or Microsoft Internet Information Server (IIS), require a System DSN. This DSN must be created on the server where the program is located. System DSNs are stored in the Windows registry under the following key:
HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\Odbc Data sources

Method

1. Create a System DSN on one of the computers in the normal way. I named mine 'ajpname' for this example.

2. Open up the registry editor and navigate to HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\ajpname
Export this entry and open it in a text editor.
Here is what my example looks like.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname]
"Driver"="C:\\WINDOWS\\system32\\odbcjt32.dll"
"DBQ"="D:\\HDDB\\hdaccess 2003.mdb"
"Description"="ajpdesc"
"DriverId"=dword:00000019
"FIL"="MS Access;"
"SafeTransactions"=dword:00000000
"UID"=""

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\Engines]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\Engines\Jet]
"ImplicitCommitSync"=""
"MaxBufferSize"=dword:00000800
"PageTimeout"=dword:00000005
"Threads"=dword:00000003
"UserCommitSync"="no"

3 . We now need to re-create the registry entries via Revolution.
Code below: change to match your exported values.

get setRegistry("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\","")

get setRegistry("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\Driver","C:\WINDOWS\system32\odbcjt32.dll")

get setRegistry("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\DBQ","D:\HDDB\hdaccess 2003.mdb")
get setRegistry("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\Description","ajpdesc")

put "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\DriverId" into theKey
get setRegistry(theKey, binaryEncode("I", 00000025), "dword")

get setRegistry("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\FIL","MS Access")

put "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\SafeTransactions" into theKey
get setRegistry(theKey, binaryEncode("I", 00000000), "dword")

--Engines/Jet

get setRegistry("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\Engines\","")
get setRegistry("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\Engines\Jet\","")

get setRegistry("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\Engines\Jet\UserCommitSync","no")

put "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\Engines\Jet\MaxBufferSize" into theKey
get setRegistry(theKey, binaryEncode("I", 00002048), "dword")

put "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\Engines\Jet\PageTimeout" into theKey
get setRegistry(theKey, binaryEncode("I", 00000005), "dword")
put "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ajpname\Engines\Jet\Threads" into theKey
get setRegistry(theKey, binaryEncode("I", 00000003), "dword")

That's it. I put the code in On OpenCard and obviously you need to add extra checks to see if the registry entry already exists etc.

This is my first contribution back to the forums so I hope someone will find it useful.

Mark · Post by **Mark** » Thu Dec 04, 2008 12:50 pm

Hi Andy,

This looks very useful. Thanks.

Now, could you please tell how to use the System DSN after creating it?

Thanks again,

Mark

AndyP · Post by **AndyP** » Fri Dec 05, 2008 10:13 pm

Hi Mark,

Apologies for the late reply.

There is a forum entry here:

http://forums.runrev.com/phpBB2/viewtopic.php?t=334

which has an example

mwieder · Post by **mwieder** » Sat Dec 06, 2008 2:40 am

Andy- this is indeed a useful post. Thanks. It's a brute-force method, but as always with Windows the bigger hammer gets the better result. A couple of caveats should apply of course: you'll need admin privileges to fiddle with HKLM registry entries, and for the same reason this may well fail on Vista if the UAC kicks in (untested here).

Would you happen to know whether the DBQ registry key can use a UNC address? That would allow it to be more portable.

AndyP · Post by **AndyP** » Mon Dec 08, 2008 2:14 pm

Hi mwieder,

Not sure about the UNC address, I will do more testing.

Also I've not yet tested on Vista (as yet).

Re the admin privileges. That's what I would have thought, however I just used this method loged in under a normal user (on 2 computers just to make sure) and both times were able to set the registry entries?

mwieder · Post by **mwieder** » Mon Dec 08, 2008 5:42 pm

Good to know. That's what I get for posting without testing first - it's been a while since I've been logged on as anything other than an admin and I would have sworn that HKCU was OK while HKLM would cause problems.