2

I have an Access Form that uses a linked sql server table as a datasource. I will need to distribute this file to other users soon and I need a way to programmaticly install the DSN to their machines. This is the process of manually setting up the link:

External Data > More > ODBC Database > Link to data source > Machine data source tab > press new > user data source > sql server > name=up to you; server= serverName > How should SQL server verify the autheticity of the login ID? With windows NT authentication using the network login ID > Attach database File Name (database name) > choose the table and press ok

That is what I did to access my table but I would like it so that the user can press a button and get access to the table and at the same time be authenticated by using windows NT authentication.

I am having trouble finding a way to write this in access vba code can someone direct me in the right direction?

braX
  • 11,506
  • 5
  • 20
  • 33
CaptainKid
  • 57
  • 2
  • 2
  • 9
  • I think the user has to set up an ODBC System DSN first. Then they can access SQL Server through Access. – sion_corn May 01 '14 at 18:15
  • So you want to programatically create the ODBC system DSN using vba? Is that correct? – RubberDuck May 01 '14 at 18:32
  • I am not asking for SQL to Access linked server I am looking for Access to SQL. – CaptainKid May 01 '14 at 18:53
  • Yes ckuhn that is what I would like to do. – CaptainKid May 01 '14 at 18:56
  • You have to modify the windows registry to do that. I don't have time to write it up right now, but I do have a pure vba solution. That won't fit in an answer here. I would recommend looking into .reg files or contacting your IT dept to have them do it. – RubberDuck May 01 '14 at 21:11
  • 1
    @CaptainKid I'm sorry. This should not have been marked as a duplicate of that question. It's much closer to [How can I enumerate the list of DSN's set up on a computer using VBA?](http://stackoverflow.com/a/165044/3198973). I really do recommend using a .reg file if you're not familiar with vba or the windows registry. – RubberDuck May 02 '14 at 13:11
  • 1
    @ckuhn203 Heck; I'd possibly use the .reg file anyway, just because it's a simple way to accomplish the task. :) – Andrew Barber May 02 '14 at 13:51
  • ckuhn is this the only way of doing it? I dont have rights to modify my registry keys if that is required. – CaptainKid May 02 '14 at 13:54
  • 1
    @CaptainKid If you don't have rights to modify the registry keys required, you won't have rights to add the DSN. But you can use a *user* DSN, rather than a system DSN. Each user should be able to edit theirs, via both registry and API. Any API to do so, by the way, requires the permissions in the registry, also. – Andrew Barber May 02 '14 at 14:02
  • 2
    Why not use DSN-Less connection. There are tons of examples available. Here is one to start. http://support.microsoft.com/kb/892490 – AVG May 02 '14 at 13:52
  • 1
    I'm not sure if you want to make it part of your access form but [this](http://www.office-archive.com/6-ms-access/65a065672a5eba59.htm) describes how that can be done. I expect this still to work on newer operating systems. Here is the native call: http://msdn.microsoft.com/en-us/library/ms716476(v=vs.85).aspx – rene May 02 '14 at 14:11

3 Answers3

5

As a general rule you find MUCH better success by using a DSN less connection. This will eliminate many issues and problems. How to use a DSN less connection is outlined here:

http://www.accessmvp.com/DJSteele/DSNLessLinks.html

And also you do NOT want to store the user name + password in the connection string, but only “log on” one time. Again this saves huge hassles and also means your connection strings and/or DSN does not have to save and expose the user name and password in the actual links.

And this approach means you can have different logons and NOT have to re-link or change existing table links.

The follow shows how to use a cached logon and this thus allows one to have different logons without having to re-link your tables.

https://blogs.office.com/en-us/2011/04/08/power-tip-improve-the-security-of-database-connections/

I highly recommend you adopt both of the above approaches when using linked tables to SQL server.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • I had to alter this linked to script a bit to get it to work, otherwise it complained of ODBC connection failures. https://pastebin.com/Hc5qPQi5 <-- altered script, with "ALTER NOFFIE" comment indicating the two sections I changed. Note: I'm on Office/Access 2016 and connecting to SQL Server 2014 in a Windows 10 / Server 2016 environment. – Adam Nofsinger Aug 14 '18 at 16:16
4

This question is the first google result for "VBA create DSN", however I do not like the answers since they seem to revolve around touching the registry or otherwise avoiding the use of a DSN. In my case I have a project manager that wants to use a DSN because that is what they are comfortable with and so I could not avoid it. For anyone else struggling with this, I found a very straight forward way to do it elsewhere. Notably starting here.

