Comparing 2 tables of records in Access using VB 
Author Message
 Comparing 2 tables of records in Access using VB
I am trying to compare recordsets from 2 different tables. The amount
of record sets are different on each table. One table may be shorter
than the other and vice versa. I have tried using EOF but I cannot get
it to work... any suggestions??? Thanks!
Also, the recordcount method does not seem to count all the records
in the table... why?

a newbie in VB,

Gracie

Sent via Deja.com http://www.***.com/
Before you buy.



Sun, 11 May 2003 03:00:00 GMT
 Comparing 2 tables of records in Access using VB

1. You could use a query to find records that match or don't match.
For instance:
SELECT * FROM TABLE1 WHERE TABLE1.ID IS NOT IN (SELECT TABLE2.ID)
This will give you the ID's from TABLE1 that don't exists in TABLE2 (I am
guessing that you will compare using the ID or some other similar field). If you
get any records, that will mean that the tables are different.
2. The RecordCount property is only updated in certain cursor types. In some
others, it may have a value of -1 (if your cursors are on the server). To "trust"
the RecordCount property you could use Client Cursors and Disconnected Recordsets.

Dim lrs As ADODB.Recordset
Dim lConn As ADODB.Connection
Set lConn = New ADODB.Connection
Set lrs = New ADODB.Recordset
lConn.Open "..."
lrs.CursorLocation = adUseClient
lrs.Open "SELECT ...", lConn
Set lrs.ActiveConnection = Nothing

Hope this helps
Gerardo Villeda
MCP, MCSD
http://www.netreach.net/~gvilleda

Quote:
-----Original Message-----

I am trying to compare recordsets from 2 different tables. The amount
of record sets are different on each table. One table may be shorter
than the other and vice versa. I have tried using EOF but I cannot get
it to work... any suggestions??? Thanks!
Also, the recordcount method does not seem to count all the records
in the table... why?

a newbie in VB,

Gracie

Sent via Deja.com http://www.deja.com/
Before you buy.
.



Sun, 11 May 2003 03:00:00 GMT
 Comparing 2 tables of records in Access using VB
Thanks!

The recordcount, I ended up adding one to the number of recordcount
and I left it as that.
Would that work?



Quote:

> 1. You could use a query to find records that match or don't match.=20
> For instance:
> SELECT * FROM TABLE1 WHERE TABLE1.ID IS NOT IN (SELECT TABLE2.ID)
> This will give you the ID's from TABLE1 that don't exists in TABLE2
(I =
> am=20
> guessing that you will compare using the ID or some other similar =
> field). If you=20
> get any records, that will mean that the tables are different.
> 2. The RecordCount property is only updated in certain cursor types.
In =
> some=20
> others, it may have a value of -1 (if your cursors are on the
server). =
> To "trust"=20
> the RecordCount property you could use Client Cursors and
Disconnected =
> Recordsets.

> Dim lrs As ADODB.Recordset
> Dim lConn As ADODB.Connection
> Set lConn =3D New ADODB.Connection
> Set lrs =3D New ADODB.Recordset
> lConn.Open "..."
> lrs.CursorLocation =3D adUseClient
> lrs.Open "SELECT ...", lConn
> Set lrs.ActiveConnection =3D Nothing

> Hope this helps
> Gerardo Villeda
> MCP, MCSD
> http://www.netreach.net/~gvilleda

> -----Original Message-----
> I am trying to compare recordsets from 2 different tables. The amount
> of record sets are different on each table. One table may be shorter
> than the other and vice versa. I have tried using EOF but I cannot get
> it to work... any suggestions??? Thanks!
> Also, the recordcount method does not seem to count all the records
> in the table... why?

> a newbie in VB,

> Gracie

> Sent via Deja.com http://www.deja.com/
> Before you buy.
> .

Sent via Deja.com http://www.deja.com/
Before you buy.


Mon, 12 May 2003 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Append records to MS Access table using VB

2. Comparing records in Access via VB

3. Accessing Linked Oracle Tables in Access Using ODBC in VB

4. Using VB to run an Access 7.0 Make table into Access 2

5. Updating record in OS390 DB2 table using DataGrid and ADODC with VB

6. Retriving the current Record from MS Access using VB

7. (Performance Issue) Inserting records in Access Database using VB

8. Counting records in Access DB using VB Code

9. record numbers in VB/Access 2.0 tables

10. Problem adding record to blank Access Table using DAO


 
Powered by phpBB® Forum Software