Can I create a user DSN to SQL Server programatically?

Hi Experts,

Is it possible to create a User DSN from VBA code or with a batch file?
LVL 5
bfuchsAsked:
Who is Participating?
 
arnoldCommented:
Here is an example, from ms note it pushes a system DSN https://support.microsoft.com/en-us/kb/184608
Which could be pushed as a startup on a computer OU/GPO which would only apply on reboot.

Altering from hkey_local_machine to hkey_current_user will allow for the creation of user DSN and ran by login script applicable via GPO to the user.
0
 
Saurabh Singh TeotiaCommented:
I believe you are looking for this...

SQL Connecting Strings

You can do the same whole lot of ways and above link covers a bunch of them..

Saurabh...
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I like   to go DNS-less using this:  http://www.accessmvp.com/DJSteele/DSNLessLinks.html

I also use this

How To Programmatically Create a DSN for SQL Server with VB
https://support.microsoft.com/en-us/kb/184608

and

http://www.exceltoolset.com/how-to-create-dsn-for-odbc-using-vba/


Also here is so untest VBA code:

Function CreateDSN()
     On Error GoTo CreateDSN_err
     DBEngine.RegisterDatabase "DSNNameHere", "SQL Server", True, "Description=Whatever" & Chr(13) & "Server=data.myserver.com" & Chr(13) & "Database=MyDatabaseName"
 CreateDSN_exit:
     Exit Sub
 CreateDSN_err:
     MsgBox Error, vbCritical
 End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bfuchsAuthor Commented:
Hi Experts, thanks for reply.

@Saurabh,
The code there looks like its meant to establish a temp connection, what I am looking for is a way to create a permanent DSN connection file on the users pc, the way I would usually do through control paned/odbc and follow the wizard, however instead of having to do this manually on each users pc, I would rather have a button on my app stating that if you're having problems connecting, or if you're a first time user click here, and that would do the job.

@Boyd,
Your second link does not appear to be valid, the third link seem to be general not specific for SQL, and its way to long, isn't there something in short, like few lines of code that just calls an API function and pass in the required parameters?

Thanks,
Ben
0
 
bfuchsAuthor Commented:
@Boyd,
Actually now I was able to open your second link, and started testing it.
Do you know what do I supposed to fill in for the following, as this is not something that is being asked when I create though the Wizard?
   DriverPath = "<path to your SQL Server driver>"
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
The second link is to Microsoft's site and it has been having ifsues today.

The second and third links are basically identical.

Warning: you must have the SQL Server ODBC Drivers installed for this to work.

 It does not matter if you are using a DSN or DSN-less connection, you are always using a ODBC driver to connect to the SQL Server.

ODBC is a generic interface. That is by design.  ODBC drivers have a generic interface and the driver does all the translation underneath fir talking to the actual server,  In theory, software that can talk to a ODBC compliant database is not back end dependent.      
 

the third link seem to be general not specific for SQL, and its way to long
Not sure what you mean by not specific to SQL Server because  ODBC configuration is very generic by design.

The length of the code really should not matter.  Good code with lots of error handle to be stable tends to be longer.

You don't have to know how any of the code works. You just have to know the correct parameter values to pass to it.
0
 
bfuchsAuthor Commented:
@Boyd,

Actually I did try that code without submitting DriverPath and somehow nothing got created.

Also I have few questions about that approach.

1- Its mentioning dealing with registry values in system with 32 bits, does that means its not compatible for win 64 Bit?
2- How can I change to be a user DSN as opposed to system DSN?
3- In general is it safe to play with registry values?
0
 
bfuchsAuthor Commented:
Warning: you must have the SQL Server ODBC Drivers installed for this to work.
I understand that, and they probably all have, however as mentioned, since this is something that I never have to fill in when using the wizard, is there a way (perhaps a function) I can use to figure out where its located, or that could be different on each pc?
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
3- In general is it safe to play with registry values?
NO. You better make sure you do a system restore point just in case you need to recover.

When you write to teh registry you have to do everything yourself that the wizard does for you.