I used code found there, here and here to cobble this together and put it in the open event of a splash screen form:

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
    (ByVal hwndParent As Long, ByVal fRequest As Long, _
    ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next

If fDsnExist("DSN=YOUR_DSN_NAME") = True Then
    'Do all of your loading or just close this form.
Else

    Dim doContinue As Integer

    doContinue = MsgBox("There is an issue with the database connection. This can be corrected now or you can reach out to support." _
        & vbCrLf & vbCrLf & "Do you want to attempt to correct the issue now?", vbYesNo, "Connection Error")

    If doContinue = vbYes Then
        Dim vAttributes As String
        vAttributes = "DSN=YOUR_DSN_NAME" & Chr(0)
        vAttributes = vAttributes & "Description=Self Explnatory" & Chr(0)
        vAttributes = vAttributes & "Trusted_Connection=Yes" & Chr(0)
        vAttributes = vAttributes & "Server=YOUR_SQL_SERVER_ADDRESS" & Chr(0)
        vAttributes = vAttributes & "Database=YOUR_DATABASE_NAME" & Chr(0)

        SQLConfigDataSource 0&, 1, "SQL Server", vAttributes

        If Err.Number <> 0 Then
            MsgBox "The connection could not be restored. Please report this error to support: " & vbCrLf & vbCrLf & Err.Description
            Err.Clear
            DoCmd.Close acForm, "frmSplash"
            DoCmd.Quit acQuitSaveNone
        Else
            MsgBox "The Connection has been restored.", , "Success"
        End If
    Else
        MsgBox "Please contact support to resolve this issue.", vbCritical + vbOKOnly, "Error"
        DoCmd.Close acForm, "frmSplash"
        DoCmd.Quit acQuitSaveNone
    End If
End If
End Sub

Function fDsnExist(strDsn)
    On Error Resume Next
    ' ------------------------------------------------------
    ' Declare Variables
    ' ------------------------------------------------------
    Dim objConnection
    Dim strReturn
    ' ------------------------------------------------------
    ' Create database object
    ' ------------------------------------------------------
    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.ConnectionString = strDsn
    objConnection.Open
    ' ------------------------------------------------------
    ' Check if database is open Correctly
    ' ------------------------------------------------------
    If Err.Number <> 0 Then
        strReturn = False
        Err.Clear
    Else
        strReturn = True
        ' ------------------------------------------------------
        ' Close database connection
        ' ------------------------------------------------------
        objConnection.Close
    End If
    Set objConnection = Nothing
    ' ------------------------------------------------------
    ' Return database status
    ' ------------------------------------------------------
    fDsnExist = strReturn
End Function

Now when the user opens the access database, the splash form checks for the existence of the DSN and if it is not found, gives the user an option to create it.

You mentioned NT authentication; I use a trusted connection under the assumption that the user is already logged into a domain and has been provided access using those credentials to the SQL server. You may need to modify the DSN connection string in order to prompt for a password and username.

Pow-Ian
  • 3,607
  • 1
  • 22
  • 31
  • +1 for importing the call to set it up. 1 downside to your solution is users need to know what to put in there and the resulting inconsistent setups. I could see using that library call to programmatically ensure consistency the way I did though. – RubberDuck Jun 21 '17 at 11:05
  • I 100% acknowledge that this is a quick and dirty solution that makes assumptions. I put it here more as a light weight jumping off point and I appreciate the feedback. – Pow-Ian Jun 27 '17 at 12:34
2

First and foremost, Albert D. Kallal is absolutely correct with his answer. If you can use a DSN-less connection, you should. However, for the sake of answering the question you asked...

ODBC DSN entries are stored in the windows registry. You can add by directly modifying the windows registry. I DO NOT recommend this if you are not familiar with the registry. You can brick a machine if you remove/alter the wrong keys. The particular keys we're looking for are located under Software/ODBC of HKEY_LOCAL_MACHINE and HKEY_CURRENT_USER, depending on whether we're looking for a System or User odbc connection respectively.

My solution is too long and involved to post in it's entirety on Stack Overflow. You can find it on my blog under VBA ODBC DSN Installer complete with class module downloads and examples of how to use them. (Full disclosure, one of them was originally written by Steve McMahon, but I have modified it for use with MS Access.)

The short version is I built a DSN class on top of Mr. McMahon's registry class in order to install DSNs when my MS Access application is started.

Community
  • 1
  • 1
RubberDuck
  • 11,933
  • 4
  • 50
  • 95