Results 1 to 4 of 4

Flummoxed by connection to SQL server 2012 from Access 2010

  1. #1
    jacksonmacd is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2019
    Posts
    3

    Flummoxed by connection to SQL server 2012 from Access 2010

    Using linked tables from Access 2010 to the SQL server 2012 backend with SQL Server Authentication. The linked tables are accessible for the current session, however, when I quit from Access and then re-open the database, it has lost the connection to the database. Furthermore, it goes into an unresponsive state for about one minute, then shows an error message that the Login Timeout has Expired. When I dismiss that dialog, a new dialog is presented to log into the SQL server. I log in with exactly the same credentials as before, and everything works fine until I quit from Access again. I have tried using a Trusted Connection, a DSN with Linked Table Manager, and variations of the code. Nothing seems to retain the current login to the SQL Server.



    Here are some debug.print statements from the code. The first one prints the .Connect property of the TableDef object that creates the table, and the second one prints the Connect property of the local Access linked tables after the TableDef object has been appended.

    ODBC;DRIVER=SQL Server;SERVER=LENOVONOTEBOOK\SQLEXPRESS;UID=Volunt eer;PWD=xxxx;APP=Microsoft Office 2003;WSID=LENOVONOTEBOOK;DATABASE=cresql;Network=D BMSSOCN

    ODBC;DRIVER=SQL Server;SERVER=LENOVONOTEBOOK\SQLEXPRESS;APP=Micros oft Office 2003;WSID=LENOVONOTEBOOK;DATABASE=cresql;Network=D BMSSOCN


    As you can see, the resulting .Connect string contains no reference to the User and Password. Is this the expected behaviour? If so, does Access store that information somewhere else on the computer? Or is it normal to require a login for every Access session?

    Here is the relinking code
    Code:
        strConnect = "ODBC;Driver={SQL Server};UID=" & Me.txtUser & ";PWD=" & Me.txtPwd & ";" ' SQL Server login
        ' Walk through the recordset and create the links.
        Do Until rst.EOF
            Set tdf = db.CreateTableDef(rst!localtablename)
            tdf.Connect = strConnect & "Server=" & strServer & ";Database=" & strDB & ";"         ' Set the Connect property to establish the link.
            tdf.SourceTableName = rst!remotetablename
            db.TableDefs.Append tdf
        Debug.Print tdf.Connect
        Debug.Print CurrentDb.TableDefs(rst!localtablename).Connect
            rst.MoveNext
        Loop
    This code worked until we recently upgraded the workstations from Windows 7 to Windows 10, and moved the SQL Server from a Windows 7 machine to a Windows Server machine. Any help would be appreciated to know how to retain the login credentials from Access to the SQL Server.

  2. #2
    jacksonmacd is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2019
    Posts
    3
    This is my first application with SQL Server, so I don't know the expected behaviour and where to start looking for the solution. Would someone please confirm they can create a table link from Access to SQL Server using SQLServer authentication and NOT have to re-login for each session. An example of a working connection string would really be appreciated: CurrentDb.TableDefs("myTableName").Connect


    Quote Originally Posted by jacksonmacd View Post
    Using linked tables from Access 2010 to the SQL server 2012 backend with SQL Server Authentication. The linked tables are accessible for the current session, however, when I quit from Access and then re-open the database, it has lost the connection to the database. Furthermore, it goes into an unresponsive state for about one minute, then shows an error message that the Login Timeout has Expired. When I dismiss that dialog, a new dialog is presented to log into the SQL server. I log in with exactly the same credentials as before, and everything works fine until I quit from Access again. I have tried using a Trusted Connection, a DSN with Linked Table Manager, and variations of the code. Nothing seems to retain the current login to the SQL Server.

    Here are some debug.print statements from the code. The first one prints the .Connect property of the TableDef object that creates the table, and the second one prints the Connect property of the local Access linked tables after the TableDef object has been appended.

    ODBC;DRIVER=SQL Server;SERVER=LENOVONOTEBOOK\SQLEXPRESS;UID=Volunt eer;PWD=xxxx;APP=Microsoft Office 2003;WSID=LENOVONOTEBOOK;DATABASE=cresql;Network=D BMSSOCN

    ODBC;DRIVER=SQL Server;SERVER=LENOVONOTEBOOK\SQLEXPRESS;APP=Micros oft Office 2003;WSID=LENOVONOTEBOOK;DATABASE=cresql;Network=D BMSSOCN


    As you can see, the resulting .Connect string contains no reference to the User and Password. Is this the expected behaviour? If so, does Access store that information somewhere else on the computer? Or is it normal to require a login for every Access session?

    Here is the relinking code
    Code:
        strConnect = "ODBC;Driver={SQL Server};UID=" & Me.txtUser & ";PWD=" & Me.txtPwd & ";" ' SQL Server login
        ' Walk through the recordset and create the links.
        Do Until rst.EOF
            Set tdf = db.CreateTableDef(rst!localtablename)
            tdf.Connect = strConnect & "Server=" & strServer & ";Database=" & strDB & ";"         ' Set the Connect property to establish the link.
            tdf.SourceTableName = rst!remotetablename
            db.TableDefs.Append tdf
        Debug.Print tdf.Connect
        Debug.Print CurrentDb.TableDefs(rst!localtablename).Connect
            rst.MoveNext
        Loop
    This code worked until we recently upgraded the workstations from Windows 7 to Windows 10, and moved the SQL Server from a Windows 7 machine to a Windows Server machine. Any help would be appreciated to know how to retain the login credentials from Access to the SQL Server.

  3. #3
    jacksonmacd is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2019
    Posts
    3
    Found the answers:
    - Access caches known connections for the duration of an active session. Must delete all linked tables, quit from Access, and re-establish valid connections to avoid the "gone off into the ether" delay while it tries to connect via an invalid connection
    - while linking to tables manually, there is a checkbox to "save passwords" that I had previously overlooked. Enabling that checkbox causes the password to be stored in the connection string. An attribute named dbAttachSavePWD can be set when the connection is created via VBA to cause the password to be saved in the connection string.

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,726
    Thanks for updating the thread with your solution. I saw the earlier posts, but didn't answer as I normally use Windows Authentication rather than SQL Server and figured somebody with more relevant experience would answer.

    Welcome to the site and sorry for the lack of responses. Normally somebody has an answer pretty quickly.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access 2010 and Server 2012 R2?
    By btidwell3 in forum Access
    Replies: 1
    Last Post: 06-03-2016, 04:30 PM
  2. access 2013 load data to sql server 2012
    By jassie in forum Programming
    Replies: 1
    Last Post: 05-18-2015, 01:30 PM
  3. Export Access 2013 to sql server 2012
    By jassie in forum Import/Export Data
    Replies: 2
    Last Post: 05-18-2015, 01:03 PM
  4. Replies: 1
    Last Post: 01-13-2015, 04:29 PM
  5. Replies: 1
    Last Post: 12-04-2012, 09:39 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums