1 SQL Server/3 Db's to connect to

[cunger28]

I have one SQL server with 3 db's that I need to connect to. I have 3 odbc's
set up for each database. I'd like to be able to perform pass-through
queries, but it looks like i can only do one odbc per pass through. The 3
db's have elements that need to link across the board.

I've read about adp's, but it looks like i can access one database at a time
that way too. I'd like to avoid linking a bunch of tables if possible.

Is there a way to have more than one database in a passthrough connection
string?

Thanks,

 

[Rick Brandt]

I have one SQL server with 3 db's that I need to connect to. I have 3
odbc's set up for each database. I'd like to be able to perform
pass-through queries, but it looks like i can only do one odbc per pass
through. The 3 db's have elements that need to link across the board.

I've read about adp's, but it looks like i can access one database at a
time that way too. I'd like to avoid linking a bunch of tables if
possible.

Is there a way to have more than one database in a passthrough
connection string?

Thanks,

If all three databases are on the same server you can do it all in one
SQL statement with one ODBC connection. You will have one default
database location established by the ODBC setup. Table references in
that database will be standard (only table name required). References
for tables in the other databases will be of the format...

DatabaseName.Owner.TableName

 

[Danny J. Lesandrini]

Chris:

I like ADPs and I use them a lot these days, but I think a Pass Through
will work for you. You just need to write the SQL correctly.

Let me give it a try here with a sample cross database query. The syntax
may not be perfect, but it will be something like the following. Assume I
have a table named tblErrorLog in 3 databases on a SQL Server, and I
want to query them all in one pass-through from Access. here's how ...

SELECT ErrNum, ErrDescr, ErrDateTime
FROM MyDatabase01.dbo.tblErrorLog

UNION SELECT ErrNum, ErrDescr, ErrDateTime
FROM MyDatabase02.dbo.tblErrorLog

UNION SELECT ErrNum, ErrDescr, ErrDateTime
FROM MyDatabase03.dbo.tblErrorLog

ORDER BY ErrDateTime DESC

Now, it doesn't really matter which ODBC connection I use. In fact, this
query could be executed on ANY SQL Server database because the
table paths are fully qualified.

Is this approximately what you were asking?