Programatically create ODBC connection and link tables in MS Access
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 tDefEnd Function
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.