Can an Access Data Project (ADP) Connect to multiple datasources?

I wanted to find out if an ADP can connect to multiple SQL Databases.  In addition to a my Primary SQL database, is it also possible to link to a table within an MDB?

Who is Participating?
SidFishesConnect With a Mentor Commented:
Yes...and no

you can't setup multiple databases in the File/Connection dialog

but it's a simple matter of using dsnless connections in your code

Dim cnn As ADODB.Connection
Dim cnn2 As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.ConnectionString = "connection string to database 1"

Set cnn2 = New ADODB.Connection
cnn2.ConnectionString = "connection string to database 2"


Jokra_the_BarbarianConnect With a Mentor Commented:
No. You won't be able to connect to a remote database server (like SQL Server) then within the database window, link to other databases (like Access). If you really want to do this, I would stay away from the adp and just use an mdb. Everything that you do within an adp can be done with an mdb, plus you get local tables, and the ability to link to several different database tables within the database window.
you just hate adp's don't cha  Jokra ;)

I use dns less connections to get info from about 4 different databases in one adp's all a matter of how you look at things

...and adp's are faster ;P
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Love 'em! LOL
nmcdermaidConnect With a Mentor Commented:
Another option is to create views in your SQL database.

These views can reference other databases on the same server, or they can use OPENROWSET to reference something completely different like a text file.

When you point your ADP at these views, they *should* behave like normal tables.

good point nmcdermaid

i use views of several databases as well... but you do have to be careful about views of multiple tables if you are trying to update or insert data...joins can be a show stopper...

rugby148Author Commented:
wow!  Lot's to think about going forward!  Thanks to all of you for some input.  Sounds like I need to give this a bit more thought than I planned before selecting a best course of action.

...unless you use an update trigger on the view....... but that is getting waaaay out of MS Access land.  :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.