Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Possible to connect to multiple DBs from one ODBC?

    My company's data warehouse contains several "data marts", all accessible from one database connection. In my SQL tool (Toad), I can freely code between them. But in Access, we use an ODBC connection that specifies a "default database" (data mart). Then I try to link to a table in Access, it only shows me tables from that defaul one set in the ODBC. Is it possible to to link to tables in other data marts from the same database in Access without creating separate ODBC's for each?

    (The only thing that would be changed in the ODBC's is the "default database")

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Generally speaking: 1 ODBC connection = 1 Server and 1 database. e.g.
    Code:
    ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes;
    1. You can create attached tables connected to several databases (on the same or on different servers) by creating several ODBC connections.
    2. You can also create attached tables without creating any connection in the ODBC manager.To do so, you use the Connect property of a TableDef object:
    Code:
    Sub CreateLinkedTable()
    
        Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes;"
        
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef("Orders")  ' Orders = Name of the table in the local Access database.
        tdf.Connect = c_Connect                 ' Connection string to the SQL Server.
        tdf.SourceTableName = "Tbl_Orders"      ' Tbl_Orders = Name of the table in the SQL Server database.
        dbs.TableDefs.Append tdf
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Sinndho View Post
    Generally speaking: 1 ODBC connection = 1 Server and 1 database. e.g.
    Code:
    ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes;
    1. You can create attached tables connected to several databases (on the same or on different servers) by creating several ODBC connections.
    2. You can also create attached tables without creating any connection in the ODBC manager.To do so, you use the Connect property of a TableDef object:
    Code:
    Sub CreateLinkedTable()
    
        Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes;"
        
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef("Orders")  ' Orders = Name of the table in the local Access database.
        tdf.Connect = c_Connect                 ' Connection string to the SQL Server.
        tdf.SourceTableName = "Tbl_Orders"      ' Tbl_Orders = Name of the table in the SQL Server database.
        dbs.TableDefs.Append tdf
        
    End Sub
    Ah, #2 should do what I need. I have no trouble using multiple ODBCs but that is not practical when this rolls out to the users. Thanks again.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Note: You can easily transform the procedure so that it receives the names for the table and the connection string as arguments:
    Code:
    Sub CreateLinkedTable(Connect As String, ServerTableName As String, LocalTableName As String)
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef(LocalTableName)  ' Name of the table in the local Access database.
        tdf.Connect = Connect                         ' Connection string to the SQL Server.
        tdf.SourceTableName = ServerTableName         ' Name of the table on the SQL Server.
        dbs.TableDefs.Append tdf
        
    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
  •