Henry Combrinck wrote:
>> <>It sounds to me like the real problem is with non-schema-aware client
>> software.
>> They're using Office XP Developer (Access 2000). No hope of fixing that.
>
No problem at all.
It's easy to automate the table linking process.
I have a table in access that holds - among other things - the internal
and external name of my linked tables, in which database, schema and
server they locate.
This is used by a little procedure that goes through this list and
creates the ODBC linked table entries for me.
You need a connection-string:
strConn = "ODBC;"
strConn = strConn & "DSN=" & strDSN & ";"
strConn = strConn & "DATABASE=" & strDBName & ";"
strConn = strConn & "UID=" & strDBUser & ";"
strConn = strConn & "PWD=" & strDBPassword & ";"
strConn = strConn & "TABLE=" & strDBScheme & "." & strTblExtern
You should check if an entry allready exists.
If it exist test if it works by calling
CurrentDb.TableDefs(strTblIntern).RefreshLink
Catch an error in case, the link info is stale.
If an error rose then refresh the link infos.
Set db = CurrentDb()
Set tbl = db.TableDefs(strTblIntern)
If tbl.Connect <> strConn Then
tbl.Connect = strConn
tbl.RefreshLink
End If
If the table entry doesn't exist, create it.
Set tbl = db.CreateTableDef(strTblIntern, _
dbAttachSavePWD, _
strDBScheme & "." & strTblExtern, _
strConn)
db.TableDefs.Append tbl
Dropping and recreating the links takes a little longer than, first
checking for existence.
############################
BTW this creates a link entry for a table in another Access mdb-file
DoCmd.TransferDatabase acLink, "Microsoft Access",
"d:\some\folder\my_access.mdb", acTable, strTblExtern, strTblIntern
