Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ASP Connections w/ Multiple MDB files

Posted on 2004-07-30
33
Medium Priority
?
386 Views
Last Modified: 2010-05-19
OK, I'm sure somebody's gonna make me feel dumb here but I really need to get straightened out on this.

I'm building an application, ASP over Access, and I've run into a problem.  There's actually multiple MDB files that run the web pages and on some of the pages I need to make queries to more than one of them at a time.  I've figured out that I need to have another connection to do this and I understand that.  My issue is that I had a real simple way of making a connection on each page and now I'm not sure how to modify it to do what I need.

On each ASP Page, I included accessConn.asp that sets the values for everything

**********************************
<%
Set conn = Server.CreateObject("ADODB.Connection")  
driver = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
configConn = driver & Server.MapPath("\db\config.mdb")
userConn =  driver & Server.MapPath("db\users.mdb")
miscConn = driver & Server.MapPath("\db\misc.mdb")
orderConn = driver & Server.MapPath("\db\orders.mdb")
productConn = driver & Server.MapPath("\db\products.mdb")
%>
***********************************

Then, when I need to create a recordset I just do something like this

*************************************************
<%
set rsProductType = server.createObject("adodb.recordset")
sqlProductType = "select productTypeName, productTypeID from tbl_productType order by productTypeID"
conn.Open productConn
rsProductType.open sqlProductType, conn, 2, 3
%>
*************************************************

The problem lies in the fact that conn is already opened so I have to create another connection if I need another recordset opened from a different file.  

Am I making sense at all??  How do I really want to do this??
0
Comment
Question by:1cell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 8
  • +3
33 Comments
 
LVL 31

Expert Comment

by:alorentz
ID: 11679337
You may just need to create a different connection object for each MDB.

0
 
LVL 31

Expert Comment

by:alorentz
ID: 11679352
OR, check state and open and close whn needed:

<%
set rsProductType = server.createObject("adodb.recordset")
sqlProductType = "select productTypeName, productTypeID from tbl_productType order by productTypeID"
if conn.state = 1 then
   conn.close
end if
conn.Open productConn  '<-------CHANGE THIS VARIABLE WHEN NEEDED
rsProductType.open sqlProductType, conn, 2, 3
%>
0
 
LVL 58

Assisted Solution

by:Gary
Gary earned 1200 total points
ID: 11679357
Or unless you're using the connection to do inserts/updates without a recordset then reference the variable you assign the connection to and dont do conn.open...


' conn.Open productConn  ** gone **
rsProductType.open sqlProductType, productConn , 2, 3
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Accepted Solution

by:
Clif earned 800 total points
ID: 11679378
You have two options.

1. You will have to create a connection object for each database.
2. Pick an Access database (or create a whole new one) and add Linked tables from the other databases.
0
 
LVL 6

Author Comment

by:1cell
ID: 11679414
alorents, I do change that variable when I want to connect to a different database.  the problem is that I can't re-use conn on a page that already has it opened.  I do need to create another connection.... I think.

GaryC123, some of these will just be to display data but there will also be quite a few pages that do inserts and updates.  If I make the changes you prescribe, will the conn object not be open if I need to open another recordset?  Do I need to modify my accessConn.asp page?
0
 
LVL 58

Expert Comment

by:Gary
ID: 11679451
If you're using the recordset for doing inserts/updates then you don't need to open a connection using conn.open productconn for any connection, just do it as in my example above obviously appending with the correct variable name for the database.
If you do inserts etc like this conn.execute("insert into ...") then you do need to open the connection.
0
 
LVL 6

Author Comment

by:1cell
ID: 11679456
"Pick an Access database (or create a whole new one) and add Linked tables from the other databases"

hmmmm...  I didn't think about that.  what's involved in that?  Sorry, I haven't really done it that way before.  I try to use SQL as much as possible but this project has to be Access.
0
 
LVL 6

Author Comment

by:1cell
ID: 11679495
gary, I'll just be doing inserts and updates through rs.addNew and rs.Update.

So you're saying I can eliminate

conn.open productConn

and just go to

rs.Open sql, productConn,2,3

so it would be like this???

rs = server.createObject("adodb.recordset")
sql = "select * from table"
rs.open sql, productConn,2,3

and I don't need to change :

<%
Set conn = Server.CreateObject("ADODB.Connection")  
driver = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
configConn = driver & Server.MapPath("\db\config.mdb")
userConn =  driver & Server.MapPath("db\users.mdb")
miscConn = driver & Server.MapPath("\db\misc.mdb")
orderConn = driver & Server.MapPath("\db\orders.mdb")
productConn = driver & Server.MapPath("\db\products.mdb")
%>

??????
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11679503
>>I do need to create another connection.... I think.

Correct, that was my first answer...
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11679510
>>So you're saying I can eliminate

No, that's the same thing as you have...just written differently
0
 
LVL 10

Expert Comment

by:Clif
ID: 11679511
Open up an Access database, select the "Tables" Object.  Right click and select "Link Tables".  You will then be given the option of selecting the database you want to link the tables from, then given the choice of which table(s) you want to link.
0
 
LVL 6

Author Comment

by:1cell
ID: 11679527
re: linking tables, if I do link the tables then I can just connect to one file and reference tables from the other file the same as if they were local?

0
 
LVL 58

