Solved

Combining Recordsets not through UNION

Posted on 2004-08-11
7
412 Views
Last Modified: 2012-06-27
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.

Problems:
- 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"
showConn.Open showDSNStatement
Set secondConn = Server.CreateObject("ADODB.Connection")
secondDSNStatement = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=db2.mdb"
secondconn.open secondDSNStatement
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
             found=false
             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
             else
                   secondrs.open "Select * from tblDownloads WHERE ThisCategory LIKE '%"&session("cat")&"%'Order By ThisCategory DESC, HeadCategory DESC, DispText", secondconn
             end if
                   while NOT secondrs.EOF AND found=false
                   if showthisID = secondrs.fields("ID") then
                                                             'do stuff
                                                end if
                                                secondrs.MoveNext
                  wend
            secondrs.close
            showRS.moveNext
      wend
showrs.close
showconn.close
secondconn.close


**** 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
0
Comment
Question by:dtburke
  • 4
  • 3
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 11780280
Surely it would be easier to link one database to the other and then create queries off the combined tables.

Just my 2 cents worth.
0
 

Author Comment

by:dtburke
ID: 11780426
so wait....how does that work using an access database on microsoft 2000 server...using SQL and asp and ADO...
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 11784076
>>how does that work using an access database on microsoft 2000 server<<
Can you elaborate what this means?  Do you mean your database is linked into MS SQL Server 2K?  If so, so what?  I do not see any reference to any data in the SQL Server 2K.  All your connection strings are using JET providers.
0
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 

Author Comment

by:dtburke
ID: 11785225
Obviously i dont know what im talking about...perhaps some sample code will make me understand. Or whatever process it takes to "link one database to the other"

I'm looking for a process that will be efficient.
0
 

Author Comment

by:dtburke
ID: 11787055
so using access i found that had the tables been in the same database, then the query could be...

SELECT tblDownloads.*, tblDownloads.ID, tblPub880System.CategoryOverride
FROM tblPub880System INNER JOIN tblDownloads ON tblPub880System.LinkID = tblDownloads.ID
WHERE (([tblDownloads].[ID]=[tblPub880System].[LinkID]))
ORDER BY tblDownloads.ID;

But I have two different databases...how will this work???

[the question seems to have gotten a whole lot easier]
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11789956
>>But I have two different databases...how will this work???<<
Then all you need to do is link in the table(s) from one database into the other and then use only one connection string.  Supposing you decide to use the connection string to DB1 than do the following:
1. In MS Access open DB1
2. In Tables select New
3. Select "Link Table"
4. Select DB2
5. Select the required table(s)

You can now use those tables as if they were in the one database.


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11790031
Also, please re-read the EE Guidelines regarding grading:
What's the right grade to give?
http://www.experts-exchange.com/Web/Web_Languages/ASP/help.jsp#hi73

And specifically this section:
<quote>
C: Because Experts' reliability are often judged by their grading records, many Experts would like the opportunity to clarify if you have questions about their solutions. If you have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information; or if the answers, after clarification, lack finality or do not completely address the issue presented, then a "C" grade is an option. You also have the option here of just asking Community Support to delete the question.
Remember, the Expert helping you today is probably going to be helping you next time you post a question. Give them a fair chance to earn an 'Excellent!' grade and they'll provide you with some amazing support. It's also true that a "C" is the lowest grade you can give, and the Experts know that -- so use it judiciously.
</quote>

Also, see here:
Can I get a grade changed?
http://www.experts-exchange.com/Web/Web_Languages/ASP/help.jsp#hi18
0

Featured Post

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
API Location, separating out information 10 48
JQuery Date Time picker not showing 29 129
document.getElementById not worj with 2 IDs with the same name 10 40
FileUp - Classic ASP 5 17
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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