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 DeleteDuplicate Records(strTabl eName 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(strTa bleName)
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.OpenR ecordset(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 4 4978
"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
DeleteDuplicate Records("****")
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
You mean the general rule that you should understand what code does
before you use it *still* applies?! Darn! pi********@hotm ail.com wrote in
news:11******** **************@ o13g2000cwo.goo glegroups.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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Maur |
last post by:
Hi all,
I have 2 tables say t_OLD and t_NEW. The new has
corrections for audit purposes.
They are identical in all respects (i.e. new is a copy of
old and then changes are made to t_new)
I would like a quick way to cycle through all the the fields
in each table and compare the values to see if there are
|
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 new database. For instance,
her old database has a table with Band Info in it. Her new database
also has a table with Band Info in it but slightly different. I was
wondering if there was an easy way to compare the fields from similar
tables in...
|
by: Vincent |
last post by:
has any one seen a program to compare mdbs'. I have ran into a few of
them, but none seem to really do that job.
Basically what I need to do is, take 2 access mdb's and check the
differences between the 2. i am talking about tables, forms, queries,
the whole ball of wax. Most of the programs jus do tables, that is
the easy part.
Also I was wondering how you would compate text fiels progammatically
and kick back a report using...
|
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 necessary, I can create the schema
for the 3rd table (C) since it'll be exactly like table A. But I'm more
interested in being able to store the resulting set into another table.
Thanks.
|
by: Mark |
last post by:
by m.r.davies
I have 2 tables on seperate Db's (and servers)
I want to use a datareader on the first table to pick the booking ref, and then use that booking ref to query the 2nd DB
when i have the correct row in the 2nd DB I want to compare each field in the 2 tables to see if they match
Every time i try and think how i end up with a while loop for the reader on table1 and a while loop for the reader on table 2
any ideas how i'm going...
| |
by: Shaw |
last post by:
Our database is constantly updated (input data) from another DB, and
sometimes it crashes our ASP.NET applications. My boss told me to write a DB
utility app to check DB and make sure all apps are still running.
The first step of few checks is to compare current DB tables with
yesterday’s tables. I store yesterday’s tables at a XML file (DB is
maintained by another guy, and certainly they don’t want me to make back copy
for few...
|
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 selected into 2
different SP variables and compared for equal. They are both NULL, but do
not compare as equal. When the Not NULL columns (SALARY) are compared, they
do compare as equal.
|
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
....where each table stores results of a different type of analysis
|
by: chazzy69 |
last post by:
Ok heres what im trying to achive i am constantly updating the information in a table but know i what know if the data im replacing it with is new, i.e. whether or not i already had the data in the table.
So my method for this is to first copy the information to be updated into another table (the 2nd table), then allow the information in the first table to updated (via deleting the current information and re-entering it).
Then i what to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |