Programmitcally linking SQL Server Tables from within Access

[KML]

Hello all,

I'm using the following code to attempt to link a table from a form
within Access (the link only needs to be established once as a
workaround for another issue, which I won't go into).

Dim strDatabase As String
Dim objCurrentDB As Database
Dim objAccessTableDef As TableDef
Dim objExternalDB As Database
Dim strConnect As String
Dim strTable As String

strTable = InputBox("Please enter the table name to link to",
"Enter Table Name")
strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
SECURITY INFO=FALSE;INITIAL CATALOG=KrisTest;DATA
SOURCE=KrisTest;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=KRISLPC"

Set objCurrentDB = CurrentDb
Set objExternalDB = OpenDatabase("", False, False, strConnect)

Set objAccessTableDef = objCurrentDB.CreateTableDef(strTable,
dbAttachSavePWD)
objAccessTableDef.Connect = objExternalDB.Connect
objAccessTableDef.SourceTableName =
objExternalDB.TableDefs(strTable).Name
objCurrentDB.TableDefs.Append objAccessTableDef


Set objAccessTableDef = Nothing
Set objExternalDB = Nothing
Set objCurrentDB = Nothing

The code does work, but it's not fully automated. I am prompted to
"Select Data Source", and the code will establish the link if I select
it, but I thought my connection string was already specifying the Data
Source so I'm kinda confused. I suspect the code doesn't like my
connection string for some reason. Really all I need is a connection
string that works for the following criteria:

System DSN is already set up and working - Name: "KrisTest"
SQL Server 8.0 Database, database name is also "KrisTest"
Database is using Windows integrated security
I'm using Access 2002

Thanks a lot!