Link to tables via VBA

[RLN]

RE: Access 2003

I have a front end Access app that links to several tables in a backend .mdb
file.
I am trying to automate the processes contained in Linked Table Manager.
Depending on processing, I sometimes need to go in and link some tables from
a 3rd .mdb app.

Does anyone have an example via VBA that will do these two things:

1. In MyFrontEnd.MDB, interrogate for the presence of TableX in MyBackEnd.mdb
If TableX is not already linked, link it up to the front end .mdb.

2. But if TableX does not exist on the backend .mdb, then link up TableX
from MyOtherProdApp.mdb

Thank you.

 

[JonWayn]

Sub CreateLink() 'This procedure assumes that TableX will exist in either
backend
Dim dbFront As Database, dbBack As Database, dbOtherProd As Database
Dim tblX as TableDef, tblLink as TableDef

Set dbFront = CurrentDb
Set dbBack = OpenDatabase("C:\SomePath\MyBackEnd.mdb")
On Error Resume Next
CreateLink_TestExistence:
Set tblX = dbBack.TableDefs("TableX")
If tblX Is Nothing Then Err.Raise vbObjectError + 1,,"Table does not
exist in Backend. Creating link to MyOtherProdApp.mdb"
Set tblLink = dbFront.CreateTableDef("TableXClient")
tblLink.Connect = ";DATABASE = " & dbBack.Name
tblLink.SourceTableName = tblX.Name
dbFront.TableDefs.Refresh

CreateLink_Exit:
Exit Sub

CreateLink_Error:
If Err = vbObjectError + 1 Then
Msgbox Err.Description
Set dbBack = OpenDatabase("C:\SomeOtherPath\MyOtherProdApp.mdb")
Resume CreateLink_TestExistence
Else
Msgbox Err.Description & " - (" & Err & ")"
Resume CreateLink_Exit:
Endif
End Sub

 

[RLN]

Jon,

Thank you for this code sample. I won't get a chance to try it until
Monday or early part of next week. You no doubt put a lot of effort into it,
so I'm confident that it will work just fine.

One other question I might run by you if I may.
There are some Oracle tables from a production database that the app will
link to after some enhancements are made. The enhancement consist of needing
to link to two Oracles tables to run a make-table quiery (which will create
another local Access table)

I have heard that to connect to an Oracle table totally via code, that it is
indeed not possible , because the end user's workstation must have 3 things:
1) the Oracle client installed
2) must have a TNSNAMES.ORA file with a reference to the backend db.
3) an ODBC data source must be set up separately on the workstation.

Is this true? Or can Oracle tables be linked to an Access DB (with none of
the three above present) long enough for an extract, then disconnected?