-
Linking in SQL Server
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?
-
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:
Code:
SELECT * FROM
dbA..theTable
SELECT * FROM
dbA.dbo.theTable
SELECT * FROM
linkedserverA.dbA.dbo.theTable