Have you tested the VBA code in the third link.

FWIW:
I have been deploying Access apps with a SQL server for years now. I to was a headache to deal with the DSN set p. Once I went with the DNS-less method (the first link) life became much  simpler.  I don't worry about the version of Windows, user or system DSN, admin permissions or anything else like that. I have   used the exact same code with SQL Server 2000 to the very latest version.  

FYI: In all the years I have been using the DSN-Less code I have never even look through the code or even cared  how it works. Don't need to. It just works.
0
 
bfuchsAuthor Commented:
Hi Boyd,

Your third link worked halfway, it created a DSN however It assumed Windows Authentication, and I need an option to select SQL server Authentication with login and password, any idea how can I accomplish that?

Thanks,
Ben
0
 
Saurabh Singh TeotiaCommented:
Ben,

Here is the code for your reference which i use extensively and it works for users without creating a DSN for them in the computer and it connects to the sql server.. In additional it works on the sql id and password...

Also in order to run this you need to select active x data objects 2.8 library

Please make the necessary changes in the code and you will see it connect to sql without dsn...

Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset

 Dim usr As String
    Dim pwd As String
    Dim servername As String
    servername = "your server name here"
    usr = "server sql id"
    pwd = "sql password"

    If cn.State = 0 Then
        With cn
            .ConnectionString = "Provider=SQLOLEDB; " & _
                                "Data Source=" & servername & "; " & _
                                "Initial Catalog=schema that you want to load;" & _
                                "User ID=" & usr & "; Password=" & pwd & "; Trusted_Connection=no"
            .Open
            .CommandTimeout = 0
        End With
    End If

Open in new window


Saurabh...
0
 
bfuchsAuthor Commented:
OK, how exactly do I use this?

lets say I put this code in the main form (switchboard) that gets opened immediately when they open the app, It will automatically refresh all existing links to the sql tables I have in the app?

FYI- this is an Access MDB file that only half of the tables are linked to sql, the other half are Access tables, some of them are local tables.
0
 
Saurabh Singh TeotiaCommented:
You need to use this in the access module by pressing alt+f11 and in their put this as  macro by creating a new maco as this will help you to set up the connection to the sql server.

Also even in access you can select active-x data library as a reference point... Post which once the connection is established you can re-download the entire data into tables rather then creating a link tables and once the re-download is complete then you can simply disconnect it..
0
 
bfuchsAuthor Commented:
@Saurabh,

I am only using modules, never really did anything with macros..

I understand that needs a reference to active-x dll, but besides that do I have to change the entire way the App currently works?

What do you mean by re-download data into tables, disconnect ect..? can you post here a sample MDB?

Thanks,
Ben
0
 
Saurabh Singh TeotiaCommented:
Ben,

I don't have a database at my end since i use excel to do this transaction level activity like for instance downloading data from one server and passing the data to another server..

The above line or the connection line which i gave to you you can use that line to connect to your sql databaase without the user id and password in your current module structure or the way you are connecting replace that connecting string with this and try connecting...

If you want i can share the excel code with you which can do the entire transformation part about what i'm talking about..for access like i said i havent worked extensively on doing this activity on access...However if you want an excel file to fetch information from sql and update that access database i can provide you that set of code..

Saurabh...
0
 
Saurabh Singh TeotiaCommented:
And here is something which is more relevant to you and what you are doing..

https://support.microsoft.com/en-us/kb/892490?wa=wsignin1.0

Saurabh...
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@ Ben (bfuchs),

Saurabh code is using a DNS-Less connectiopn to open a recordset in Excel.  

Note: In Excel VBA code is called a Macro.



The code in the first link I provided will do everything you need.  

I use it in a Access mdb and a accdb  that links to multiple Access back ends and some table in an SQL Server.
 
During development is use a DSN to comment tot he SQL Server. After I make my mde or accde I then call  the a function (from the the code in first link)  that changes only all the SQL Server tables that use a DSN to be DSN-Less.

