CreateTabledef --can it create an ODBC-Linked table? 1

[TroyMcClure]

I'm trying to auotmate linking of odbc tables to an access database. I'm getting the error 3001 'Invalid Argument'.

Below is the line:

dim db as database, td as tabledef
set db = currentdb

set tb = db.CreateTableDef("tblCus", dbAttachedODBC, "tblCus", "ODBC;DSN=CustDB;UID=KANG;PWD=KANG")

'tblCus' is a valid table in the sql server database. CustDB is a valid odbc dsn connection, the uid and pwd are valid, I can select that dsn and link with no problem. I'm gussing the dbAttachODBC is the problem, but there are no others that seem right for this type of link.

Is it even possible to automate linking odbc tables?? Thanks,
-james

 

[thecreator]

Greetings Troy McClure,

I use the following code to link tables in an MDE every time it fires.

Run the LoadConfiguration function first to load variables, then call LinkTables to link them to your database.

Option Compare Database
Option Explicit

' declare public variables for environment settings
Public DB_Prefix As String
Public MyConnectionString As String

Function LoadConfiguration()
' ----------------------------------
' Database Settings
' ----------------------------------

DB_Prefix = "pthdbo." ' Prefix for database user
MyConnectionString = "ODBC;DSN=CustDB;UID=KANG;PWD=KANG"
' Connection string to database

End Function


Sub LinkTables()
On Error GoTo LinkTables_Err

Dim dbs As Database, tdf As TableDef
Dim MyTable As String, i As Integer
Dim arrTables(10) As String

' Define array of tables to link
arrTables(1) = "cfiacct"
arrTables(2) = "cfiproj"
arrTables(3) = "lapclas"
arrTables(4) = "lapcspa"
arrTables(5) = "lapcspb"
arrTables(6) = "lapcspc"
arrTables(7) = "lapcspm"
arrTables(8) = "lapfeca"
arrTables(9) = "lapfecb"
arrTables(10) = "lapfecd"

' Link tables to this database
Set dbs = CurrentDb

For i = 1 To UBound(arrTables, 1)
MyTable = Trim(DB_Prefix) + arrTables(i)
Set tdf = dbs.CreateTableDef(arrTables(i))
tdf.Connect = MyConnectionString
tdf.SourceTableName = MyTable
dbs.TableDefs.Append tdf
Next i

LinkTables_Bye:
' release objects
dbs.Close
Set tdf = Nothing
Set dbs = Nothing
Exit Sub

LinkTables_Err:
MsgBox Err.Description
Resume LinkTables_Bye

End Sub

Hope this help

 

[TroyMcClure]

thecreator,
Thank you, that partially worked. What it does not seem to do is save the password--every time I open a table or a form with a bound table, I get prompted.

I have the ...PWD=KANG;... as part of the string, and KANG is indeed the correct password. When I link manually, I always check that checkbox that says 'save password'. Whatever that box does is not being done with the code you have, so I'm wondering if there is another setting or line of code that does that? Thanks,
james