none
Issues with DSNless SQL Server 2012 Connection String; driver does not work RRS feed

  • Question

  • I use dsn-less connection strings most of the time. I have run into an issue with the drivers for sql server 2012 which I have not been able to resolve.

    For discussion I will reference the function AttachDSNLessTable() at 

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

    as a method to test connection strings, because the code I use is more involved

    Tthe driver spec'd in that function for trusted authentication is "SQL Server"

    '//Use trusted authentication if stUsername is not supplied.
    stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

    I find that if I use a later driver up to sql server 2008 is connects fine. However with the driver for sql server 2012 (and apparently sql server 2014, there are various contradictory messages as the installer goes along) it won't connect. The specific driver is

    Microsoft ODBC Driver 11 for SQL Server ver 12.0.5543.11\x64

    The database I spec in the test is sql server 2012.

    Debug shows connection strings like these, depending on how I tweak the code:

    ODBC;DRIVER={SQL Server Native Client 11.0};DATABASE=mydb;SERVER=192.168.3.5;Trusted_Connection=Yes;
    ODBC;DRIVER=SQL Server Native Client 11.0;DATABASE=mydb;SERVER=192.168.3.5;Trusted_Connection=Yes;

    Does anyone know what the issue is?


    • Edited by rusticloud Sunday, September 17, 2017 12:45 PM
    Sunday, September 17, 2017 12:43 PM

