Linking ODBC data tables via VBA code.

[DaveO via AccessMonster.com]

OK, so I'm dabbling with linking tables using DAO.

The overall plan is this...

I have a DSN that holds a bunch of tables, which change month to month. I
want to link these tables to my database dynamically rather than have to sift
through hundreds of tables and individually picking them out.

The problem I have is that I can't seem to code this right.

So far I have this...

Dim dbs As DAO.Database
Dim dbs1 As DAO.Database
Dim tdf As DAO.TableDef
Dim tdftest As DAO.TableDef
Dim strTblName As String
Dim strName As String

Set dbs = CurrentDb
Set dbs1 = OpenDatabase("Sybase", , "ODBC;DSN=Stack1 Remote Tables;
UID=obreporting; PWD=obreporting")
Set tdf = dbs.CreateTableDef("MyLinkedTable")

dbs1.Connect

With tdf
.Connect = "ODBC;DSN=Stack1 Remote Tables; UID=****; PWD=****"

For Each tdftest In dbs1.TableDefs

strName = tdftest.SourceTableName

If Right(strName, 4) = "0805" Then

strTblName = strName
tdf.Name = strTblName
dbs.TableDefs.Append tdf
End If

Next tdftest

End With

tdf.Name = strTblName

'dbs.TableDefs.Append tdf

Set tdf = Nothing
Set dbs = Nothing

I know that this is wrong, but hopefully it gives you an idea of what I'm
trying to do.

Problem I have is that I want to link the tables and name them what they are
in the other db, but simply can't find a way to do this.

Any help would be gratefully received.

Thanks in advance.