To use the code in the first link I provide, past all the code into a new module and save the module as modSQLConn

 To convert from DSN to DSN-Less I open the immediate windows (ctrl-g) and run:

    FixConnections "Sever\SQL_Instance", "Database_Name"

Open in new window

0
 
bfuchsAuthor Commented:
@Saurabh,

I tried the second method of the Microsoft link you provided and had the same problem as with boyd third link, it creates a DSN but does save the user name and password I provided, and therefore I end up with invalid connection, see attached.
Untitled1.png
0
 
bfuchsAuthor Commented:
@Boyd,

I (think I) followed what you wrote, copied the code from first link and then called the FixConnection function.

See attached what I got.

BTW, not sure how this code will know which tables I want to replace the DSN to DSN-Less connection?
Untitled.png
0
 
bfuchsAuthor Commented:
Hi Experts,

I will be out of the office tom, will return on Sunday, please reply and I will test upon return.

Thanks,

Have a nice weekend!
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
BTW, not sure how this code will know which tables I want to replace the DSN to DSN-Less connection?

You can look in one of the system tables to determine which tables are link and to what (Access, Excel, ODBC, etc.)
0
 
bfuchsAuthor Commented:
Hi Experts,

Any updates?

(Waiting for idea how to overcome the issues posted in attachments last week)

Thanks,
Ben
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Ben,

What were the two parameters you passed to FixConnection?
0
 
bfuchsAuthor Commented:
Hi,

FixConnections "WGCTS", "HomeCare"

the first is the name of the server
the second is the name of the database
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Try including the SQL Server instance name with the server name like this:

WGCTS\InstanceName

where InstanceName is the name of the SQL Server Instance used when it was installed.
0
 
bfuchsAuthor Commented:
how can I obtain that?
0
 
bfuchsAuthor Commented:
I tried select serverproperty('InstanceName')
got null value
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:

how can I obtain that?

The common ways I use are:

1) On the machine running the SQL Server (in your case WGCTS) look under running services

or

2) Use SSMS.
0
 
bfuchsAuthor Commented:
The select I did was from SSMS.

Doesn't look like this is the issue here, what else can you think off?

FYI- I am using Access 2000-2003 in case that matters here..
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
0
 
bfuchsAuthor Commented:
trying that code.
submitted servername and domain as requested
see attached
Untitled1.png
0
 
arnoldCommented:
When creating the ODBC connection, you must provide the credentials, but they will not be saved as part of the DSN, you would still need to provide them as part of your connection string when it is not using integrated uid=user64;pwd=password.
you can create the DSN directly using the registry addition into HKCU\software\odbc\odbc.ini\DSNName
Strings
(DEFAULT)
Description
Driver location of driver (c:\windows\system32\sqlncli.dll or c:\windows\syswow64\sqlnsli.dll for sql native client)
LastUser sa or can be anything
Server sqlserver\instance

