Solved

ASP Connections w/ Multiple MDB files

Posted on 2004-07-30
33
372 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
  • 9
  • 9
  • 8
  • +3
33 Comments
 
LVL 31

Expert Comment

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

0
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
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 300 total points
Comment Utility
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
 
LVL 10

Accepted Solution

by:
Clif earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Comment Utility
>>I do need to create another connection.... I think.

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

Expert Comment

by:alorentz
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I'm confused, who are you asking?
You don't need an ADODB connection if you use my example.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 10

Expert Comment

by:Clif
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Sheeweee, that's good!  But, pretty sure recordset needs active connection, not connection string.

0
 
LVL 6

Author Comment

by:1cell
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
And my response was to alorentz: "But, pretty sure recordset needs active connection, not connection string."
0
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
gary, clean up for your solution would just be:

rs.Close
set rs = nothing

?????
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
@1cell
Yep
0
 
LVL 6

Author Comment

by:1cell
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now