I have 2 databases:
db1 - Contains tables unique to a webpage name. ie tblIndex, tblPage1, tblPage2...Fields ID, LinkID, CategoryOverride
db2 - Contains one large table...ID, URL, Text, Category
I need to accomplish:
- Write ASP script in conjunction with asp to list the links referred to in a db1 that pulls the information from db2.
- db2 ID's are autonumber, meaning there is no organization to the list
- when reading from db1, there is no means to organize it (since its just a list of numbers)
- my script i have now is exceptionally slow. Though it was slower before when i had db2 checking with db1 instead of the reverse. (doing the prior solved problems 1 and 2, but time was a big compromise in that case)
Heres what i have so far:
showDSNStatement = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=db1.mdb"
Set secondConn = Server.CreateObject("ADODB.Connection")
secondDSNStatement = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=db2.mdb"
Set secondRS = Server.CreateObject("ADODB.Recordset")
Set showrs = Server.CreateObject("ADODB.Recordset")
showRS.open "Select Distinct LinkID,CategoryOverride from "&thispagemenutable&" order by LinkID", showconn
while NOT showRS.EOF
showthisID = showrs.fields("LinkID")
if session("cat") = "all" OR session("cat") = "" then
secondrs.open "Select * from tblDownloads Order By ThisCategory DESC, HeadCategory DESC, DispText", secondconn
secondrs.open "Select * from tblDownloads WHERE ThisCategory LIKE '%"&session("cat")&"%'Order By ThisCategory DESC, HeadCategory DESC, DispText", secondconn
while NOT secondrs.EOF AND found=false
if showthisID = secondrs.fields("ID") then
**** I REALIZE THAT I DO NOT HAVE TO LOOP THROUGH ALL OF DB2. The only reason its in this format is because i USED to have to loop through DB2 multiple times (since it was ordered) and find a match with DB1. It would be a magnitude greater if i could somehow set up the recordsets then create a third recordset that is Ordered and is where LinkID = ID