In a 64 bit system, you have to decide which DSN your system will be using and set it in the right path
HKCU\software\ODBC\ODBC.ini  (64bit user DSN
HKCU\software\wow6432Node\ODBC\ODBC.ini  This is where the 32 bit stuff goes

If you have a 32bit application, you have to use the 32bit ODBC, 64 bit uses the 64bit.  I do not believe you can use/access the 32bit user DSN from the 64bit Application.

Presumably the reason you do not want to use DSNless as many suggested deals with you thinking the using the DSN method you would not need to include the username password, but that is not so.  The DSN with sql authentication does not save the password.

If you want to maintain the flexibility of not hard coding the SQL credentials, use a method that you will store the encoded connection string into the HKCU user registry from which you application could read it. or using DSN, encode the username/password or just the encrypted password.

In any event your application would need to provide the functionality to change the password and update the information stored in the registry.

odbcconf.exe might be a tool to use as part of the install rather incorporating it within the application.
0
 
bfuchsAuthor Commented:
@Arnold,

I have no problem providing the credentials, if that can be incorporated in the code.
However this could be a little tricky to have 2 sets of code, one for win 32  and one for 64 bits, but if it depends of the Access version, then I am not worried as all users have Access 2003 same version.

In order to implement the registry key solution, do you have an example of code I can use to perform that action?

Re odbcconf.exe, see link below what Microsoft says about it.
https://msdn.microsoft.com/en-us/library/ee388579%28v=vs.85%29.aspx

Thanks,
Ben
0
 
arnoldCommented:
This is a DSN to an access File?

usually an Access file with sql means the access file is linked in the sql server.

During the installation of the application you can create a system DSN not user specific.
Difference deals with whether it is in HKLM\software\odbc\odbc.ini or HKCU\software\odbc\odbc.ini

The 32/64 bit differentiation is important based on the application and which it tries to access...

If this is an AD environment, you can push the ODBC connection using GPO/GPP.

Create a test ODBC connection and export it.
key name: DSNname
Description string "dsn for access"
LastUser string "username"
ServerName string "sql server name\instance"

"trusted_connection"="yes" integrated security windows authentication
missing or "trusted-connection"="no" sql login.


Windows Registry Editor Version 5.00

[<SYSTEM DSN or USER DSN PATH>\RightCAD32]
"Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
"Server"="Sqlserver\instance"
"UseProcForPrepare"="0"
"Database"="databasename"
"LastUser"="lastUser"

Open in new window


Here is a link with MS example on reading/writing registry.

note that when the application runs as a user it can only read system DSN.

http://support.microsoft.com/en-us/kb/145679
0
 
bfuchsAuthor Commented:
@arnold,

Looks like there was a misunderstanding here, so let me explain in details.

We have an Access FE App that is linked to multiple BE sources.
some tables are linked to Access DB and some are to SQL 2005/2008.
The ones that are to SQL are also linked to 2 DB, both at the same server.

Now my main concern is one of those SQL databases, that I would like to avoid having to go to each users PC and setting up user DSN (the other SQL db is already set up with user dsn's).

Re the code you provided, is it possible to have it VBA? (as this is the only programming language I am familiar with).

Thanks,
Ben
0
 
Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
bfuchsAuthor Commented:
Hi,

While I am currently in middle some projects, this issue is still relevant and would like to have a solution, however would not request immediate attention from experts as I would need to reserve some time for testing their suggestions, would prefer to leave it on hold for now.

Thanks,
Ben
0
 
arnoldCommented:
You can use VB to push/change registry settings publishing the DSN. If these are systems in an AD, you can push these settings using a GPO/GPP
....

Your setup seems to dispersed. Could it not be consolidated to have all tables/dbs in an SQL environment.  Combining the data in a single location will provide for a better setup/configuration that allows backup of data that if the need arises can be restored. in your current circumstance you have to backup the access DB/s on one hand sql database on the other as well as export the linked DB creation script........
Main risk is that your backup of the SQL db and the access db are not uniform and when restored might have missing/incomplete information.....
0
 
bfuchsAuthor Commented:
Hi arnold,

you can push these settings using a GPO/GPP
Excuse me for asking such silly questions..
what do GPO/GPP stand for? are they scripting languages or programs?

Just want to clarify what the question is for.

As you can see attached, this is the wizard that I use to create a DSN locally for each pc to connect to the server.

Now I would prefer to have a batch file or script that does it for me just by double clicking.

This is needed regardless if all my data resides in one SQL database or are split into multiple DB's.

Thanks,
Ben
untitled.png
0
 
arnoldCommented:
GPO/GPP stand for group policy object, group preference policy (I think) these are used in an Active Directory environment to centrally manage computer and users.

You can using vb access the hkey_current_user to add the user DSN entry.

You could using the program installer, create the system DSN ....

I do not remember looking at the attachment.
There are many options including to make the information dynamic versus hard coded to handle .......


I am uncertain in what situation you are currently finding your self.
Reg -s user.reg can be a way to import the user DSNs
Where user.reg is the HKCU structure/example.

The registry for user DSN would require that you account for whether you are using 32 or 64 bit .......

What is the difficulty you are encountering with your approach to implement the user DSN creations?
Can you post what you've come up with!  Have you looked at vb create user DSN script?
0
 
bfuchsAuthor Commented:
Hi arnold,
What is the difficulty you are encountering with your approach to implement the user DSN creations?
The story is as follows, I serve as part time consultant and usually go in to work when most of the users are gone, we need a way to create those dsn thru a script as opposed to do it manually, as I would not trust users doing it, and for them to wait till I get to the office is also something not desired..

So to put it simple, if you look at my last attachment, you will see what work I am talking about to get it done programatically instead of doing it thru that UI.

Have you looked at vb create user DSN script?

Yes, as posted a while back, I have tried all suggestions mentioned in this thread and each gave me different problem/s.

Therefore if you can link me to something that's proven to work I will appreciate.

Thanks,
Ben
0
 
arnoldCommented:
The following is a step by step with images of configuring user DSN using gpo/GPP note you would need to have two and using WMI filters to apply/detect the is version to apply the 64 bit version on architecture=



Access 2013 while the is is 64bit, was the 32 or 64bit deployed?
http://www.petenetlive.com/KB/Article/0000805
0
 
bfuchsAuthor Commented:
@arnold,

Actually I tried looking (and following) at that site, however had problems finding those screens mentioned there.

Meanwhile I started looking at the following site (suggested above by Boyd)

https://support.microsoft.com/en-us/kb/184608

and need some help in getting this properly filled
   'Specify the DSN parameters.

   DataSourceName = "<the name of your new DSN>"
   DatabaseName = "<name of the database to be accessed by the new DSN>"
   Description = "<a description of the new DSN>"
   DriverPath = "<path to your SQL Server driver>"
   LastUser = "<default user ID of the new DSN>"
   Server = "<name of the server to be accessed by the new DSN>"
   DriverName = "SQL Server"

tried with the following and didnt work

   DataSourceName = "<testdsn>"
   DatabaseName = "<PlacementNP>"
   Description = "<a description of the new DSN>"
   DriverPath = "<C:\Windows\winsxs\x86_microsoft-windows-m..qlserver-driver-dll_31bf3856ad364e35_6.1.7601.17514_none_672bf0635cd400bc>"
   LastUser = "<MyUserName>"
   Server = "<SQL\SQLEXPRESS>"
   DriverName = "SQL Server"

No error messages but nothing gets created, perhaps you can help me figure this out?

Thanks,
Ben
0
 
arnoldCommented:
anything in <> needs to be replaced without including the <>


   DataSourceName = "testdsn"
   DatabaseName = "PlacementNP"
   Description ="testdsn user record"
   DriverPath = "C:\WINDOWS\system32\SQLSRV32.dll" or "C:\WINDOWS\system32\SQLncli.dll" or "
C:\WINDOWS\system32\SQLncli10.dll"
   LastUser = ""
   Server = "SQL\SQLEXPRESS"
   DriverName ="SQL server" or "SQL native client" or "SQL Native Client 10.0"


the above presumes that SQL is the name of the server and SQL resolves to the IP where the server is with instance SQLexpress.
NOte the example if for a SYSTEM DSN not USER DSN.

Further note that you have to distinguish whether you want the ODBC USER DSN in the 32bit context or in the 64bit context on a 64 bit system.

the location is different, for a 32bit context on a 64bit system the path is HKEY_current_user\software\microsoft\wow6432NODE\ODBC
while the 64bi instance is in HKEY_CURRENT_USER\software\miocrosoft\ODBS which is also the same path on a 32bit system.

Note further that the driver for a 32bit DSN on a 64bit system is in the c:\windows\syswow64\ path.
for 64 bit it is in the c:\windows\.. as is for a 32 bit system


What OS is running on your workstation, AD DC servers, you could get GPMC and install it on a workstation and/or install RSAT tools on windows 7 and newer workstation.
The GPMC will depending on your workstation OS, give you access to GPOs as well as GPP if you have a newer windows 7 and newer workstation.


not sure where you found the winSXS path.

Manually Create the User DSN.
Then see where it is it in your example, you can then export it and import it using
reg import /y file.reg
where the DSN is a
create a reg file in a share accessible to all users

The below will be for a 64 bit os as a 32bit DSN



Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\testdsn]
"Database"="PlacementNP"
"Driver"="C:\\WINDOWS\\system32\SQLSRV32.dll"
"LastUser"="sa"
"Server"="SQL\SQLEXPRESS"
"UseProcForPrepare"="0"

32bit on 32bit OS and 64 bit on 64 bit OS.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\SOFTWARE\\ODBC\ODBC.INI\testdsn]
"Database"="PlacementNP"
"Driver"="C:\\WINDOWS\\system32\SQLSRV32.dll"
"LastUser"="sa"
"Server"="SQL\SQLEXPRESS"
"UseProcForPrepare"="0"


