Eliminate Duplications

  • Thread starter Thread starter T Liden
  • Start date Start date
T

T Liden

I have set up an append query for the user, but I want to make sure that
they haven't run the query before and keep them from duplicating the data.
How do I keep the user from duplicating the data. When the user runs the
append query, I need to have access check to make sure the dates in the
query are not already in the table. What would be the most efficient way of
doing this??

Thanks in advance for your time!!

Tim
 
In developed applications, I avoid giving the users direct access to either
tables or queries, but use forms and code to check and run any such. If you
are just creating queries for some user, then you have no way to assure that
the proper sequence has been followed.

There are a number of ways to prevent duplicate records, but the most
reliable is to create an index in the table, and check that no duplicates
are allowed. That will prevent their being two records with the same unique
index/id, but it won't necessarily be very "user friendly" when the user
does enter a duplicate.

A "developed application" with updates being done from forms gives you the
ability to use VBA code to check and be a bit more user friendly, but that
doesn't appear to be what you have.

Please followup and/or clarify here in the newsgroup, not by e-mail. Thanks.

Larry Linson
Microsoft Access MVP
 
Thanks Larry, but the problem with the index with no duplicates is that I
have several of the same dates in the table and in the query itself.
Therefore, that won't work. And your suggestion of using forms is great,
but first of all, I don't want the user to look at the data, I simply want
them to run an append query, which I will do with a button on a form. What
I need is some VBA code to make sure that they are not duplicating the
dates.



The user is running a append query using a certain date range, usually a
week, and then appends that data to the table. The problem is that the user
forgets that they ran the date range already and runs the query again using
the same date range, duplicating the data in the table.



Thanks!!

Tim
 
I don't want the user to look at the data, I simply want
them to run an append query, which I will do with a button on a form.
What I need is some VBA code to make sure that they are not
duplicating the dates.
Click to expand...

SELECT COUNT(MyDateField)
FROM MyDestinationTable
WHERE MyDateField IN
( SELECT ALL MyDateField
FROM YourSourceTable IN YourSourceDatabase.mdb
);


If it returns more than zero, then there are duplicates in the destination.
Things get very complicated if there are nulls in the MyDateField but if
it's a key field, then that is presumably unlikely.

Note too, that comparing DateTime values is fraught unless you _know_ that
there are no TimeValues in them: 03/04/2004 00:00 is not equal to
03/04/2005 13:33, for example.

Hope that helps


Tim F
 
Thanks Tim!!

I am assuming that I can enter that directly into a Sub Procedure. How do I
create a line of code that says if no records were returned then continue on
with the VBA procedure?

Tim
 
Actually, I see you can not enter that directly into a sub procedure. So do
I create a new query that runs that statement and then use some code to
check and see if it produced more than zero records and then if not run the
append query else give the users a message box that states they are
appending duplicate records and exit sub?

How would I write a sub procedure to do that?

Thanks!!
Tim
 
So do
I create a new query that runs that statement and then use some code
to check and see if it produced more than zero records and then if not
run the append query else give the users a message box that states
they are appending duplicate records and exit sub?
Click to expand...

You can either

(a) build it in vba code and then .Execute it:

strSQL = "SELECT COUNT(....."

' this is DAO code, but ADO is similar-ish
Set db = CurrentDB()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)
' don't need to check for EOF/ BOF because COUNT()
' always returns exactly one row.
' just check for zero value: all you need is yes or no
' remember that Fields is a zero-based collection
fThereAreRecords = (rs.Fields(0).Value > 0)
rs.Close

' Then act on the result
If fThereAreRecords Then
' don't do the import
MsgBox "Already Done"

Else
' import them
DoImport

End If


or (b) create a querydef with the SQL code, test it, and then use it in a
similar fashion. This will be a little quicker because the database search
strategy does not have to be worked out anew every time.

Set rs = db.QueryDefs("CheckIfAlreadyThere").OpenRecordset( _
dbOpenSnapshot, dbForwardOnly)

fThereAreRecords = (rs.Fields(0).Value > 0)
' etc etc as above

Hope that helps


Tim F
 
Thanks Tim!!

I will try it.

Tim
Tim Ferguson said:
You can either

(a) build it in vba code and then .Execute it:

strSQL = "SELECT COUNT(....."

' this is DAO code, but ADO is similar-ish
Set db = CurrentDB()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)
' don't need to check for EOF/ BOF because COUNT()
' always returns exactly one row.
' just check for zero value: all you need is yes or no
' remember that Fields is a zero-based collection
fThereAreRecords = (rs.Fields(0).Value > 0)
rs.Close

' Then act on the result
If fThereAreRecords Then
' don't do the import
MsgBox "Already Done"

Else
' import them
DoImport

End If


or (b) create a querydef with the SQL code, test it, and then use it in a
similar fashion. This will be a little quicker because the database search
strategy does not have to be worked out anew every time.

Set rs = db.QueryDefs("CheckIfAlreadyThere").OpenRecordset( _
dbOpenSnapshot, dbForwardOnly)

fThereAreRecords = (rs.Fields(0).Value > 0)
' etc etc as above

Hope that helps


Tim F
Click to expand...
 
Back
Top