MS Access, VBA for Working with ODBC linked tables

I had to link several Oracle tables via ODBC. It works find via Access
wizard. Nevertheless, every time the application starts, the user is
asked to enter login and I found a workaround. There are actually two
options to do this.

1. Lookup queries
2. VBA function that recreate linked tables at startup.

The problem of option 1 is the performance if you need just a smal
subset of your data. The problem is that you cannot path through a
variable such as DATE_MIN or DATE_MAX, because the server side
obviously does not know its meaning. So just standard server side sql
is applicable. If you can leave with it, use option 1 and google for
path through queries, it is quite easy to make.

Option 2 was a half day research. First I tried to extend a connection
string for a table definition and refresh it. however this does not
work and access keeps on asking the login. Strange enough, if you
completely CREATE a table definition then it works, but only for this
specific linked table that you created. If you try to open an existing
linked tables coming from the same ODBC database, guess what, you get
the login prompt.

So because re-creating linked tables every time your App got start is
the only option , i made a friendly work around.

In your autoexec macro call a function ConnectLinkedTables ()

This function I defined in a selfmade module mdlConnect

Public Function ConnectLinkedTables()

Dim myDB As Database
Set myDB = CurrentDb
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef

Set rs = CurrentDb.OpenRecordset("qADMINTABLE")
With rs

Do Until .EOF

If TableExists(!TABLE_NAME) Then CurrentDb.TableDefs.Delete
(!TABLE_NAME)
Set tdf = New DAO.TableDef
'Debug.Print tdf.Connect
tdf.Name = !TABLE_NAME
tdf.Connect = !DATABASE_PATH
tdf.SourceTableName = !TABLE_SOURCE_NAME
CurrentDb.TableDefs.Append tdf
.MoveNext

Loop

.Close

End With

' This one works like a charm!

End Function

qADMINTABLE - source if your linked tables that has to have fields
preceeded with ! in this function.
In my case qADMINTABLE is a query that joins ADMIN_TABLE - tables that
have to be linked with ADMIN_DATABASE
that has a connection string.

DATABASE_NAME DATABASE_PATH
Productivity data ASAP formulier
X:\Part\CCS624\DATAMART\ASAP\DATA\PROD_be.mdb
GenDMAAP
ODBC;DSN=GenDMAAP;SERVER=GenDMAAP;UID=etl_admin;PWD=etl_passw;
DRIVER=Microsoft ODBC for Oracle

the query:

SELECT ADMIN_TABLE.TABLE_ID, ADMIN_TABLE.TABLE_NAME,
ADMIN_TABLE.TABLE_DATABASE_ID, ADMIN_TABLE.TABLE_SOURCE_NAME,
ADMIN_TABLE.CONNECT_AT_LOGIN, ADMIN_TABLE.TABLE_DESC,
ADMIN_DATABASE.DATABASE_PATH
FROM ADMIN_DATABASE INNER JOIN ADMIN_TABLE ON
ADMIN_DATABASE.DATABASE_ID = ADMIN_TABLE.TABLE_DATABASE_ID;

so the function walks through all the tables that have to be linked
and recreate links.
P.S. You will need a function that checks if table exist, google it TableExists

Well, this basically is. Add more tables to ADMIN_TABLE or change
ADMIN_DATABASE.DATABASE_PATH if the login changes.
Your app will experience a slight delay during the start up, but still
it is shorter than time you need to type login and it is error free.
So the end user should appreciate this automation.

Kind regards / Met vriendelijke groet,
Maxim

+31 624 659 713 | Bleekerskade 26 | 6981 LB Doesburg |
www.4suc6.com | KvK 09175538