Programatically create ODBC connection and link tables in MS Access

oracle ms-access odbc dsn
FrustratedWithFormsDesigner picture FrustratedWithFormsDesigner · Jun 22, 2010 · Viewed 22.1k times · Source

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?

Answer

Tahbaza picture Tahbaza · Jun 22, 2010

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

Related questions

Connection string syntax for Classic ADO / ODBC / Oracle 10g EZConnect

I'm trying to connect various VBA projects to an Oracle 10g back end using ADO (2.8) and no TNS. After various attempts, we've decided that the simplest series of steps for a clean installation include: Set up an Oracle Instant Client …

Read More
Access/jet equivalent of Oracle's decode

Is there an equivalent for Oracle's decode() in Access (or Jet, for that matter). The problem I am facing is: I should sort (order) a resultset based basically upon a status and a date (with all records having status = 2) at …

Read More
Microsoft Access running out of temp space (Error 3183)

I inherited this Frankenstein application at work that utilizes an Access 2007 DB frontend with linked tables to an Oracle 10g DB. Users have started reporting that a lot of the queries are failing with an error 3183 out of temporary space. …

Read More