asp : compare records in two access databases

Discussion in 'ASP General' started by Charlotte, Feb 10, 2008.

  1. Charlotte

    Charlotte Guest

    Hi,

    i've googeled to find a asp-script that can compare all the records in two
    different access databases
    the mdb's have exactly the same tables
    what i want is that (the output) all the differences comes in a html-table
    in a webpage
    can anybody help me, are give me a example ?

    thanks
    charlotte
     
    Charlotte, Feb 10, 2008
    #1
    1. Advertising

  2. Charlotte

    Evertjan. Guest

    Re: compare records in two access databases

    McKirahan wrote on 11 feb 2008 in
    > "Charlotte" <> wrote in message
    >> i've googeled to find a asp-script that can compare all the records
    >> in two different access databases
    >> the mdb's have exactly the same tables
    >> what i want is that (the output) all the differences comes in a
    >> html-table in a webpage
    >> can anybody help me, are give me a example ?

    >
    > Is a primary key defined?
    > What do you want the output to look like?
    >
    > Given this layout: "key,field1,field2"
    > where table 1 contains
    > 1,a,b
    > 2,c,d
    > and table 2 contains::
    > 1,a,b
    > 3,e,f
    > How would you want the differences reported?
    >
    > Perhaps just identifying the "key" differences?
    > table1 : 1=,2+,3-
    > table2 : 1=,2-,3+


    Interesting thought.

    That however, wouldn't be really usefull for the OP, methinks,
    as the goal would probably be to be able to correct inter table mistakes.

    =========

    Say you have two tables t1 and t2, each containing these fields:

    id,lastname,firstname,street,postcode,haircolour

    and each 100 records:

    if there are no more than single equivalent records,
    and even if you ignore the id-field differences
    each record of t1 has 99 or 100 non-aequal records in t2 to report,
    so there would be 9900 or more lines in the report plus 9900 seen from t2
    to tt1, totalling 19800 to 20000 reportlines.

    Not very useful imho.

    ==========

    Better report a subset, [a view in relational database parlance]:
    -- ignore the id field as above
    -- only test the records with the same last names
    -- order by last name, firstname, haircolour

    Now you get a reasonable amount of report lines, perhaps 5 or 60,
    but If you have a lot of records of one extrended family, the members
    will report on eachothers differences and the report lines could swell to
    2000 or more easily, if all lastnames are alike, perhaps to the 19800 of
    above.

    Also you will then loose writing mistakes of one of the corresponding
    lastname fields.

    This cannot be helped unless you develop an algorithm to define
    'nearequal' or 'sounds-like' for the last name or define another field
    that is assumed correct as key field, like a social security number in
    this person-table example, or assume two fields to be correct, like
    lastname plus postcode, but I would not think that is reasonable.

    ===========

    So Charlotte, even before you touch the sql and the niceties of inner and
    outerjoin, please reconsider what you really want.

    Read about view in relational databases:
    <http://en.wikipedia.org/wiki/View_(database)>

    and oh yes, define what database engine you are using,
    otherwize only a general sql advice can be given.

    <http://en.wikipedia.org/wiki/SQL> [ook in Nederlands]

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Feb 11, 2008
    #2
    1. Advertising

  3. Re: compare records in two access databases

    Charlotte wrote:
    > Hi,
    >
    > i've googeled to find a asp-script that can compare all the records
    > in two different access databases
    > the mdb's have exactly the same tables
    > what i want is that (the output) all the differences comes in a
    > html-table in a webpage
    > can anybody help me, are give me a example ?
    >

    I submit that the reason you have not found a generic asp script to do this
    is that this is not a task that is typically performed using asp. You have
    not defined what constitutes a "difference" between two records (do all the
    fields need to contain the same data, or just a subset of the fields), how
    to identify the records that are supposed to be the same (is there a primary
    key on the tables to identify the corresponding records?), or what the
    likely differences may be (could there be records in one table that do not
    exist in another?). And the biggest question of all: why are you duplicating
    data in two databases? This sounds like a replication process is needed.
    Access has the ability to perform replication, built into the program. You
    really should investigate this.

    Anyways, comparing the two tables is likely to involve looping through all
    the records and comparing them field-by-field. This is likely to be a
    time-consuming process that does not lend itself to the asp paradigm. You
    should consider creating some sort of scheduled off-line process that
    generates an exception table that can be easily queried and viewed by users
    via asp.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Feb 11, 2008
    #3
  4. Charlotte

    Evertjan. Guest

    Re: compare records in two access databases

    Bob Barrows [MVP] wrote on 11 feb 2008 in
    microsoft.public.inetserver.asp.general:

    > Charlotte wrote:
    >> Hi,
    >>
    >> i've googeled to find a asp-script that can compare all the records
    >> in two different access databases
    >> the mdb's have exactly the same tables
    >> what i want is that (the output) all the differences comes in a
    >> html-table in a webpage
    >> can anybody help me, are give me a example ?
    >>

    > I submit that the reason you have not found a generic asp script to do
    > this is that this is not a task that is typically performed using asp.
    > You have not defined what constitutes a "difference" between two
    > records (do all the fields need to contain the same data, or just a
    > subset of the fields), how to identify the records that are supposed
    > to be the same (is there a primary key on the tables to identify the
    > corresponding records?), or what the likely differences may be (could
    > there be records in one table that do not exist in another?). And the
    > biggest question of all: why are you duplicating data in two
    > databases? This sounds like a replication process is needed. Access
    > has the ability to perform replication, built into the program. You
    > really should investigate this.
    >
    > Anyways, comparing the two tables is likely to involve looping through
    > all the records and comparing them field-by-field. This is likely to
    > be a time-consuming process that does not lend itself to the asp
    > paradigm. You should consider creating some sort of scheduled off-line
    > process that generates an exception table that can be easily queried
    > and viewed by users via asp.


    I don't think, in the case of the OP, Bob, this is about users and about
    large databases, but about the owner wanting to do some houeshold jobs on
    her database on a semi regular basis, say once a year.

    Setting a process in motion that takes 10 minutes or perhaps an hour in
    ASP is not that bad. Being able to do that from different locations is a
    nice plus of the ASP method.



    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Feb 11, 2008
    #4
  5. Re: compare records in two access databases

    Evertjan. wrote:
    > Bob Barrows [MVP] wrote on 11 feb 2008 in
    > microsoft.public.inetserver.asp.general:
    >
    > I don't think, in the case of the OP, Bob, this is about users and
    > about large databases, but about the owner wanting to do some
    > houeshold jobs on her database on a semi regular basis, say once a
    > year.



    I'm not sure how you came to that conclusion.

    >
    > Setting a process in motion that takes 10 minutes or perhaps an hour
    > in ASP is not that bad.


    It is if a busy web server is involved.

    > Being able to do that from different
    > locations is a nice plus of the ASP method.


    Along with various other remote procedure techniques.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Feb 11, 2008
    #5
  6. Charlotte

    Charlotte Guest

    Re: compare records in two access databases


    > Hi,
    >
    > i've googeled to find a asp-script that can compare all the records in two
    > different access databases
    > the mdb's have exactly the same tables
    > what i want is that (the output) all the differences comes in a html-table
    > in a webpage
    > can anybody help me, are give me a example ?
    >
    > thanks
    > charlotte



    example:
    I have two MDB and both have exactly the same table (table1)
    table1 from MDB1 and table1 from MDB2 have exactly the same structure
    each containing these fields:
    id , lastname , firstname

    in MDB1 are 100 records
    in MDB2 are 90 records
    (records with id 91,92,93,.....100 aren't there)
    and record with id 50 has a different firstname

    so, this will be:

    50 - nothing - wrong firstname that is in MDB1
    91 - peeters - kurt
    92 - hannivoort - evertjan
    93 - deleeuw - charlotte
    ....
    ....
    100 - travolta - john

    the html-table is:

    <table>
    <tr>
    <td>50</td>
    <td></td>
    <td>wrong firstname</td>
    </tr>
    <tr>
    <td>91</td>
    <td>peeters</td>
    <td>kurt</td>
    </tr>
    <tr>
    <td>92</td>
    <td>hannivoort</td>
    <td>evertjan</td>
    </tr>
    <tr>
    <td>93</td>
    <td>deleeuw</td>
    <td>charlotte</td>
    </tr>
    ....
    ....
    <tr>
    <td>100</td>
    <td>travolta</td>
    <td>john</td>
    </tr>
    </table>

    so, the records that aren't the same in the two MDB comes in the html-table
    and also all the records witch aren't in MDB1

    I hope this is ubderstandable

    charlotte
     
    Charlotte, Feb 11, 2008
    #6
  7. Re: compare records in two access databases

    Charlotte wrote:
    >> Hi,
    >>
    >> i've googeled to find a asp-script that can compare all the records
    >> in two different access databases
    >> the mdb's have exactly the same tables
    >> what i want is that (the output) all the differences comes in a
    >> html-table in a webpage
    >> can anybody help me, are give me a example ?
    >>
    >> thanks
    >> charlotte

    >
    >
    > example:
    > I have two MDB and both have exactly the same table (table1)
    > table1 from MDB1 and table1 from MDB2 have exactly the same
    > structure each containing these fields:
    > id , lastname , firstname
    >
    > in MDB1 are 100 records
    > in MDB2 are 90 records
    > (records with id 91,92,93,.....100 aren't there)
    > and record with id 50 has a different firstname
    >


    Will mdb2 ever contain records that aren't in mdb1?

    Are the two databases on the same network (allowing a linked table to be
    used to enable an easy query solution)?


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Feb 11, 2008
    #7
  8. Charlotte

    Charlotte Guest

    Re: compare records in two access databases


    >>> Hi,
    >>>
    >>> i've googeled to find a asp-script that can compare all the records
    >>> in two different access databases
    >>> the mdb's have exactly the same tables
    >>> what i want is that (the output) all the differences comes in a
    >>> html-table in a webpage
    >>> can anybody help me, are give me a example ?
    >>>
    >>> thanks
    >>> charlotte

    >>
    >>
    >> example:
    >> I have two MDB and both have exactly the same table (table1)
    >> table1 from MDB1 and table1 from MDB2 have exactly the same
    >> structure each containing these fields:
    >> id , lastname , firstname
    >>
    >> in MDB1 are 100 records
    >> in MDB2 are 90 records
    >> (records with id 91,92,93,.....100 aren't there)
    >> and record with id 50 has a different firstname
    >>

    >
    > Will mdb2 ever contain records that aren't in mdb1?
    >
    > Are the two databases on the same network (allowing a linked table to be
    > used to enable an easy query solution)?



    the two databases are in the same LAN
    MDB1 is on webserver 1
    and MDB2 is on webserver 2

    webserver 1 is the real webserver for everyone on are LAN
    webserver 2 is my test-webserver
     
    Charlotte, Feb 11, 2008
    #8
  9. Re: compare records in two access databases

    Charlotte wrote:
    >>>> Hi,
    >>>>
    >>>> i've googeled to find a asp-script that can compare all the records
    >>>> in two different access databases
    >>>> the mdb's have exactly the same tables
    >>>> what i want is that (the output) all the differences comes in a
    >>>> html-table in a webpage
    >>>> can anybody help me, are give me a example ?
    >>>>
    >>>> thanks
    >>>> charlotte
    >>>
    >>>
    >>> example:
    >>> I have two MDB and both have exactly the same table (table1)
    >>> table1 from MDB1 and table1 from MDB2 have exactly the same
    >>> structure each containing these fields:
    >>> id , lastname , firstname
    >>>
    >>> in MDB1 are 100 records
    >>> in MDB2 are 90 records
    >>> (records with id 91,92,93,.....100 aren't there)
    >>> and record with id 50 has a different firstname
    >>>

    >>
    >> Will mdb2 ever contain records that aren't in mdb1?
    >>
    >> Are the two databases on the same network (allowing a linked table
    >> to be used to enable an easy query solution)?

    >
    >
    > the two databases are in the same LAN
    > MDB1 is on webserver 1
    > and MDB2 is on webserver 2
    >
    > webserver 1 is the real webserver for everyone on are LAN
    > webserver 2 is my test-webserver


    Do you know how to create a linked table in Access? That will make this
    relatively easy since you will be able to create a query using a full outer
    join to generate the records for your report.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Feb 11, 2008
    #9
  10. Charlotte

    Charlotte Guest

    Re: compare records in two access databases


    >>>>> Hi,
    >>>>>
    >>>>> i've googeled to find a asp-script that can compare all the records
    >>>>> in two different access databases
    >>>>> the mdb's have exactly the same tables
    >>>>> what i want is that (the output) all the differences comes in a
    >>>>> html-table in a webpage
    >>>>> can anybody help me, are give me a example ?
    >>>>>
    >>>>> thanks
    >>>>> charlotte
    >>>>
    >>>>
    >>>> example:
    >>>> I have two MDB and both have exactly the same table (table1)
    >>>> table1 from MDB1 and table1 from MDB2 have exactly the same
    >>>> structure each containing these fields:
    >>>> id , lastname , firstname
    >>>>
    >>>> in MDB1 are 100 records
    >>>> in MDB2 are 90 records
    >>>> (records with id 91,92,93,.....100 aren't there)
    >>>> and record with id 50 has a different firstname
    >>>>
    >>>
    >>> Will mdb2 ever contain records that aren't in mdb1?
    >>>
    >>> Are the two databases on the same network (allowing a linked table
    >>> to be used to enable an easy query solution)?

    >>
    >>
    >> the two databases are in the same LAN
    >> MDB1 is on webserver 1
    >> and MDB2 is on webserver 2
    >>
    >> webserver 1 is the real webserver for everyone on are LAN
    >> webserver 2 is my test-webserver

    >
    > Do you know how to create a linked table in Access? That will make this
    > relatively easy since you will be able to create a query using a full
    > outer join to generate the records for your report.



    yes, I know how to make linked tables, but that's not our intention
    our intention is an ASP-page with an html-table like I wrote above

    thanks
     
    Charlotte, Feb 11, 2008
    #10
  11. Re: compare records in two access databases

    Charlotte wrote:
    >
    >>>> Are the two databases on the same network (allowing a linked table
    >>>> to be used to enable an easy query solution)?
    >>>
    >>>
    >>> the two databases are in the same LAN
    >>> MDB1 is on webserver 1
    >>> and MDB2 is on webserver 2
    >>>
    >>> webserver 1 is the real webserver for everyone on are LAN
    >>> webserver 2 is my test-webserver

    >>
    >> Do you know how to create a linked table in Access? That will make
    >> this relatively easy since you will be able to create a query using
    >> a full outer join to generate the records for your report.

    >
    >
    > yes, I know how to make linked tables, but that's not our intention
    > our intention is an ASP-page with an html-table like I wrote above
    >

    I was not intending it to be the end result. I was intending to use it to
    facilitate creating a query to generate that html-table via an ASP page as
    you desire. I'm not clear why you are rejecting this out-of-hand. With both
    tables effectively in the same database, a simple query using an outer join
    can generate the records you wish to show in your html table. Otherwise, you
    will be forced to open connections to both databases, retrieve recordsets
    from both tables. loop through them, comparing the fields by hand: a grossly
    inefficient process compared to running a query to generate the records.

    Think about it again. If there's a valid reason for rejecting the linked
    table approach, we will help you develop the inefficient
    recordset-comparison approach.

    Let me ask you this: would you reject programatically creating a temporary
    database on the fly, programmatically creating linked tables in that
    database to the two tables you wish to compare, allowing a query to be run
    to generate your html-table?

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Feb 11, 2008
    #11
  12. Charlotte

    Charlotte Guest

    Re: compare records in two access databases


    >>>>> Are the two databases on the same network (allowing a linked table
    >>>>> to be used to enable an easy query solution)?
    >>>>
    >>>>
    >>>> the two databases are in the same LAN
    >>>> MDB1 is on webserver 1
    >>>> and MDB2 is on webserver 2
    >>>>
    >>>> webserver 1 is the real webserver for everyone on are LAN
    >>>> webserver 2 is my test-webserver
    >>>
    >>> Do you know how to create a linked table in Access? That will make
    >>> this relatively easy since you will be able to create a query using
    >>> a full outer join to generate the records for your report.

    >>
    >>
    >> yes, I know how to make linked tables, but that's not our intention
    >> our intention is an ASP-page with an html-table like I wrote above
    >>

    > I was not intending it to be the end result. I was intending to use it to
    > facilitate creating a query to generate that html-table via an ASP page as
    > you desire. I'm not clear why you are rejecting this out-of-hand. With
    > both tables effectively in the same database, a simple query using an
    > outer join can generate the records you wish to show in your html table.
    > Otherwise, you will be forced to open connections to both databases,
    > retrieve recordsets from both tables. loop through them, comparing the
    > fields by hand: a grossly inefficient process compared to running a query
    > to generate the records.
    >
    > Think about it again. If there's a valid reason for rejecting the linked
    > table approach, we will help you develop the inefficient
    > recordset-comparison approach.




    > Let me ask you this: would you reject programatically creating a temporary
    > database on the fly, programmatically creating linked tables in that
    > database to the two tables you wish to compare, allowing a query to be run
    > to generate your html-table?


    no problem
    the only thing we want is an asp-page with a html-table
    and in that html-table are the differences between MDB1 and MDB2

    but remember:
    mdb1 is on webserver1 and mdb2 is on webserver2
    (in the same LAN)
     
    Charlotte, Feb 11, 2008
    #12
  13. Charlotte

    Evertjan. Guest

    Re: compare records in two access databases

    Bob Barrows [MVP] wrote on 11 feb 2008 in
    microsoft.public.inetserver.asp.general:

    > Evertjan. wrote:
    >> Bob Barrows [MVP] wrote on 11 feb 2008 in
    >> microsoft.public.inetserver.asp.general:
    >>
    >> I don't think, in the case of the OP, Bob, this is about users and
    >> about large databases, but about the owner wanting to do some
    >> houeshold jobs on her database on a semi regular basis, say once a
    >> year.

    >
    >
    > I'm not sure how you came to that conclusion.


    So why don't we as k her, instead of assuming either way?

    Charlotte, what say?

    But in general I think to much weight is given to the buzy server
    problem, while I think quiet servers [say 1000 visits or less a day] are
    in the fast majority server number wize on the web, though perhaps not
    total web visits number wise. [Example: Google counts for one serverpark
    in the first and maybe millions in the second count. But I am not Google
    and neither is Charlotte. Don't know about you though ;-)]

    >> Setting a process in motion that takes 10 minutes or perhaps an hour
    >> in ASP is not that bad.

    >
    > It is if a busy web server is involved.


    I specified the case that it was not. But let's not assume further.

    >> Being able to do that from different
    >> locations is a nice plus of the ASP method.

    >
    > Along with various other remote procedure techniques.


    Perhaps, but not in the majority case of the quiet server.
    Why not just use ASP there?
    Why learn to use other techniques.



    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Feb 11, 2008
    #13
  14. Charlotte

    Charlotte Guest

    Re: compare records in two access databases

    >>> I don't think, in the case of the OP, Bob, this is about users and
    >>> about large databases, but about the owner wanting to do some
    >>> houeshold jobs on her database on a semi regular basis, say once a
    >>> year.

    >>
    >>
    >> I'm not sure how you came to that conclusion.

    >
    > So why don't we as k her, instead of assuming either way?
    >
    > Charlotte, what say?
    >
    > But in general I think to much weight is given to the buzy server
    > problem, while I think quiet servers [say 1000 visits or less a day] are
    > in the fast majority server number wize on the web, though perhaps not
    > total web visits number wise. [Example: Google counts for one serverpark
    > in the first and maybe millions in the second count. But I am not Google
    > and neither is Charlotte. Don't know about you though ;-)]
    >
    >>> Setting a process in motion that takes 10 minutes or perhaps an hour
    >>> in ASP is not that bad.

    >>
    >> It is if a busy web server is involved.

    >
    > I specified the case that it was not. But let's not assume further.
    >
    >>> Being able to do that from different
    >>> locations is a nice plus of the ASP method.

    >>
    >> Along with various other remote procedure techniques.

    >
    > Perhaps, but not in the majority case of the quiet server.
    > Why not just use ASP there?
    > Why learn to use other techniques.



    our webserver (LAN) have 300 - 500 visits (or less) a day
    so, it's NOT a busy webserver
     
    Charlotte, Feb 11, 2008
    #14
  15. Charlotte

    Charlotte Guest

    Re: compare records in two access databases

    so, ....... is there someone who can (will) give me an example

    thanks
    charlotte
     
    Charlotte, Feb 11, 2008
    #15
  16. Re: compare records in two access databases

    Charlotte wrote:
    > so, ....... is there someone who can (will) give me an example
    >
    > thanks
    > charlotte


    Patience ... i'm working on it. :)
    It's not something I have ever done, so I have nothing to copy and paste.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Feb 11, 2008
    #16
  17. Re: compare records in two access databases

    Charlotte wrote:
    >>>>>> Are the two databases on the same network (allowing a linked
    >>>>>> table to be used to enable an easy query solution)?
    >>>>>
    >>>>>
    >>>>> the two databases are in the same LAN
    >>>>> MDB1 is on webserver 1
    >>>>> and MDB2 is on webserver 2
    >>>>>
    >>>>> webserver 1 is the real webserver for everyone on are LAN
    >>>>> webserver 2 is my test-webserver
    >>>>
    >>>> Do you know how to create a linked table in Access? That will make
    >>>> this relatively easy since you will be able to create a query using
    >>>> a full outer join to generate the records for your report.
    >>>
    >>>
    >>> yes, I know how to make linked tables, but that's not our intention
    >>> our intention is an ASP-page with an html-table like I wrote above
    >>>

    >> I was not intending it to be the end result. I was intending to use
    >> it to facilitate creating a query to generate that html-table via an
    >> ASP page as you desire. I'm not clear why you are rejecting this
    >> out-of-hand. With both tables effectively in the same database, a
    >> simple query using an outer join can generate the records you wish
    >> to show in your html table. Otherwise, you will be forced to open
    >> connections to both databases, retrieve recordsets from both tables.
    >> loop through them, comparing the fields by hand: a grossly
    >> inefficient process compared to running a query to generate the
    >> records. Think about it again. If there's a valid reason for rejecting
    >> the
    >> linked table approach, we will help you develop the inefficient
    >> recordset-comparison approach.

    >
    >
    >
    >> Let me ask you this: would you reject programatically creating a
    >> temporary database on the fly, programmatically creating linked
    >> tables in that database to the two tables you wish to compare,
    >> allowing a query to be run to generate your html-table?

    >
    > no problem
    > the only thing we want is an asp-page with a html-table
    > and in that html-table are the differences between MDB1 and MDB2
    >
    > but remember:
    > mdb1 is on webserver1 and mdb2 is on webserver2
    > (in the same LAN)


    And comparedbs.asp will be run on webserver1? Or do you intend to have a
    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>","&nbsp;")
    rs.close: set rs = nothing
    html =left(html , len(html ) - 8)
    end if
    cn.close: set cn = nothing
    html = html & "</table>"
    Response.Write html
    %>


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Feb 11, 2008
    #17
  18. Charlotte

    Jeff Dillon Guest

    Re: compare records in two access databases

    "Charlotte" <> wrote in message
    news:pk4sj.49543$...
    >
    > so, ....... is there someone who can (will) give me an example
    > thanks
    > charlotte
    >
    >


    How often do you have to do this? I'm wondering "why" an ASP page?

    Jeff
     
    Jeff Dillon, Feb 11, 2008
    #18
  19. Re: compare records in two access databases

    Jeff Dillon wrote:
    > "Charlotte" <> wrote in message
    > news:pk4sj.49543$...
    >>
    >> so, ....... is there someone who can (will) give me an example
    >> thanks
    >> charlotte
    >>
    >>

    >
    > How often do you have to do this? I'm wondering "why" an ASP page?
    >
    > Jeff


    Oh no, you're not allowed to wonder that ... ;-)

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Feb 12, 2008
    #19
  20. Charlotte

    Charlotte Guest

    Re: compare records in two access databases

    > And comparedbs.asp will be run on webserver1? Or do you intend to have a
    > 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>","&nbsp;")
    > rs.close: set rs = nothing
    > html =left(html , len(html ) - 8)
    > end if
    > cn.close: set cn = nothing
    > html = html & "</table>"
    > Response.Write html
    > %>



    thanks bob'ke
     
    Charlotte, Feb 12, 2008
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luke Airig
    Replies:
    0
    Views:
    795
    Luke Airig
    Dec 31, 2003
  2. Ken North
    Replies:
    0
    Views:
    606
    Ken North
    Jul 14, 2005
  3. GenxLogic
    Replies:
    3
    Views:
    1,289
    andrewmcdonagh
    Dec 6, 2006
  4. Willem

    Search two databases with ASP?

    Willem, Jun 28, 2005, in forum: ASP General
    Replies:
    6
    Views:
    183
  5. Replies:
    3
    Views:
    666
    Anthony Jones
    Nov 2, 2006
Loading...

Share This Page