«

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

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)

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] 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. 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"

> 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 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"

>>> 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 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"

>>>>> 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