Connecting to multiple SQL Server databases

  • Thread starter Thread starter jhmosow
  • Start date Start date
J

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
 
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
Click to expand...

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.
 
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.
 
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.
Click to expand...

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.
 
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.
 
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.
 
Back
Top