Creating A Link To Sql Server Table In Code

[jaydwest]

I have written the following code to create a table in a SQL Server 2000 Database.

mySQL = "CREATE TABLE tbl_PriceAndAvail_" & strDistCode _
& "(Code varchar(255) NOT NULL, " _
& "PartNumb_1 varchar(50) NOT NULL," _
& "Price_1 money NOT NULL) "

Set cmd = MakeStoredProc(cnn, "sp_executesql")
Set prmStatement = cmd.CreateParameter("@Statement", adVarWChar, adParamInput, 4000, mySQL)
cmd.Parameters.Append prmStatement
cmd.Execute
DoEvents

MakeStoredProc is a Function I have created that makes to stored Proc. All this works great. Awesome!

The Connection string looks like:
Set cnn = New ADODB.Connection
strCnn = "Provider=SQLOLEDB; Data Source=DIMENSION4100; Initial Catalog=MYDB; Integrated Security=SSPI; Persist Security Info=False;"
cnn.Open strCnn

Now I want to link the SQL Server table to Access. Here's the code I'm trying.

Set tbl = New ADOX.Table
tbl.Name = strTableName
Set tbl.ParentCatalog = catUserDB
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Datasource") = "DATABASE=DIGI"
tbl.Properties("Jet OLEDB:Remote Table Name") = "dbo." & strTableName
catUserDB.Tables.Append tbl
catUserDB.Tables.Refresh

Unfortunately, it doesn't like the Properties statements. When it hits the first statement, I get the following error message.

Item cannot be found in the collection corresponding to the requested name or ordinal.

What's wrong?

Thanks for your help.