473,322 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

compare two tables using this module?

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
Reply With Quote

Nov 13 '05 #1
0 4477

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Stephen | last post by:
I have to write a .Net application which can compare SQL Databases including things like: - DB structure, PK's, FK's, indexes and types of indexes i.e. should be able to detect if the same index...
5
by: Megan | last post by:
Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the...
9
by: geronimo_me | last post by:
Hi, I am atempting to compare part of a field with the whole of another field in access. Is this possible? Basically I have 2 tables with the following info: Table1 Field1 = MR.
4
by: dfs9 | last post by:
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...
9
by: VMI | last post by:
I have two tables and I want to compare these two tables with a query( ie. "select * from A where B.key = A.key") and the result will be stored in a 3rd table (table C). is this possible? If...
17
by: Mark A | last post by:
DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP 11). Using the SAMPLE database, tables EMP and EMLOYEE. In the followng stored procedure, 2 NULL columns (COMM) are...
4
by: =?Utf-8?B?TmlrbGFz?= | last post by:
Hi Look at the TODO in the code. The commented code do not work, but the code below the commented one does. How do I do to compare Types? foreach (Type typeInAssembly in typesInAssemply) {...
5
by: Edd E | last post by:
Hi, I have a database to store my analyses (Access 2002, WinXp), the basic structure is: TABLE 1 = Sample Info TABLE 2 = Analysis type 1 TABLE 3 = Analysis type 2 TABLE 4 = Analysis type 3 ...
1
by: dbanrman | last post by:
I have a module in a shopping cart program where a sellers logs in and adds product. As usual the seller logs in with an email and password which is stored in the mysql table _supplier_admins. This...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.