Query between two databases on different servers

[brotherkennyh]

Hi,
I want to be able to create query's based on two tables that are in different databases, particularly update and insert queries.

In access it is possible to add the remote table as a linked table, in all queries the remote table can be treated and accessed with the same syntax as local tables. Can I do something like this in vb?

Both my databases are MySQL databases. One is on a local Linux server and the other on a web server.

Cheers

Kenny

 

[Herman]

You would need to enable server side cross-server queries, if there is such a thing in MySQL. In SQL Server once you enable it you can link servers and access them through their fully qualified names. Note that only one of the servers need to support it, your linux server probably has a high chance of supporting it. For example:

SELECT *
FROM [ServerA].[DB1].[dbo].[Table1] t1
INNER JOIN [ServerB].[DB2].[dbo].[Table2] t2 ON t1.ID = t2.ExID

You can also enable ad-hoc cross-server queries (again if there is an equivalent on MySQL), and use the OPENDATASOURCE() function, or equivalent, to establish the connection to the secondary server in-query. This is rather insecure though.

You could also populate yourself a local cache and query that.