Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Machine Data Source Not Available For Pass Through Query

    Hi all,

    Using Access 2013 and SQL 2012 with AdventureWorks2012 on Windows 7 Home Premium 64-bit.

    I added an ODBC Data Source through Administrative Tools in the Control Panel - I can see it in ODBC Data Source Administrator, when I test the connection it yields successful.
    However, when I go to Access to create a Pass Through Query and click on the ellipses for the ODBC Connection String, I do not see the Data Source listed under Machine Data Source.

    Any ideas?

    thx
    w

  2. #2
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Code:
    Sub myTblLinkUpdate()
    'PURPOSE: EMBEDS THE OBDC CONNECTION STRING IN A LINKED TABLE SO USER DOES NOT
    '         HAVE TO SETUP AN ODBC CONNECTION ON THEIR MACHINE
    
    Dim myDb As DAO.Database
    Dim myTbl As TableDef
    
    Set myDb = CurrentDb
    Set myTbl = myDb.TableDefs("TableName w quotes")
    
    myTbl.Connect = "ODBC;DRIVER=SQL Server;SERVER=<Server/Desktop Name>;APP=Microsoft Office 2003;" _
    & "Trusted_Connection=Yes;DATABASE=<Database_Name>"
    
    myTbl.RefreshLink
    myDb.TableDefs.Refresh
    End Sub

    Create a module in Access and paste this code in there. Make sure to set your tables see above.

    Through VBA it will establish the connection for you. I really like this because you don't have to set up ODBC on any one's machine if you are working in a corporate office and multiple instances of the database has to go out.

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks VLookup,

    I modified a bit to add a form to input the name of the server and the database.
    I used a Trusted Connection String for SQL 2012 I found on ConnectionStrings.com

    I am receiving this error
    Run-time error '3219':
    Invalid operation.
    Not sure what the problem is?

    thx
    w

    Click Event:
    Code:
    Private Sub cmdConnect_Click()
    
        Dim Server As String
        Dim Database As String
        
        With Me
            Server = .txtServer.Value
            Database = .txtDatabase.Value
        End With
        
        Call LinkTables(strServer:=Server, _
                        strDatabase:=Database)
    End Sub
    Sub:
    Code:
    Option Explicit
    
    Public Sub LinkTables(strServer As String, _
                          strDatabase As String)
    '-------------------------------------------------------------------------------------------------------------
    '-------------------------------------------------------------------------------------------------------------
    'Author:        VLookup
    'Modified by:   ws
    'Date:          6/25/2015
    'PURPOSE:       EMBEDS THE OBDC CONNECTION STRING IN A LINKED TABLE SO USER DOES NOT
    '               HAVE TO SETUP AN ODBC CONNECTION ON THEIR MACHINE
    'Info:          http://www.dbforums.com/showthread.php?1706419-Machine-Data-Source-Not-Available-For-Pass-Through-Query
    '               https://www.connectionstrings.com/sql-server-2012/
    
    '-------------------------------------------------------------------------------------------------------------
    '-------------------------------------------------------------------------------------------------------------
    
        'Declare objects
            Dim db As DAO.Database
            Dim tdf As DAO.TableDef
            
        'Declare variables
            Dim strConn As String
            
        'Initialize
            Set db = CurrentDb
            strConn = ""
            
        'Connection string
            strConn = strConn
            strConn = strConn & "Server=" & strServer & ";"
            strConn = strConn & "Database=" & strDatabase & ";"
            strConn = strConn & "Trusted_Connection=True;"
            
        'Connect Tables
            For Each tdf In db.TableDefs
                tdf.Connect = strConn
            Next tdf
            
        'Tidy up
            Set db = Nothing
            
            
    End Sub

  4. #4
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Bump.

    thx
    w

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As you build it, the connection string is not complete (some parts are missing). Try:
    Code:
    Public Sub LinkTables(strServer As String, strDatabase As String)
    
    '-------------------------------------------------------------------------------------------------------------
    '-------------------------------------------------------------------------------------------------------------
    'Author:        VLookup
    'Modified by:   Sinndho
    'Date:          2015-07-01
    'PURPOSE:       EMBEDS THE OBDC CONNECTION STRING IN A LINKED TABLE SO USER DOES NOT
    '               HAVE TO SETUP AN ODBC CONNECTION ON THEIR MACHINE
    'Info:          http://www.dbforums.com/showthread.php?1706419-Machine-Data-Source-Not-Available-For-Pass-Through-Query
    '               https://www.connectionstrings.com/sql-server-2012/
    '
    'Reasons for changes:
    '
    ' The Connect property of an ODBC attached table looks like this (ACC2000 to ACC2016):
    '
    '     ODBC;DSN=Sales;Trusted_Connection=Yes;WSID=Iskender;DATABASE=Sales;
    '
    '-------------------------------------------------------------------------------------------------------------
    '-------------------------------------------------------------------------------------------------------------
    '
        'Declare constant
            Const c_Connect As String = "ODBC;DSN=@D;Trusted_Connection=Yes;WSID=@S;DATABASE=@D;"
        
        'Declare objects
            Dim db As DAO.Database
            Dim tdf As DAO.TableDef
            
        'Declare variables
            Dim var As Variant
            Dim i As Long
            
        'Initialize
            Set db = CurrentDb
            
        'Connect Tables
            For Each tdf In db.TableDefs
                If Len(tdf.Connect) > 0 Then
                    tdf.Connect = Replace(Replace(c_Connect, "@S", strServer), "@D", strDatabase)
                    tdf.RefreshLink
                End If
            Next tdf
            
        'Tidy up
            Set db = Nothing
              
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •