Results 1 to 3 of 3
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    113

    ODBC- connection to SQL server Native client 11.0 ... Failed

    We have 5 people using the Access Interface to connect to SQL server. I linked the tables to SQL server using DSN name. My other team members cannot access these SQL tables the get error -- ODBC connection failed message.



    I don't want to create DSN for every user, because our Access file Is on share drive and I renamed the linked tables to use in queries and forms.
    I am using .accbd access file format. We never had this connectivity issue with .mdb format.

    Any thoughts how to resolve this connectivity issue ?

    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,006
    I have differnt PC with diffent connections so I made a table of the users PC, then set accordingly

    some were set to
    SQL server Native client 11.0
    or
    SQL server
    or
    SQL server Native client 10.0

    so I put each PC in a table with their setting , then had the code look it up:

    Code:
    Public Sub Connect2Db()
    Dim sConnect, sDriver, sServer, sDB
    sServer = "myServer"
    sDB = "myDb"
    sDriver = getOdbcDriver()
    
    sConnect = "ODBC;DRIVER=" & sDriver & ";SERVER=" & sServer & ";DATABASE=" & sDB & ";Trusted_Connection=Yes"
    End Sub
    
      'Returns the computername
    Function getPCName() As String
    getPCName = Environ$("COMPUTERNAME")
    End Function
    
    Public Function getOdbcCon()
    Dim vPC
    vPC = getPCName()
    getOdbcCon = DLookup("[ConnStr]", "tPcSettings", "[PCname]='" & vPC & "'") & ""
    End Function

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,029
    don't want to create DSN for every user, because our Access file Is on share drive
    sound horribly like your users are using the same front end file - if so, you will get significant corruption issues at some point. MS advice is to split the db (which you have by virtue of using sql server BE) and each user has their own copy of the front end on their local machine

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

Similar Threads

  1. ODBC Data Source (ODBC or SQl Native Client)
    By DLTaylor in forum SQL Server
    Replies: 4
    Last Post: 11-07-2018, 06:36 AM
  2. SQL Server Native Client 11.0 - Some advice please
    By NauticalGent65 in forum SQL Server
    Replies: 4
    Last Post: 08-07-2018, 08:26 AM
  3. Replies: 0
    Last Post: 06-07-2015, 05:08 PM
  4. Replies: 1
    Last Post: 03-02-2015, 09:44 AM
  5. Connection to SQL Server ODBC Failed
    By Historypaul in forum SQL Server
    Replies: 2
    Last Post: 01-05-2015, 01:50 PM

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