Results 1 to 5 of 5
  1. #1
    bwb@wiginton.net is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2017
    Posts
    2

    Linked Table with SQL Native Client

    I am trying to create a linked table to a SQL Server table with a SQL Native Client connection rather than an ODBC connection. I have a prior Access program that linked to a table with the following string in the Table Description. This program was developed by someone that is no longer with the company.

    Table Description:
    ODBC;driver={SQL Native Client};server=SQLSERVER1;database=JDE_Database;ui d=sa;pwd=;TABLE=PRODDTA.F00165

    When I add the new table I'm forced to use an ODBC connection and the Table Description then appears as:

    ODBC;DSN=erp;Description=erp;UID=sa;APP=Microsoft Office 2010;DATABASE=ERPDB;TABLE=dbo.WSDOCS

    How do I add a linked table in Access using a driver rather than an ODBC connection? A query runs against the table to return a network path that is then used to save a document to that location. I do not want to add an ODCB connection to about 20 users computers to our ERP system.



    Thank you in advance for assistance.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    How much do you know about DSN-less connections. I don't know if this is your problem or not but if you do not want to use an ODBC definition (either system or user) you can re-establish links to a SQL server without having one.

    Example:

    FT_ID ForeignTable
    1 SQLTable1
    2 SQLTable2
    3 SQLTable3


    DB_ID DB_Server DB_Name DB_User DB_Password DB_Description DB_Current
    9 DBServerName1 DBName UserName *** SourceDB1 Yes
    13 DBServerName2 DBName2 Username2 *** SourceDB2 No

    In this example there's a combo box on my welcome screen listing the most recent database to which I was attached (I have test/production servers listed in my tblDBList table both of which have the same table structure and table names)
    cboCurrentServer is the name of the combo box holding the most recent (or selected) database.

    Code:
    If DCount("*", "tblDBList", "[DB_Current] = -1") > 0 Then    cboCurrentServer = DLookup("[DB_ID]", "tblDBList", "[DB_Current] = -1")
        
        sServer = Forms("frmMain").Controls("cboCurrentServer").Column(3)
        sDatabase = Forms("frmMain").Controls("cboCurrentServer").Column(4)
        sUser = Forms("frmMain").Controls("cboCurrentServer").Column(5)
        sPW = Forms("frmMain").Controls("cboCurrentServer").Column(6)
        TestConn = TestConnection(sServer, sDatabase, sUser, sPW)
        If Len(TestConn) = 0 Then
            ReturnValue = RELINK(sServer, sDatabase, sUser, sPW, 0)
            If Len(ReturnValue) > 0 Then
                MsgBox "The following tables were not found in the database:" & vbCrLf & vbCrLf & ReturnValue, vbOKOnly, "Tables not Found"
            End If
        Else
            MsgBox TestConn, vbOKOnly, "Selected Database is Invalid"
        End If
    Else
        MsgBox "No Default Database Selected" & vbCrLf & vbCrLf & "Please select a database and click 'RELOAD SELECTED DATABASE", vbOKOnly, "No Default Database"
    End If
    Functions called in this

    Code:
    Public Function RELINK(sServer, sDatabase, sUser, sPassword, iLink)
    Dim dbCurrent As dao.Database
    Dim rstTables As dao.Recordset
    Dim sTable As String
    Dim tdfCurrent As dao.TableDef
    Dim sErrMsg As String
    
    
    On Error GoTo ERRHANDLER
    
    
    sConnStr = "ODBC;DRIVER={SQL Server};DATABASE=" & sDatabase & ";SERVER=" & sServer & ";UID=" & sUser & ";PWD=" & sPassword & ";"
    
    
    Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
    Set rstTables = CurrentDb.OpenRecordset("SELECT * FROM tblGOSHTables ORDER BY GOSHTable")
    
    
    Do While rstTables.EOF <> True
        sTable = rstTables!GOSHTable
        fldStatus = "Importing " & sTable & "..." & vbCrLf
        DoEvents
        If DCount("[name]", "MSysObjects", "[Name] = 'dbo_" & sTable & "'") = 0 Then
            'do whatever if the table does not exist adding the table is coded below
        Else
            CurrentDb.Execute ("DROP TABLE dbo_" & sTable)
        End If
    
    
        If iLink = 0 Then
            Set tdfCurrent = dbCurrent.CreateTableDef("dbo_" & sTable)
            tdfCurrent.Connect = sConnStr
            tdfCurrent.SourceTableName = sTable
            dbCurrent.TableDefs.Append tdfCurrent
    
    
        Else
            DoCmd.TransferDatabase acImport, "ODBC Database", sConnStr, acTable, sTable, "dbo_" & sTable
        End If
        HideTable ("dbo_" & sTable)
        rstTables.MoveNext
    Loop
    Application.RefreshDatabaseWindow
    rstTables.Close
    
    
    Set tdfCurrent = Nothing
    Set dbCurrent = Nothing
    fldStatus = Null
    RELINK = sErrMsg
    Exit Function
    
    
    ERRHANDLER:
    Debug.Print Err.Number & " " & Err.Description
    If Err.Number = 3011 Then
        sErrMsg = sErrMsg & sTable & vbCrLf
        Resume Next
    Else
    End If
    End Function
    Public Function TestConnection(sServer, sDatabase, sUser, sPassword)
    Dim cnn As Object
    On Error GoTo ERRHANDLER
    Set cnn = CreateObject("ADODB.Connection")
    sConnStr = "Provider=SQLOLEDB;" & _
                "Server=" & sServer & ";" & _
                "Initial Catalog=" & sDatabase & ";" & _
                "UID=" & sUser & ";" & _
                "PWD=" & sPassword & ";"
    'Debug.Print sConnStr
    cnn.Open (sConnStr)
    If cnn.State = adStateOpen Then
        cnn.Close
        TestConnection = ""
        Set cnn = Nothing
        Exit Function
    Else
    End If
    Set cnn = Nothing
    Exit Function
    ERRHANDLER:
    Debug.Print Err.Number & " " & Err.Description
    'MsgBox "The connection failed:" & vbCrLf & vbCrLf & "ERROR NUMBER:  " & vbCrLf & Err.Number & vbCrLf & vbCrLf & "ERROR DESCRIPTION: " & vbCrLf & Err.Description, vbOKOnly, "Connection Failed"
    TestConnection = "The connection to the server \\" & sServer & "\" & sDatabase & " failed" & vbCrLf & vbCrLf & "ERROR NUMBER:  " & vbCrLf & Err.Number & vbCrLf & vbCrLf & "ERROR DESCRIPTION: " & vbCrLf & Err.Description & vbCrLf & vbCrLf & "Please Select a different server or check your connections"
    End Function

  3. #3
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by bwb@wiginton.net View Post
    I am trying to create a linked table to a SQL Server table with a SQL Native Client connection rather than an ODBC connection. I have a prior Access program that linked to a table with the following string in the Table Description. This program was developed by someone that is no longer with the company.

    Table Description:
    ODBC;driver={SQL Native Client};server=SQLSERVER1;database=JDE_Database;ui d=sa;pwd=;TABLE=PRODDTA.F00165

    When I add the new table I'm forced to use an ODBC connection and the Table Description then appears as:

    ODBC;DSN=erp;Description=erp;UID=sa;APP=Microsoft Office 2010;DATABASE=ERPDB;TABLE=dbo.WSDOCS

    How do I add a linked table in Access using a driver rather than an ODBC connection? A query runs against the table to return a network path that is then used to save a document to that location. I do not want to add an ODCB connection to about 20 users computers to our ERP system.

    Thank you in advance for assistance.
    You must always use ODBC. The SQL Server Native client is the requires ODBC drivers.

    What you want is to use ODBC without creating a DSN.

    I use this: Using DSN-Less Connections

  4. #4
    bwb@wiginton.net is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2017
    Posts
    2
    Thank you. I was able to get the table connection to update with the VBA code to change the connection string. Everything is working now.

    Bryan

  5. #5
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    You're welcome.

    Glad we could assist.

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

Similar Threads

  1. Replies: 5
    Last Post: 12-30-2016, 08:37 AM
  2. Replies: 0
    Last Post: 06-07-2015, 05:08 PM
  3. Replies: 3
    Last Post: 11-25-2014, 01:14 PM
  4. Replies: 9
    Last Post: 10-20-2014, 04:00 PM
  5. Replies: 1
    Last Post: 07-26-2011, 06:10 AM

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
  •  
Other Forums: Microsoft Office Forums