Note if you use SQL Native Client which you should if you have sql2008 express and newer
SQLSRV32.dll (SQL SERVER) is commonly the sql 2000 driver and might not work as expected with newer DBS..........

once you have the file.reg.

in the user login GPO run
reg import "\\server\share\file.reg"


The Last Username is based on your access providing its own SQL login....
0
 
arnoldCommented:
Need to correct, the Driver for 32 bit DSN on a 64 bit system will be within the C:\windows\SYSWOW64\ location.
0
 
bfuchsAuthor Commented:
Hi,

Just running again with the below credentials and nothing is happening.

   DataSourceName = "testdsn"
   DatabaseName = "PlacementNP"
   Description = "a description of the new DSN"
   DriverPath = "C:\Windows\SysWOW64\sqlsrv32.dll"
   LastUser = "MyUserName"
   Server = "SQL\SQLEXPRESS"
   DriverName = "SQL Server"


Here is the full code

 Option Explicit

    Private Const REG_SZ = 1    'Constant for a string variable type.
    Private Const HKEY_LOCAL_MACHINE = &H80000002

    Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
       "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
       phkResult As Long) As Long

    Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
       "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
       ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
       cbData As Long) As Long

    Private Declare Function RegCloseKey Lib "advapi32.dll" _
       (ByVal hKey As Long) As Long
                        

