This app has 2 SQL Server 2000 databases (on the same server). In one particular routine, I have to grab a table from dbA and bring it temporarily into dbB. In Access, ADOX works fine, as:
Code:
Set objCtlg = New ADOX.Catalog
objCtlg.ActiveConnection = p_PROJ_DB

Set objTbl = New ADOX.Table
With objTbl
    .ParentCatalog = objCtlg
    .Name = strTblName
    .Properties("Jet OLEDB:Link Datasource") = p_PROJ_DB_SRC.Properties("Data Source")
    .Properties("Jet OLEDB:Link Provider String") = "MS Access;PWD=trA1n5"
    .Properties("Jet OLEDB:Remote Table Name") = p_strTblName
    .Properties("Jet OLEDB:Create Link") = True
    .Properties("Temporary Table") = False
End With

"p_PROJ_DB_SRC" is the connection string for dbA,
"p_PROJ_DB" is the connection string for dbB,
"p_strTblName" is the table name in dbA,
"strTblName"  is the name of the new table in dbB
However, I can't figure out how to make it work in SQLServer. We ship this app in either flavor, Access or SS2K.

Anybody?