All replies

  • Well, the native 11 or later drivers have to be installed on each client computer. If you use the default and “long time legacy SQL server, then they are installed by default.

    As a general rule I use the legacy drivers since they always will be installed on any recent version of windows – so no additional installing of software is required.

    However, one “big” reason to use the native 11 drivers if the tables in question use any “newer” datetime2 format columns. If you use the legacy drivers, then Access sees these columns as string, and not date time. So if tables user newer date time, then you REALLY have to quite much adopt the native drivers.

    I note your one connection has {} around it, and the other does not – quite sure they not required when creating the connection string. I suppose you could try both and see what occurs. But you have to be REALLY, but REALLY careful. If you at ANY time connect with a known working string, then additional connections with INCORRECT formats will ALSO work! (this is because Access caches known working connections – and once that occurs, then Access will use these alternative connections (what this means is that you MUST (and I cannot stress this MUST) exit Access and re-enter when testing if a new connection string works if at ANY point in time you connected by any means during that session with a working connection.

    I don’t know why some of your strings don’t work, but you would be best for testing in those cases to use Access to launch the ODBC panel to create a new connection. Keep in mind that any and all connections created from Access (a file DSN) are by default DSN-less. The DSN (a file dsn) are ONLY used at link time – after that they are DSN-less and thus you can distribute the application to other workstations without the DSN being required.

    So not at all sure what you issue is – but if you are using native drivers, then they have to be installed on each workstation. And if you by accident connected with a working connection, then additional testing attempts are null and void and cannot be certain they worked due to the connection caching issue – so much caution is required during testing. Once you connection, then attempts with incorrect connections will also work.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Sunday, September 17, 2017 9:13 PM
  • Thanks Albert much of that is new to me and of interest. I don't full understand what you said though about the caching of connections. Possibly it applies to a different phase of the odbc lifecycle - using it to get to the data. I can't really visualize what it adds up to even though you wrote it out twice. I would like to fully understand it.

    In my case, these are all new attempts to link the tables via dsn-less. So I don't think the caching issue comes in to play.

    The drivers for all of these odbc connection types to sql server are installed on the machines. One thing I didn't mention in an attempt to keep my post down in size is that I can freely created dsns (system is what I usually use) using the sql server 2012 driver. So the driver is not broken - it can work under the auspices of a dsn. As far as the dnsless effort goes, I can spec every single installed sql server driver in the string, and they work all the way through 2008, and then forget it. Very strange. Also, again left off of the original post for reasons of brevity, the same dsnless connection that fails on machine x will work on machine y, even though they're same OS, same driver, same version of Office. I have several pcs that work and several that fail. None of these issues arrise with sql server 2008 or less.

    And yes I do need the later generation data types.


    • Edited by rusticloud Monday, September 18, 2017 3:42 AM
    Monday, September 18, 2017 12:00 AM
  • The caching issue will apply to your case and “possible” bite you. So yes, the issue is important in this context.

    The issue may not affect your case, but during “testing” it OFTEN will.

    Say for example we launch an Access application that we want to change or do some DSN less testing/changes/debugging etc.

    If on start-up, or say after you launch the application you decide to click on a linked table. At that point in time you have NOW established a WORKING connection to the SQL server. That connection is NOW cached, NOW in memory and will REMAIN ACTIVE no matter what you do for the ENTIRE session. You can have VBA errors, you can attempt to run some re-link code or whatever you do. That connection REMAINS active! There is NO way to remove or de-active that connection (until you shut down Access).

    So you just opened a linked table - “just” opening a linked table – the above information applies. You now close that table, and say, “Hey” lets write some code to test a DSN connection. If your connection is WRONG or BAD or incorrect, Access WILL STILL return true and report that the connection is valid! What this means is if you exit the database and try the EXACTLY same connection in code it will now fail! (This is because the second time you opened the database you did not open any form, or table with an EXISTING working connection. Since you have NOT seen, or allowed ANY working connection, then the EXACT same test of a DSN now will fail! (Assuming an incorrect one).

    So what I saying is “be careful” during testing. If you at any time during the access session connect to a linked table, then that information is cached and REMAINS cached. You now might write some code to try and re-link or use a different connection – and you go, hey it works (when in fact it did not – because if it fails, the Access will look for and use any PREVIOUS connection that worked during that session). What this means is if you going to test a different connection string, you MUST exit Access, re-start Access and again ensure that during start up some stray code, form or table happens to or manages to open a table (by accident or by intention).

    All the above means in s simple sentence that if some working connection occurs, and you want to test a different connection that connection will APPEAR to work when it fact it failed.

    So anytime you want to “test” a new connection, but already connected, then the above connection caching issue can most certainly fool you into thinking what you did worked when it did not.

    Bottom line: Make sure you exit Access before you test a connection if some connection already occurred.

    Next up:

    > I can freely created dsns (system is what I usually use

    I keep this short: don’t use system DSN’s. They create entries in the registry and CAN NOT be DSN-less. I strong suggest you ALWAYS create a FILE dsn, since then Access ALWAYS converts them to DSN less – even when using the linked table manager. So using code or NOT – connections from a FILE dsn become DSN-less.

    As noted, I don’t know why you seeing some failures, but you linking tables based on system DSN’s, then that could well explain the issue.

    The “long” post (and I apologize if it seems close to a rant) is that the caching issue often will fool developers into thinking that they have a working connection when in fact they don’t.

    So as a survive guide and going “crazy” during testing, keep the caching issue in mind, and ALWAYS link tables using FILE DSN’s – avoid using anything else (including SYSTEM DSN’s). Once the tables been linked using an FILE dsn, they are now DSN less. And no doubt you may well THEN run some linking code to change the table links, but at least they started out being DSN less from the get go.

    If during testing and setup and over time you by accident used SYSTEM DSN’s, then I recommend you delete the table links, and re-link them. That can be a pain. (So you might write a small loop to write out table names to a text file or a local table in the front end. And again, after deleting the linked tables, you REALLY need to exit and then re-enter the application for the caching reasons above before you re-run or run your code that creates the table links.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Monday, September 18, 2017 1:48 AM
  • Yeah, great info, you did make it clear, or at least I understood much more this time around.

    The app is using system dsns now; I'm trying to wean it off of those. The relinking and dsnless system I have is more complicated than the function I pointed to from Microsoft. For example at relink time it deletes all of the linked tables and then recreates them from a list in a local table.

    What is going to be interesting is to use a file dsn to relink; and then inspect the connection string that those links exhibit. There is even a chance that for whatever mystery reason is messing with the dsnless system, the file dsn won't work.

    Experiments coming up. Thanks again.

    Monday, September 18, 2017 3:49 AM
  • The result of this test is very interesting indeed. Creating a link via file dsn resulted in a connection string for the linked table which ref'd the driver as

    ODBC Driver 11 for SQL Server

    If I use that in my system's relink routine, it works.

    Note that the driver is listed as "SQL Server Native Client 11.0" everywhere I've seen it.

    Anyone have a clue what the deal is with this?

    Albert thanks for the tip re file dsns.
    Tuesday, September 19, 2017 3:25 AM
  • Excellent.

    I never been quite sure what the 100% syntax for a given driver is, so I always let Access launch the ODBC panel, create the file dsn, and go from that.

    I “suppose” it goes without saying, whatever Access cooks up for a string is what one should use in code.

    I use this routine to “create” the connection string, and thus if I need to re-link say from “test” to production, then I use this code to give me the correct string:

    Public Const AppName = "MIS"

    Public Const SQLDRIVER = "SQL Server"

    Public Function dbCon(ServerName As String, _

                          DataBaseName As String, _

                          Optional UserID As String, _

                          Optional USERpw As String, _

                          Optional APP As String = "Office 2010", _

                          Optional WSID As String = "Axis") As String

        ' returns a SQL server conneciton string

       

       dbCon = "ODBC;DRIVER=" & SQLDRIVER & ";" & _

               "SERVER=" & ServerName & ";" & _

               "DATABASE=" & DataBaseName & ";"

               If UserID <> "" Then

                  dbCon = dbCon & "UID=" & UserID & ";" & "PWD=" & USERpw & ";"

               End If

               dbCon = dbCon & _

               "APP=" & APP & ";" & _

               "WSID=" & WSID & ";" & _

               "Network=DBMSSOCN"

    End Function

    So the above allows me to set the server, user etc., and the string back works quite well for me. You would have to mod the constant I have for the driver name - but the routine is handy.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Tuesday, September 19, 2017 3:55 AM