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")
Do Until .EOF
If TableExists(!TABLE_NAME) Then CurrentDb.TableDefs.Delete
Set tdf = New DAO.TableDef
tdf.Name = !TABLE_NAME
tdf.Connect = !DATABASE_PATH
tdf.SourceTableName = !TABLE_SOURCE_NAME
' This one works like a charm!
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.
Productivity data ASAP formulier
DRIVER=Microsoft ODBC for Oracle
SELECT ADMIN_TABLE.TABLE_ID, ADMIN_TABLE.TABLE_NAME,
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,
+31 624 659 713 | Bleekerskade 26 | 6981 LB Doesburg |
www.4suc6.com | KvK 09175538