joining databases

weazy

is it possible to join and query an Access and MS SQL database?
there is data spread across both that i need to match.

Thanks.

jheitz

One way to do this is to create an ODBC connection to SQL Server from Access. Once you've done this and linked to the SQL Server table, you can use that table within access just as if it was an access table. Search on the web as to how exactly to do this...

weazy

so i have an odbc link to the SQL server from an access database. however, when i try to connect to the linked table i get the following error:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'SQL ServerSQLSERVER' failed., SQL state S1000 in SQLExecDirect in

am i missing something?

Thanks.

jheitz

By the error message it looks like something has not been configured properly "connection to 'SQL ServerSQLSERVER' failed"

How are you doing this? When I´ve had to do this in the past I´ve done the following:

Create an ODBC connection to the SQL Server database in Windows & test it. You should be able to connect ok
On the access database chose to link a table to the new ODBC source. Once that is done you should be able to open the table from within access without any problem.