Open two databases to compare data and generate report

I have two MS Access DBs that I am using to generate data reports using SQL statement and ASP pages.

I am trying to use two different databases, one is an archived database and the other is the current database. The fields have exactly the same names and are TEXT fields.

How do I open two databases, to pull the data from, at the same time. Currently I am using an inc file to open ONE database and that works fine, but how do I open the 2nd database. I will include my inc file.
inc.txt
Malloy1446Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you'll need to create a second connection object. right now you have:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("fpdb/comparison.mdb") & ";"

add another connection object, then you can create a second recordset to manage the data you pull out of it:

Set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("fpdb/comparison_archive.mdb") & ";"

then you can pass the same sql to both databases if you want to compare data:

set rs = Server.CreateObject("ADODB.RecordSet")
set rs2 = Server.CreateObject("ADODB.RecordSet")

sql = "select * from table where condition = true"

set rs = objConn.Execute( sql )
set rs2 = objConn2.Execute( sql )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Malloy1446Author Commented:
I think I have both DBs open but how do you write the WHILE statement to retrieve the data.

All the results for the 2nd database field are exactly the same.

Code is attached.
WHILE.txt
0
Malloy1446Author Commented:
Eventually I will be comparing the two pieces of data and if they are different the cell will be highlighted.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
since you have 2 recordset objects, objRS2 and objRS2, populating, outputting the data in a WHILE loop is simply a matter of which object to loop through:
'-- first loop
While Not objRS.EOF
counter = counter + 1

	Response.Write "<TR><TD class='CCTable' scope='row' width='25px' valign='top'>" & counter & "</td>"
	Response.Write "<TD class='CCTable' scope='row' width='200px' valign='top'>" & objRS("City") & ", " & objRS("State") & VbCrLf
	
	Response.Write "<TD class='CCTable' font-size='44' scope='row' width='100px' valign='top'>" & objRS("VISNID") & "&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS("Grade1") & "<br>" & objRS("Title1") & "</b>&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS2("Grade1") & "<br>" & objRS2("Title1") & "</b>&nbsp;</TD></TR>" & VbCrLf



'-------------------------------------------------------------------------------


   'Advance the recordset
   objRS.MoveNext

Wend
objRS.Close
Set objRS = Nothing

'-- second loop:
counter = 1
While Not objRS2.EOF
counter = counter + 1

	Response.Write "<TR><TD class='CCTable' scope='row' width='25px' valign='top'>" & counter & "</td>"
	Response.Write "<TD class='CCTable' scope='row' width='200px' valign='top'>" & objRS2("City") & ", " & objRS2("State") & VbCrLf
	
	Response.Write "<TD class='CCTable' font-size='44' scope='row' width='100px' valign='top'>" & objRS2("VISNID") & "&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS2("Grade1") & "<br>" & objRS2("Title1") & "</b>&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS2("Grade1") & "<br>" & objRS2("Title1") & "</b>&nbsp;</TD></TR>" & VbCrLf



'-------------------------------------------------------------------------------


   'Advance the recordset
   objRS.MoveNext

Wend
objRS2.Close
Set objRS2 = Nothing

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if you only want 1 output loop, then you would do (assuming the same number of records exist in both recordsets):

While Not objRS.EOF and not objRS2.EOF
counter = counter + 1

	Response.Write "<TR><TD class='CCTable' scope='row' width='25px' valign='top'>" & counter & "</td>"
	Response.Write "<TD class='CCTable' scope='row' width='200px' valign='top'>" & objRS("City") & ", " & objRS("State") & VbCrLf
	
	Response.Write "<TD class='CCTable' font-size='44' scope='row' width='100px' valign='top'>" & objRS("VISNID") & "&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS("Grade1") & "<br>" & objRS("Title1") & "</b>&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS2("Grade1") & "<br>" & objRS2("Title1") & "</b>&nbsp;</TD></TR>" & VbCrLf



'-------------------------------------------------------------------------------


   'Advance the recordset
   objRS.MoveNext
   objRS2.MoveNext

Wend
objRS.Close
Set objRS = Nothing

Open in new window

