accessing a table on another server

[coolmoe]

How does one through a sql statement access table 'coolone' located on server 'a' from server 'b'?

 

[jrbarnett]

Hi,

You can either create a linked table to the remote access database (it doesn't matter that its on a different server) and then write SQL that uses the two, or create an ODBC connection to it and connect through that.

John

 

[coolmoe]

Actually, i am looking at access another sql server database from another sql server database.
server 'a' has table 'tbl_cars'
server 'b' has table 'tbl_colors'

i wish in a stored procedure for example to link from server 'a' to tbl_colors in server 'b'. also, there is a login and password to get to the other server.

 

[jrbarnett]

Hi,

I would ask in the SQL Server forums here (use the search box at the top to locate them), as this relates purely to Microsoft Access databases.
I don't know much about SQL Server at all.

John

 

[cmmrfrds]

If the join to the other table is more adhoc then, sql server has 2 functions that will allow you to do this. Check out the OPENROWSET and OPENQUERY functions in sql server. The other option is to ask the DBA to do a linkedserver on the 2nd table from the other database and it will appear to you as if it is in the first database. I would do this for a more frequent/permenant need.

 

[cmmrfrds]

Here is an example of using the OPENROWSET function which in this example is joining to an Access table but it can be an sql server table from another database. It is using Northwinds which you probably have installed.


Public Function rowset()

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
'--- could put variables in at this point for location and database name
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If
End Function