Public Sub testCreateDSN()
Dim DataSourceName As String
   Dim DatabaseName As String
   Dim Description As String
   Dim DriverPath As String
   Dim DriverName As String
   Dim LastUser As String
   Dim Regional As String
   Dim Server As String

   Dim lResult As Long
   Dim hKeyHandle As Long

   'Specify the DSN parameters.

   DataSourceName = "testdsn"
   DatabaseName = "PlacementNP"
   Description = "a description of the new DSN"
   DriverPath = "C:\Windows\SysWOW64\sqlsrv32.dll"
   LastUser = "MyUserName"
   Server = "SQL\SQLEXPRESS"
   DriverName = "SQL Server"

   'Create the new DSN key.

   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
        DataSourceName, hKeyHandle)

   'Set the values of the new DSN key.

   lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
      ByVal DatabaseName, Len(DatabaseName))
   lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
      ByVal Description, Len(Description))
   lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
      ByVal DriverPath, Len(DriverPath))
   lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
      ByVal LastUser, Len(LastUser))
   lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
      ByVal Server, Len(Server))

   'Close the new DSN key.

   lResult = RegCloseKey(hKeyHandle)

   'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
   'Specify the new value.
   'Close the key.

   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
      "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
   lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
      ByVal DriverName, Len(DriverName))
   lResult = RegCloseKey(hKeyHandle)
End Sub

Open in new window


Also wondering why doesn't this code have a place for password entry, how can a dsn be created without it?

Thanks,
Ben
0
 
bfuchsAuthor Commented:
32bit on 32bit OS and 64 bit on 64 bit OS.

In general regarding the 32/64 bits, I am not sure what I'm suppose to look at (excuse me for asking silly questions:)

a- The Win OS of the desktop which we are trying to create the dsn
b- The Servers settings
c- The version of Office which will be the client app using that dsn?

Thanks,
Ben
0
 
