multiple databases in sql server and access

[Dru Snyder]

I'm trying to move a system from a networked access design to an access
frontend with sql server backend. I'm not sure which method I'm going to go
with at ths point, adp or mdb. My problem is that I need to have 3
databases in sql server. How do I link the databases on the access frontend
to multiple databases? I read that an adp can only connect to one database
on a sql server. Is there a way to do this? If so, how? And is there a
book that explains all this?

Thanks,
Dru

 

[Mary Chipman]

One of the (many) advantages of an mdb over an adp is that you can
link to multiple heterogeneous data sources, not just connect to one
SQLS database. However, you can also query other databases in SQLS by
creating views, stored procedures, or UDF's in one of them that
reference any database on the same server using the full syntax:
"select * from pubs.dbo.authors"

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[Karen Vaudreuil]

If you use SQL Server, it's better to use an adp. It's true that adps
connect only to one database, but you can access objects from other
databases through views and stored proc by referencing the database name,
like: MySecondDatabase.dbo.MyObjectName.

Karen

 

[Mary Chipman]

Better how? Even Microsoft no longer supports this assertion, based on
statements made by the Access team at various conferences in the last
year. If you're going to make a blanket statement like that to someone
looking for advice, at least come up with a fact or two to support it.
Sure, you can reference other ojbects from views and procs, but so
what?

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446