[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-04-12
8
Medium Priority
?
514 Views
Last Modified: 2013-12-05
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?

Thanks,
John
0
Comment
Question by:rugby148
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 36

Accepted Solution

by:
SidFishes earned 200 total points
ID: 13767451
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"
cnn.Open

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

etc


0
 
LVL 11

Assisted Solution

by:Jokra_the_Barbarian
Jokra_the_Barbarian earned 200 total points
ID: 13767611
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.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 13767803
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 app...it's all a matter of how you look at things

...and adp's are faster ;P
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 13767929
Love 'em! LOL
0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 200 total points
ID: 13767986
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.

0
 
LVL 36

Expert Comment

by:SidFishes
ID: 13768091
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...

0
 

Author Comment

by:rugby148
ID: 13768720
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.

Thanks,
John
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13769710
...unless you use an update trigger on the view....... but that is getting waaaay out of MS Access land.  :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question