0
Malloy1446Author Commented:
One of the issues is the number of records in the tables. The common field in each table is the ChartID. There is a table (tblGeneral) in each database that has the "master" ChartIDs with the city, state, etc. Both databases match in the tblGeneral.ChartID.

In this report I do want to include ALL ChartIDs from both databases if they appear in either of the tables (from archive.mdb or the current.mdb). Will this be possible?

I am including a screenshot of what the report will look like (except for highlighting the rows where there are changes).
report.PNG
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
will there be duplicate Chart IDs across the 2 tables, or will the be unique? if there are duplicates, do you want to show them as well?
0
Malloy1446Author Commented:
The ChartIDs are unique. There are no duplicate ChartIDs in the same table.
To make sure I am clear, one database table may have ChartIDs that are not in the
other database table and vice versa. For example ChartID=112=Chicago is the same
in both databases.

Make sense?
0
Jeffrey CoachmanMIS LiasonCommented:
Just a note here...
Typically there is little need to actually "open" two database just to pull data form the two.

You can create a third database and "Link" the tables from the original two databases, into this third database.
Then,  you only need open this one database...
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if ChartID exists in one database, CAN it also exist in the second database? if so, do you want to display it twice?
0
Malloy1446Author Commented:
No, I don't want to display twice.
Database1 is the database containing current data.
Database2 is an archive copy of the current database with the same tables and fields.

I need to do a comparison to see how things have changed between the current and the archived data so I am comparing the tblStaff.Grade1 field in database1 with the corresponding tblStaff.Grade1 field in database2.

But there will be instances where database2 has ChartIDs in the tblStaff that are not in database1. The ChartIDs will be in tblGeneral where all ChartIDs exist.

The only other option I was thinking of was to create a new table in the current database and copy the data from the archived data and do a comparison that way. I would just have to compare the records and make sure the ChartID data matches up.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
displaying the contents of each table from each database isn't difficult, I already gave you an example on how to do that. I'm just having difficulty understanding what else you need to get you what you need.
0
Malloy1446Author Commented:
Sorry I missed the objRS2.MoveNext statement. So it is now pulling data from archive.mdb and is looping through the records. But since there are some ChartIDs in one table and not in the other table it is not matching up the ChartIDs correctly.
0
Malloy1446Author Commented:
Do I need a 3rd SQL statement linking the current.mdb.tblGeneral.ChartID to the archive.mdb.tblGeneral.ChartID
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you would need to create linked tables to connect the two databases. otherwise you have no way to reference the 2 sets of data, unless you put each into its own array and do the comparison there, but that would get tricky if you're not good with arrays
0
Malloy1446Author Commented:
After reading several posts, I tried linking the tables with this statement:

Dim sql, objRS, counter
counter = 0

sql = "SELECT comparison.tblGeneral.ChartID, comparison.tblGeneral.City, comparison.tblGeneral.State, comparison.tblGeneral.VISNID, " & _
      "comparison.tblStaff.Grade1+'<bt>'+comparison.tblStaff.Title1 AS Current, " & _
      "comparison2008.tblStaff.Grade1+'<bt>'+comparison2008.tblStaff.Title1 AS 2008, " & _
      "FROM (comparison.tblGeneral FULL OUTER JOIN comparison.tblStaff ON comparison.tblGeneral.ChartID= comparison.tblStaff.ChartID) " & _
      "FULL OUTER JOIN comparison2008.tblStaff.ChartID = comparison.tblGeneral.ChartID "


      If Request.querystring("sort") = "" then
            sql = sql & "ORDER BY City,State"
      Else
            sql = sql & "ORDER BY " & Request.querystring("sort")
      End If                  
      
      Set objRS = Server.CreateObject("ADODB.Recordset")
      objRS.Open sql, objConn

But this generates an error '80004005'

Am I on the right track?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
to be honest, I don't know much on linking access tables together, as I've never had a need for it. I did a
quick google search on that error number and a possible cause could be due to permissions.do you have a lot of records for each data set (say, over 50)? if not, can you just print out each recordset individually and then eyeball the comparisons? or does it need to be side by side? if you do need to print out each section, let me know the steps you took in linking the 2 databases and I'll see if I can figure something out for you if no other experts have any ideas.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.