Programatically create ODBC connection and link tables in MS Access

Question

We are using MS Access as a reporting front-end for Oracle. The data is accessed via an ODBC connection, and there are two linked tables in Access to Oracle that are the raw data tables. I'd like to be able to set up the connection and link the tables from within the Access file. That way users don't have to worry about setting up an DSN for the ODBC connection (most of them are not very technical users, and will require hand-holding for something like this), and we can eventually have the Access file point to different test environments and generate reports.

Is it possible to have the database connection created dynamically when the file is opened, and can I dynamically change where my Linked Tables link to?

Solution 1

You want a DSN-less linked table connection from Access. It is possible and I've done it but I don't have the code with me. I think it was something like the below (this uses a SQL Server source but Oracle would just have a slightly different connection string). To have the table(s) created on startup you'll need to check for the existence of each tabledef prior to attempting to create them again and call a subroutine like the below upon Access database open.

Function LinkTables()
    Dim DB As Database, tDef As TableDef
    Set DB = CurrentDb
    Set tDef = DB.CreateTableDef("YourAccessLinkedTableNameHere")
    tDef.Connect = "ODBC;Driver={SQL Server};Server=srvname;Database=dbname;UID=sqluserid;PWD=sqlpwd"
    tDef.SourceTableName = "dbo.YourSourceTableNameHere"
    DB.TableDefs.Append tDef
End Function

Solution 2

I do my programming on a workstation with a DSN defined, and then before distributing for production use, run a variant of Doug Steele's code to convert all the DSN-based connect strings to be DSN-less.