arnoldCommented:
If your windows workstation when looking at system control panel says it is a 64 bit OS or

Systeminfo will also tellyouwhichversion.

Now if your installed application is the same version asthe OS, the hkey_current_user\software\\microsoft\odbc

The issue you have to consider is mainly impacting a system with OS running 64 bit while you office 2013/access 2013 installed is 32 bit, the path is altered as depicted in my earlier comment.
Create a user DSN using odbcad32.exe on 32/64 bit system that will create the DSN in the maipath.
C:\windows\syswow4\odbcad32.exe is the 32 bit ODBC user DSN on a 64 bit OS which will be in the wowo6432node\odbc sub key .......

Create the DSN and look through the registry at the path outlined to see the implication/effect.
0
 
bfuchsAuthor Commented:
ok by looking at the key

hkey_current_user\software\odbc
 I see the driver is on

C:\Windows\System32\sqlsrv32.dll

So I modified that and entered all info as above but still nothing gets created..

Any idea whats still missing?

PS. My win is 64 bit, see attached system info

Thanks,
Ben
Untitled.png
0
 
arnoldCommented:
Which version of office 2013 are you running, if access 2013 is 32 bit, you need the DSN created in the wow3264node\odbc the user DSN.
A 32 bit application can only access the 32 bit DSN user or system.

Run c:\windows\syswow64\odbcad32.exe for a 32 bit application on a 64bit OS the user DSN needs to be created here.
0
 
bfuchsAuthor Commented:
Hi
Which version of office 2013 are you running
We have 2003..
you need the DSN created in the wow3264node\odbc the user DSN.
Currently I see the DSN's created with the wizard are stored (in some computers) under My documents.

Run c:\windows\syswow64\odbcad32.exe
Well this will activate the wizard, while the whole purpose here is to have this created automatically without users intervention.

Thanks,
Ben
0
 
arnoldCommented:
The use of the wizard is to illustrate where the user DSN is stored which can be exported and imported by the user on login.
Hkey_current_user\software\wownode3264\odbc\odbc.ini.....

....
Depending on which if the various options provided in this thread, you can test each to see which fits your setup/needs.

Using sqlsrv32 if your SQL server is newer 2095, 2098, 2012 you would likely need to use sqlncli*.dll corresponding to the db version 10,11,12 for SQL 2008/r2,2012,2014.

Which of the option did you test and what was the effect?
0
 
bfuchsAuthor Commented:
@Arnold,

The use of the wizard is to illustrate where the user DSN is stored which can be exported and imported by the user on login.
Hkey_current_user\software\wownode3264\odbc\odbc.ini.....

I did all the above and got the following (when opening it thru notepad)

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\PlacementNP]
"Driver"="C:\\Windows\\system32\\SQLSRV32.dll"
"Server"="SQL\\SQLEXPRESS"
"Database"="PlacementNP"
"LastUser"="**"

Which of the option did you test and what was the effect?
As far I know most if not all options suggested here were tested and so far I could not get them to work, meaning either they throw an error or it simply doesnt do anything..would love to see something that is simple to apply & proven to work..

Thanks,
Ben
0
 
arnoldCommented:
Saving the above in a dsn_key.reg
This will work for 32bit OS or 64bit OS with 64 bit office I stalked.
And using a user GPO with a login script reg.exe /import \\server\sharename\dsn_key.reg

Using GPO/GPP on a system that has the DSNs setup
You will be able to use GPO to add the registry entry for the DSN name under the odbc higherarchly, then using GPP you would add the renaming items described in your example.

The group policy management interface uses the local system as the basis for what us being added.

You would need to use wmi filters detect a 64 bit is to which you would publish the 32bit DSN
A separate GPO/GPP will push the DSN that is 32bit on a 32 bit is and 64bit on a 64bit os.

What errors do you get when pushing settings using user gpos?
0
 
bfuchsAuthor Commented:
Hi Experts,Thanks to all info provided, hope sooner or later to get this implemented (& working) in my environment as well.

Ben
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.