Connect ADP to 2 SQL Servers?

[Leo K]

Using ADP (an Access project), how would you connect to databases on 2
separate servers?

The only way I know of to do this is to configure a "Linked Server" in SQL
Server Enterprise Manager.

Is there any way to have more than one DB connection in an ADP project?

 

[Norman Yuan]

As you know, ADP application has a built-in ADO connection created
automatically when it is started: CuurentProject.CurrentConnection. On the
other hand, ADP is simply a front-end app that allows you write your own
data manipulate code in VBA. So, with VBA code, you can always create you
own ADO Connection/RecordSet object, which connects to a database other than
the one CurrentProject.CurrentConnection connects to, just like what you do
in a VB stand-alone program. The other database could be another SQL Server,
or any other type, as long as ADO can connects to. Of course, you will not
be able to see the other database's table/View/SP in diesign mode, like the
db CurrentProject.CurrentConnection coneccts to.

 

[Leo K]

You rule Norman,

I did think of creating another connection with code in a VBA module, but I
wondered if the gui allowed any way to set up two conections. It didn't
look like there was any way to do this via the interface.

Your opinion confirms both of my initial thoughts. Thanx.

 

[Guest]

hI

ADP has buit in feature to do thi via linked tables. Though id does the
samething as Enterprise manager, You can perform this with much ease .

You can also either t-sql mode or linked server mode to connect it to other
server though this GUI: files >linktables

 

[Guest]

I think that it would be easier to use server-linked-servers instead of
server-linked tables