Expert Comment

by:Gary
ID: 11679538
Yep thats it.  You're not referencing a connection to a db instead you're including the connection string (productConn etc ) as part of the recordset opening.
0
 
LVL 6

Author Comment

by:1cell
ID: 11679567
is there any performance hit expected for linking tables???

"No, that's the same thing as you have...just written differently"

I'm confused a bit.  Does there not need to be an ADODB connection if there's an ADODB recordset????
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11679599
Yes, you need both... Gary, I'm guessing, is referencing the need for multiple connection object, like I stated earlier.

Never needed to use Clif's answer, if that works, use that.
0
 
LVL 58

Expert Comment

by:Gary
ID: 11679600
I'm confused, who are you asking?
You don't need an ADODB connection if you use my example.
0
 
LVL 10

Expert Comment

by:Clif
ID: 11679630
No appreciable performance hit with Access to Access linking.  The only possible drawback is that tables with identicale names will have 1 appended.  So, if you already have a Table1, linking a Table1 from another database will cause it to be named Table11 in the hosting database.

If your tables are all named different, you should have no problem.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11679645
>>You don't need an ADODB connection if you use my example.

What, what, what?  That's a new idea here....connect to a DB without a connection.  Amazing stuff!
0
 
LVL 10

Expert Comment

by:Clif
ID: 11679653
GaryC123,
You said, "You don't need an ADODB connection if you use my example."

Your example from above:
' conn.Open productConn  ** gone **
rsProductType.open sqlProductType, productConn , 2, 3

What is productConn?
0
 
LVL 58

Expert Comment

by:Gary
ID: 11679681
productconn is set in his connection include along with all the db connections.

accessConn.asp
<%
Set conn = Server.CreateObject("ADODB.Connection")  
driver = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
configConn = driver & Server.MapPath("\db\config.mdb")
userConn =  driver & Server.MapPath("db\users.mdb")
miscConn = driver & Server.MapPath("\db\misc.mdb")
orderConn = driver & Server.MapPath("\db\orders.mdb")
productConn = driver & Server.MapPath("\db\products.mdb")
%>
0
 
LVL 58

Expert Comment

by:Gary
ID: 11679703
alorentz, lol
I mean you dont need to go
<%
productConn = driver & Server.MapPath("\db\products.mdb")
set conn=server.createobject("adodb.connection")
conn.open productConn
rs.open sql,conn
%>

You can just go
<%
productConn = driver & Server.MapPath("\db\products.mdb")
rs.open sql,productConn
%>
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11679748
Sheeweee, that's good!  But, pretty sure recordset needs active connection, not connection string.

0
 
LVL 6

Author Comment

by:1cell
ID: 11679791
Thanks for all the feedback on this so far.  I'll give you all some points as all of the information is helpful to me.  I'm going for a quick lunch then I'm going to come back and try some of this.  But since we are looking at two solutions here, I'm curious what you guys see as possible positives or negatives to each method.  Again, thanks for all the feedback.  I really appreciate it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11679802
Nope. From BOL:

ActiveConnection
Optional. Either a Variant that evaluates to a valid Connection object variable name, or a String that contains ConnectionString parameters.
0
 
LVL 58

Expert Comment

by:Gary
ID: 11679803
Recordsets don't need an open connection, including the connection string in place of an open connection works fine.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11679807
If Clif's method works use that, the server will maintain the link connections so not much resource problem.  

There would be resource issues possibly with opening multiple connections on every page.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11679812
And my response was to alorentz: "But, pretty sure recordset needs active connection, not connection string."
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11679831
Thanks AC, sometimes when we do something the same way for so long, we think it's the only way! :)
0
 
LVL 6

Author Comment

by:1cell
ID: 11680215
It will take me a little while to test these ideas and see which I prefer.  It'll probably be tomorrow before I can report back (unless I've got questions! lol).
0
 
LVL 13

Expert Comment

by:flow79
ID: 11680305
the method i use to do it is: (and I included sample queries just to show you how to use it)

<%
Dim oConnA, oConnB, DRIVER
Dim SQLA, SQLB

Set oConnA=Server.CreateObject("ADODB.Connection")
oConnA.Open "DRIVER={Microsoft Access Driver (*.mdb)};" &_
   "DBQ=c:\inetpub\wwwroot\db1.mdb;"

Set oConnB=Server.CreateObject("ADODB.Connection")
oConnB.Open "DRIVER={Microsoft Access Driver (*.mdb)};" &_
   "DBQ=c:\inetpub\wwwroot\db2.mdb;"

SQLA="insert into tblConnection1 (field1) VALUES (field1value) "
Call oConnA.execute(SQLA)

SQLB="insert into tblConnection2 (field2) VALUES (field2value) "
Call oConnB.execute(SQLB)

%>
0
 
LVL 6

Author Comment

by:1cell
ID: 11680565
gary, clean up for your solution would just be:

rs.Close
set rs = nothing

?????
0
 
LVL 58

Expert Comment

by:Gary
ID: 11680597
@1cell
Yep
0
 
LVL 6

Author Comment

by:1cell
ID: 11698609
Thanks for the help, everybody.  I'm going with Gary's solution strictly because I prefer to have everything in the code.  I couldn't find a performance difference either way and even if a minor one exists, these pages will never see enough traffic to make it significant.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 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