third website for this?
Anyways, the general approach in the asp page will be to use ADOX to
create the temp database and add the linked tables and stored query. Then,
it will use ADO to run the query and getstring to generate the html table.
To this end, in whatever web server you are planning to run this in,
create a folder and grant Modify permissions for the folder to the user(s)
that will be running this page (I am assuming you will disable Anonymous
access and use Integrated security). The temp database will be created in
this folder - let's assume you call the folder "tempdb". You will also
need to create filesystem shares for the folders containing the two
databases and grant Modify rights for those folders to the users that will
run this asp page.
Here is the code for comparedbs.asp (untested):
<%
Option Explicit
dim cat, newDB, tbl, MasterDB, SlaveDB, cmd, sql
MasterDB=\\server2name\sharename\mdb2.mdb
SlaveDB = \\server1name\sharename\mdb1.mdb
newDB = server.MapPath("tempdb/temp.mdb")
newDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
newDB
Set cat = CreateObject("ADOX.Catalog")
cat.Create newDB
cat.ActiveConnection = newDB
Set tbl=CreateObject("ADOX.Table")
tbl.Name = "Master"
Set tbl.ParentCatalog = cat
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Datasource") = MasterDB
tbl.Properties("Jet OLEDB:Remote Table Name") = "mdb2"
cat.Tables.Append tbl
Set tbl=CreateObject("ADOX.Table")
tbl.Name = "Slave"
Set tbl.ParentCatalog = cat
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Datasource") = SlaveDB
tbl.Properties("Jet OLEDB:Remote Table Name") = "mdb1"
cat.Tables.Append tbl
set tbl=nothing
set cmd=createobject("adodb.command")
sql="SELECT s.ID, " & _
"IIF(s.LastName=m.LastName,'',s.LastName) AS DiffLastName," & _
"IIF( s.FirstName=m.FirstName,'',s.FirstName) As DiffFirstName " & _
"FROM Master AS m RIGHT JOIN Slave AS s ON m.ID = s.ID " & _
"WHERE (((s.LastName)<>[m].[LastName])) OR" & _
" (((s.FirstName)<>[m].[FirstName])) OR (((m.ID) Is Null))"
cat.Views.Append "CompareSlave2Master", cmd
set cat=nothing
set cmd=nothing
html = "<table border=""1""><tr><th>ID</th>" & _
"<th>Last Name</th>" & _
"<th>First Name</th></tr>"
dim cn, cmd, rs
dim html
set cn=createobject("adodb.connection")
cn.open newDB
set rs = createobject("adodb.recordset")
cn.CompareSlave2Master rs
if not rs.eof then
html = html & "<tr><td>" & _
rs.GetString(,,"</td><td>","</td></tr><tr><td>"," ")
rs.close: set rs = nothing
html =left(html , len(html ) - 8)
end if
cn.close: set cn = nothing
html = html & "</table>"
Response.Write html
%>
