none
DSN settings in connect string RRS feed

  • Question

  • I am going to use my application in several different offices in various regions, each with its own SQL Server. Each site will have a copy of MS Access front-end and SQL Server back-end with same database structure.

    I want to dynamically set MS Access application in such a way that when it runs it dynamically replaces the appropriate DSN in Connect string using Temp Variable.
    I need this because I want to create a Pass-Through query function and call it every time to run query executed over SQL Server.

    Obviously Access is saving this settings somewhere or else it could not connect to the back-end database at start up!!

    Thanks,
    K
    Friday, January 20, 2017 11:08 AM

Answers

  • You actually don’t want to use a DSN connection. Such connections are stored outside of Access – and thus pose more of security risk. And since you have to setup the DSN separate from Access, then again it more hassle for IT department.

    And keep in mind that if you using SQL logons, then Access will ALWAYS prompt the user for logon + password EVEN if the external DSN includes the user + password. So I think this quite much means DNS’s are a bad idea.

    Few realize that Access linked tables do NOT support file based DSN’s. In other words, if you link a table to SQL server using FILE based DSN, then access will “copy” the connection information and convert it to DSN-less anyway! That means if you modify the file DSN, then you MUST have Access re-link all the tables. The good part of this setup means that you don’t have to distribute the file DSN with the application after you linked tables using that file DSN. So such DSN’s are ONLY used one time during linking. You are thus free to distribute the application to any desktop without having to deploy the DSN. It is NEVER used!

    For a user/system DSN, you STILL have to include the user+password on Access startup! (the user + password in the DSN is NOT supported!). And worse user/system DSN’s are NOT files, but are settings in the registry – that’s another big hassle for deployment.

    The only way to fly here is DNS-less.

    >>I need this because I want to create a Pass-Through query function and call it every time to run query executed over SQL Server.

    You don't need to do the above. I think the above is much why you are asking your question. The solution is to adopt some DSN less approach to fix this issue.

    The “trick” for PT query is to create ONE PT query that you use everywhere in your application. That way you don’t need some temp var with a connection string. Just use this:

      With CurrentDb.QueryDefs("MyPass")
           .SQL = "exec sp_myProc " & Param1
          .Execute
       End With

    So in above we execute a store procedure called MyProc and pass it one parameter in a variable. If the PT query is to return records, then use this:

      Dim rstRecords       As DAO.Recordset
       With CurrentDb.QueryDefs("MyPass")
           .SQL = "select * from tblHotels"
           .ReturnsRecords = True
          Set rstRecords = .OpenRecordset
       End With

    Note again how simple the PT query is, and note again how we not messing with connection strings in code.

    Just make sure you table re-link code includes the above one PT query that you can then use everywhere you need some pass-through raw T-SQL.

    One last tip:

    You do NOT want to include the user + password in those connection strings. Simple execute a “logon” one time at the start of the application, and then all of your connection strings will work without the UID/PWD being required.

    How to “logon” is outlined here:

    Power Tip: Improve the security of database connections

    http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx

    So you really do need to use a DSN-less connection, and as the above shows for PT query, it really simple and you don’t mess with logons or connecting strings in your code. And read the above first part carefull - Access linked tables to SQL server don't use file based DSN's. And if you use a system DSN, then that has to be placed in the registry - and user + password STILL much be supplied if you using SQL logons. If you using windows authentication, then you can use user/system DNS's in the registry, but a file with the DSN (file DSN) can only be used during linking and changing the file after linking will not work - even when not using SQL logons.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada






    Sunday, January 22, 2017 2:09 AM

