By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

compare tables

P: n/a
In the article "Delete Duplicate Records From Access Tables"
By Danny Lesandrini writes the following:

This final suggestion is the most flexible and accurate. Given any
table, it generates a recordset of appropriate fields (excluding memo
and binary image fields) and dynamically loops through the fields'
collection to perform the recordset compare.

In this example, two recordsets are used, one being a clone of the
other. When a duplicate is found, it is deleted from the first
recordset and the next record is examined. When it has determined that
the records do not match, both recordsets are advanced. The code for
this solution is shown below. Copy and paste it into an Access module
and try it.

Sub DeleteDuplicateRecords(strTableName As String)
' Deletes exact duplicates from the specified table.
' No user confirmation is required. Use with caution.
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
Dim varBookmark As Variant

Set tdf = DBEngine(0)(0).TableDefs(strTableName)
strSQL = "SELECT * FROM " & strTableName & " ORDER BY "
' Build a sort string to make sure duplicate records are
' adjacent. Can't sort on OLE or Memo fields,though.
For Each fld In tdf.Fields
If (fld.Type <> dbMemo) And _
(fld.Type
<> dbLongBinary) Then
strSQL = strSQL & fld.Name & ", "
End If
Next fld
' Remove the extra comma and space from the SQL
strSQL = Left(strSQL, Len(strSQL) - 2)
Set tdf = Nothing

Set rst = CurrentDb.OpenRecordset(strSQL)
Set rst2 = rst.Clone
rst.MoveNext
Do Until rst.EOF
varBookmark = rst.Bookmark
For Each fld In rst.Fields
If fld.Value <> rst2.Fields(fld.Name).Value Then
GoTo NextRecord
End If
Next fld
rst.Delete
GoTo SkipBookmark
NextRecord:
rst2.Bookmark = varBookmark
SkipBookmark:
rst.MoveNext
Loop
End Sub

How do I put this into a access project so I can compare two tables?

Thanks

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"dfs9" <da****@mmfcpa.com> wrote
How do I put this into a access project
so I can compare two tables?


You do not -- it does not COMPARE TWO TABLES, it DELETES DUPLICATE RECORDS
from ONE TABLE. And, just as a further caution, the comment in the code
says: No user confirmation is required. Use with caution.

The quoted article says copy and paste into a module (in the database where
the table resides). You'd run it by opening the Immediate Window and typing

DeleteDuplicateRecords("****")

where you replace **** with the name of your table.

But PLEASE, do not run it expecting to compare two tables.

Larry Linson
Microsoft Access MVP
Nov 13 '05 #2

P: n/a
You mean the general rule that you should understand what code does
before you use it *still* applies?! Darn!

Nov 13 '05 #3

P: n/a
pi********@hotmail.com wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:
You mean the general rule that you should understand what code
does before you use it *still* applies?! Darn!


Well, at *some* point you should understand the code.

But I do have code that I wrote some time ago that I run and don't
understand, because I've forgotten how it worked.

But since I wrote it, I did actually understand it.

Once.

Way back when. . .

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #4

P: n/a
Well, at least it will be beneficial to read the description and comments
that describe what the code does. I fear that anyone who does not even read
the description may well execute it on the only, or production, copy of the
database.

Larry Linson
Microsoft Access MVP

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.