Query SQL Server and MS Access Database

[Miked811]

Hi Guys,

I want to query 2 databases. The first DB is SQL Server 2000 and the 2nd is MS Access.

I created this simple query in SQL Server but, it gives me an error saying "Invalid Object Name", where ODBC_TBL2 is an ODBC connection

SELECT TBL1.Field1
FROM TBL1 
INNER JOIN ODBC_TBL2.Field1 ON 
TBL1.Field1 = ODBC_TBL2.field1

I also get the same error message for:

SELECT TBL1.Field1
FROM TBL1 
INNER JOIN [C:\DB\ACS_TBL.MDB].TBL2 AS ODBC_TBL2 ON 
TBL1.Field1 = ODBC_TBL2.field1

I need this query in an ASP application I am creating. Links does not work in ASP.

I can query similar databases e.g. SQL Server vs SQL Server and MS Access vs. MS Access.

Thanks in advance for your help

Mike

 

[jrbarnett]

Easiest way is to set up a linked server on SQL Server pointing to your Access database (this will have to be in a location accessible by the SQL Server service account, preferably in a folder somewhere on that server).

You can then treat it as an external database in a link as any other table, eg

linkeddb.tablename

If the ODBC connection is on your PC, remember that all SQL Server knows is what it has access to - any queries run via Access are executed by the SQL Server database engine and the results returned to your PC.
This is why the differing database type queries fail.

John

 

[Miked811]

Hi John,

Thank you very much for your response. I will work on it now.

Link Server it is.

Mike