Connecting to multiple SQL Server databases

[jhmosow]

I am using Access 2003 ADP and need to access tables in multiple
databases on my SQL server. I run the link table wizard and create a
new SQL server connection, however my only option is Transact SQL and
the Linked Server option is not available. All works properly but when
I am asked to which tables I want to link to for the project, the
Available Tables section is empty so I am unable to attach to any
tables in my additional database. Any help is appreciated.

TIA

 

[Rick Brandt]

I am using Access 2003 ADP and need to access tables in multiple
databases on my SQL server. I run the link table wizard and create a
new SQL server connection, however my only option is Transact SQL and
the Linked Server option is not available. All works properly but
when I am asked to which tables I want to link to for the project, the
Available Tables section is empty so I am unable to attach to any
tables in my additional database. Any help is appreciated.

TIA

My understanding was that one ADP = one SQL Server database.

You could put views for other databases into the one database and then use
those.

 

[jhmosow]

In looking at the help from Access 2003, it looks like I should be able
to connect to multiple servers/databases. Maybe I am reading it
incorrectly.

 

[Rick Brandt]

In looking at the help from Access 2003, it looks like I should be
able to connect to multiple servers/databases. Maybe I am reading it
incorrectly.

Are you sure it's a section of the help file that refers to ADPs and not to
MDBs?

I could be wrong, but I thought an ADP could only connect to a single db. I
mean in terms of seeing that db's objects in the GUI. In code I imagine you
can connect to any DB you want and do stuff with ADO.

 

[jhmosow]

When running the Link Table Wizard, it allows you to create/use a new
data source. Access creates a connection file with the information you
specified. I tried doing this, selecting the new data source thinking
I can add additional servers/databases using this method. I can't see
that I can't do this and all seems to work with the exception of being
able to view the tables in the new data source.

 

[dbahooker]

wow you guys are crazy

ok. northwind

if you want to pull something out of tempDb then you use

'select * from tempDb.dbo.Sysobjects'

no linking, no bullshit-p- much better than MDB

ADP is a super easy way to connect to one db server and pull info from
as many sql databases as you would like.