All replies

  • If I understand your question you want to create a DSN programmatically. There are two methods you can use, DBEngine.RegisterDatabase or the SQLConfigDatabase API function call. See the below link for examples:

    http://sourcedaddy.com/ms-access/creating-user-dsn.html

    The other option here is to use a DSN-less connection:

    https://support.microsoft.com/en-us/help/892490/how-to-create-a-dsn-less-connection-to-sql-server-for-linked-tables-in-access

    You can also use CreateQueryDef to set your connection string options at run time:

    https://msdn.microsoft.com/en-us/library/office/ff195966.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, January 20, 2017 1:57 PM
  • Thanks Paul, but I'm requesting about getting a name of an already created DSN on my system...

    Yes i know about DSN-Less connection but with this, as per our Commercial Director, software will be vulnerable enough to take on any computer and run easily without any additional setup (manually creating DSN connection) by Admin... that's why i'm stuck to use DSN unfortunately!

    Friday, January 20, 2017 2:05 PM
  • Thanks Paul, but I'm requesting about getting a name of an already created DSN on my system...

    Yes i know about DSN-Less connection but with this, as per our Commercial Director, software will be vulnerable enough to take on any computer and run easily without any additional setup (manually creating DSN connection) by Admin... that's why i'm stuck to use DSN unfortunately!

    So let me ask this question. Do you want to dynamically select an existing DSN for the connection? These can be enumerated through the Windows Registry API. But changing the connection string in the DSN would involve updating the entry in the Windows Registry. Just an FYI, the information for these DSNs are stored in the below Registry keys:

    64-bit:

    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

    32-bit (on 64-bit OS):

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI

    I haven't seen any of your code so I don't know exactly how you are running the pass-through queries.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, January 20, 2017 2:31 PM
  • Hi, this is the code I'm using...

    Function RunPassThroughSELECT(strSQL As String)

        Dim qdfPassThrough As QueryDef
        Dim strConnect As String

        If Not IsNull(CurrentDb.QueryDefs("qrySQLPass").SQL) Then CurrentDb.QueryDefs.Delete "qrySQLPass"

        Set qdfPassThrough = CurrentDb.CreateQueryDef("qrySQLPass")

        strConnect = "DSN=****;Uid=****;Pwd=****;DATABASE=****;" '(Using this one here)
        'strConnect = "DRIVER=SQL Server;SERVER=****;DATABASE=****;Uid=****;Pwd=****;"

        With qdfPassThrough
            .Connect = "ODBC;" & strConnect
            .SQL = strSQL
            .ReturnsRecords = True
            .Close
        End With

    End Function

                        
    • Edited by KhurramKZ Saturday, January 21, 2017 5:07 AM
    Saturday, January 21, 2017 5:06 AM
  • Hi, this is the code I'm using...

    Function RunPassThroughSELECT(strSQL As String)

        Dim qdfPassThrough As QueryDef
        Dim strConnect As String

        If Not IsNull(CurrentDb.QueryDefs("qrySQLPass").SQL) Then CurrentDb.QueryDefs.Delete "qrySQLPass"

        Set qdfPassThrough = CurrentDb.CreateQueryDef("qrySQLPass")

        strConnect = "DSN=****;Uid=****;Pwd=****;DATABASE=****;" '(Using this one here)
        'strConnect = "DRIVER=SQL Server;SERVER=****;DATABASE=****;Uid=****;Pwd=****;"

        With qdfPassThrough
            .Connect = "ODBC;" & strConnect
            .SQL = strSQL
            .ReturnsRecords = True
            .Close
        End With

    End Function

                        

    OK, so using the above code enables you to specify any DSN you want in the connection string. You indicated that you can't use a DSN-less connection so the details of that DSN are not important to you.

    Does that answer your question or do you want to go even further and enumerate the DSNs that are currently installed on the machine so that one can be selected for your connection?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, January 21, 2017 3:28 PM
  • You actually don’t want to use a DSN connection. Such connections are stored outside of Access – and thus pose more of security risk. And since you have to setup the DSN separate from Access, then again it more hassle for IT department.

    And keep in mind that if you using SQL logons, then Access will ALWAYS prompt the user for logon + password EVEN if the external DSN includes the user + password. So I think this quite much means DNS’s are a bad idea.

    Few realize that Access linked tables do NOT support file based DSN’s. In other words, if you link a table to SQL server using FILE based DSN, then access will “copy” the connection information and convert it to DSN-less anyway! That means if you modify the file DSN, then you MUST have Access re-link all the tables. The good part of this setup means that you don’t have to distribute the file DSN with the application after you linked tables using that file DSN. So such DSN’s are ONLY used one time during linking. You are thus free to distribute the application to any desktop without having to deploy the DSN. It is NEVER used!

    For a user/system DSN, you STILL have to include the user+password on Access startup! (the user + password in the DSN is NOT supported!). And worse user/system DSN’s are NOT files, but are settings in the registry – that’s another big hassle for deployment.

    The only way to fly here is DNS-less.

    >>I need this because I want to create a Pass-Through query function and call it every time to run query executed over SQL Server.

    You don't need to do the above. I think the above is much why you are asking your question. The solution is to adopt some DSN less approach to fix this issue.

    The “trick” for PT query is to create ONE PT query that you use everywhere in your application. That way you don’t need some temp var with a connection string. Just use this:

      With CurrentDb.QueryDefs("MyPass")
           .SQL = "exec sp_myProc " & Param1
          .Execute
       End With

    So in above we execute a store procedure called MyProc and pass it one parameter in a variable. If the PT query is to return records, then use this:

      Dim rstRecords       As DAO.Recordset
       With CurrentDb.QueryDefs("MyPass")
           .SQL = "select * from tblHotels"
           .ReturnsRecords = True
          Set rstRecords = .OpenRecordset
       End With

    Note again how simple the PT query is, and note again how we not messing with connection strings in code.

    Just make sure you table re-link code includes the above one PT query that you can then use everywhere you need some pass-through raw T-SQL.

    One last tip:

    You do NOT want to include the user + password in those connection strings. Simple execute a “logon” one time at the start of the application, and then all of your connection strings will work without the UID/PWD being required.

    How to “logon” is outlined here:

    Power Tip: Improve the security of database connections

    http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx

    So you really do need to use a DSN-less connection, and as the above shows for PT query, it really simple and you don’t mess with logons or connecting strings in your code. And read the above first part carefull - Access linked tables to SQL server don't use file based DSN's. And if you use a system DSN, then that has to be placed in the registry - and user + password STILL much be supplied if you using SQL logons. If you using windows authentication, then you can use user/system DNS's in the registry, but a file with the DSN (file DSN) can only be used during linking and changing the file after linking will not work - even when not using SQL logons.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada






    Sunday, January 22, 2017 2:09 AM
  • See my above response - I show how to use a pass-though without having connection stings in code. While my post was long - I will re-state that Access linked tables don't support file DSN's - they are ONLY using during linking - if you modify the file DSN, then you have re-link the tables anyway.

    You without question could provide a txt file or ini file that Access reads on startup - you then have to check if tables need to re-link, but a file DSN will not solve your problem.

    Regards,

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Sunday, January 22, 2017 2:24 AM
  • I think I've already mentioned this to the OP and he said he couldn't use DSN-less connections.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, January 23, 2017 4:44 AM
  • I think I've already mentioned this to the OP and he said he couldn't use DSN-less connections.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Much agree - much appreciate.

    The problem here is no one has informed them that Access does not really support or use file DSN’s. It one thing to suggest a DSN and to not use a file DSN.

    However very few realize that Access simply does not support nor use file DSN’s. In fact I am likely the first person in 20 years from the Access community, posts, blogs, and articles on Access to point out this amazing fact!

    Many IT departments no doubt prefer and push use of file DSN’s. That results in IT simply being able to edit the settings in that file and they are off to the races. So they have VERY good reasons for their position.

    I think the instant its pointed out that Access ignores changes to those file DSN’s and in fact coverts then “one time” on first use to "DSN  LESS" connections much changes the game and information required.

    It one thing to “suggest” DSN less, but much different to be told the alternative file based DSN's  don’t work and are not supported!

    Access does not use the file DSN on start-up and ignores them, and in fact any table connected to SQL server will ignore and not use the DSN file – they are ignored and Access uses a DSN less connection for those linked tables. In fact you can NOT even place DSNFILE=“some file name” in a linked table – Access will remove that and covert it to DSN less. So file DSN’s really don’t work in Access and they are not supported. System based (in the registry) is supported - but UID and password are not used nor supported even when you include them in the system DSN.

    Anyway, once again at least we outlined the information and suggesting there – the rest is up to the poster to process the information we given here and make the best choice that suites their needs.

    I kind of think we are here to lead the horses to the water – it’s up to them what they do with our suggesting!

    I think between you, me and others here – we done a great job providing some suggesting and information. In fact I think we lead the horses to a fresh tasty lake of really good water! – that’s all we can do!

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada



    Monday, January 23, 2017 5:36 AM
  • I don't think the OP is trying to use a "File" DSN. He is referring to either a "User" or "System" DSN.

    Once we can get clarification as to what he really wants to do we can provide the optimum solution. :-)


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, January 23, 2017 1:17 PM
  • Thanks Albert for your valuable suggestions. Sorry i was away for a week on another project.

    I've taken your advice and I'm now going for DSN-less connection because it'll be a much more hassle for me and IT department to deal with DSN connections. There will simply be more time wasted for unwanted problem. Though, I was referring to System DSN since begining.

    I didn't know about the fact that I could reuse the PT query like this, but i created the function and call it every time whenever required. But I think if i need to change query for another table/view in a string being passed on, then I've to recall the function?

    Thank you for your time and suggestions.

    BR,
    K

    Saturday, January 28, 2017 7:33 AM