Linking in SQL Server

[Jeff Carlin]

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:

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?

[PilgrimPete]

I don't know how you'd go about doing this for SQL Server. You may want to look at the SQL-DMO object in VB.

However, do you need to link the tables, because in SQL Server you can use a qualified table name to reference the dbA table from dbB. The syntax is as follows
server.database.owner.objectname

But you can miss out the bits you don't need, for example:

SELECT * FROM
dbA..theTable

SELECT * FROM
dbA.dbo.theTable

SELECT * FROM
linkedserverA.dbA.dbo.theTable