Remove oldest duplicates

T

Tubbzuk

Registered User.
Local time
Today, 22:09
Joined
Dec 21, 2012
Messages
37
Hi,

I am writing an access database that will receive data from an excel spreadsheet when the user hits a button.
THis button may be hit more than once and therefore duplicate records would be entered.

What I want to do is write macro that will run when the database is opened that will remove all entries that are older leaving the earliest upload in the database.

Its either that or i add a new column to the excel spreadsheet that will increase the count by one every time it is uploaded and therefore delete the oldest one of those.

Any help is greatly appreciated,

Regards,

Alan
 
Can you clarify

Code: 
will remove all entries that are older leaving the earliest upload in the database.
earlier and older are the same thing:)

Also, are you saying your excel spreadsheet has different rows identities each time you load i.e.

first load includes balance for accounts 1, 2 and 10
second load includes balances for accounts 3, 5 and 22
third load includes balances for accounts 1, 3 and 34

so no records are removed for the first and second load, but on the third you want to remove the records relating to accounts 1 and 3?
 
Hi,

yes i want the earliest upload in.
The entries i wish to have uploaded are on the attahced spreadsheet.
any line in the spreadsheet that has no product entered in column D will not be written to the database. I have added a column in the code to enter the time it was uploaded so i have a start to where i can look for the latest upload.


Thanks for your help.

Regards,

Alan
 

Attachments

Sorry, you are still not making yourself clear.

yes i want the earliest upload in.
Click to expand...
So you want to keep your earliest upload? I thought you wanted to delete them?

Product F10799 and others appears more than once in the spreadsheet - do you want all the entries or only one? and if so, which one?

What does the table you are importing to look like? - do you want to import the calculated fields such as percentages.

You also need to clarify how to define a record to be deleted - is it product or product and team or product, team, line and shift for example.

It might be helpful if you post what you want your table to look like both before and after updating with the attached information.

The spreadsheet is not in an ideal format because of the lines at the top summarising what the report is - so you'll need to use a range unless you can change the spreadsheet to exclude the first five lines. If you use a range - does the spreadsheet always have the same number of rows? or does this vary?
 
CJ_London said:
The spreadsheet is not in an ideal format because of the lines at the top summarising what the report is - so you'll need to use a range unless you can change the spreadsheet to exclude the first five lines. If you use a range - does the spreadsheet always have the same number of rows? or does this vary?
Click to expand...
I've got one of these with a 'header row' (stupid PeopleSoft). A2:M30000 works well in Access 2010/2013, and only pulls in the rows with actual data (12-14k) rather than having 18k blank records at the end.
 
Hi, thanks for all the replies.

the code i have for exporting my data to a database is this
Code: 
Sub ExportToDB()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, row As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
            "Data Source=G:\div\MAN\Production\Shift Reports\Production\shift report data.accdb;"
Dim fi As String
fi = ActiveWorkbook.Name
Dim DTE As String
DTE = Now
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "Batches", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    row = 7    ' the start row in the worksheet
    
    Do While Not IsEmpty(Worksheets("Batch Summary").Range("A" & row))
        
        With rs
            If Range("D" & row).Value <> "" Then
            
            .AddNew    ' create a new record
            .Fields("FileName") = fi
            .Fields("Line") = Worksheets("Batch Summary").Range("A" & row).Value
            .Fields("Shift") = Worksheets("Batch Summary").Range("B" & row).Value
            .Fields("Team") = Worksheets("Batch Summary").Range("C" & row).Value
            .Fields("Product Code") = Worksheets("Batch Summary").Range("D" & row).Value
            .Fields("Volume") = Worksheets("Batch Summary").Range("E" & row).Value
            .Fields("Waste KGS") = (Worksheets("Batch Summary").Range("F" & row).Value)
            
            .Fields("Waste %") = Worksheets("Batch Summary").Range("G" & row).Value
            .Fields("Waste £") = Worksheets("Batch Summary").Range("H" & row).Value
            .Fields("Hours Run") = Worksheets("Batch Summary").Range("I" & row).Value
            .Fields("Target Vol") = Worksheets("Batch Summary").Range("P" & row).Value
            .Fields("Performance") = Worksheets("Batch Summary").Range("Q" & row).Value
            .Fields("Total TBGS") = Worksheets("Batch Summary").Range("T" & row).Value
            .Fields("KGS Vol") = Worksheets("Batch Summary").Range("U" & row).Value
            .Fields("Week") = Worksheets("Batch Summary").Range("V" & row).Value
            .Fields("ProdDate") = Worksheets("Batch Summary").Range("W" & row).Value
            .Fields("Upload Time") = DTE
            
            .Update
            Else
        End If
        
        End With
        row = row + 1
        
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
'Windows(fi).Close savechanges:=True
End Sub

This will then write the necessary fields to the table.
what i need to do is keep the latest entry.

so i need to keep each block of entries i.e. if i have the same filename but 2 different upload times - all of the earliest upload times need to be deleted t=from the table so there is only one block of entries in the database.

I have attached a screenshot of the table so you can see how it looks.


Hope this makes more sense. I am rubbish at explaining things!

Thanks for the help,

Regards,

Alan
 

Attachments

  • Batches Table.jpg
    Batches Table.jpg
    100.2 KB · Views: 146
I was working on the understanding you were pulling data from excel to Access, not pushing it. But this still leaves the BIG UNANSWERED question - what do you mean by
what i need to do is keep the latest entry
Click to expand...

Unless you are able to answer this question I do not think I can help
 
hi,
the entries i need to keep are the ones with the later date and time.

for example if someone runs the macro and sends a set of data to the database and it has the time of 12/12/2013 20:52.
then someone runs it again the following morning and it has the time of 13/12/2013 08:12.
then when opening the database all of the entries with 12/12/2013 20:52 will be deleted leaving one complete set of data from that specfic file.

Hope that covers it. I know i am poor at explaining.
Thanks for sticking with me on this ;-)

Regards,

Alan
 
OK - so now you've answered that question,

Please answer this one

You also need to clarify how to define a record to be deleted - is it product or product and team or product, team, line and shift for example.

and also this one

Product F10799 and others appears more than once in the spreadsheet - do you want all the entries or only one? and if so, which one?

And just to confirm, in your first post you said
What I want to do is write macro that will run when the database is opened that will remove all entries that are older leaving the earliest upload in the database
Click to expand...
Is the bit in red supposed to say 'leaving the latest upload'
 
cool.

say for example i have a block of entries for filename 12-12-2013.xlsx upload time 12/12/2013 20:52 and anoher block of entries for filename 12-12-2013.xlsx upload time 13/12/2013 06:22
I need the macro to delete all the entries for 12/12/2013 20:52

Hope that covers it,

regards,

Alan
 
OK so it is alll records to be deleted - if that is the case, then I would simply modify your code in excel to delete all the records before you insert the new ones - after the rs.open line and before the row=7 line
 
the problem i have though is that the code i posted before is on the spreadsheet. it is part of a subroutine that e-mails the report out.

They will not all be using the database as it is soemthing different i am trying out.

I wanted to put the check for the duplicate entries on the opening of the database so that it cleaned it before it was viewed by management.

I am uncertain of the code needed to do this or how i would go about putting it at the beginning of the original code.

I did some VB stuff around 7 years ago and have literally only just started picking stuff back up over the last 6 months so my knowledge is fairly limited at the moment.

Regards,

Alan
 
Hi,

been looking into it some more and am thinking i want to add something like this but where it loops through all the records in the database first deleting everthing that matches the filename first.
Code: 
Dim fi As String
fi = ActiveWorkbook.Name
Dim DTE As String
DTE = Now
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "Batches", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    'loop for deleting previous uploads
    
    DELETE FROM "Batches"
    WHERE Filename = fi
    
    'end of deletion loop
    row = 7    ' the start row in the worksheet
    
    Do While Not IsEmpty(Worksheets("Batch Summary").Range("A" & row))
        
        With rs
            If Range("D" & row).Value <> "" Then
            
            .AddNew    ' create a new record
            .Fields("FileName") = fi
            .Fields("Line") = Worksheets("Batch Summary").Range("A" & row).Value
            .Fields("Shift") = Worksheets("Batch Summary")

i have the basic in there which comes up with a syntax error.
Not sure how to put this into something that will work...

Regards,

Alan
 
i think the above problem occurs form it not understanding the code as an sql statement. maybe (flying blind here! lol)

Changed it to this but now getting a different error about a missing operator
Code: 
    Dim sql As String
    
Dim fi As String
fi = ActiveWorkbook.Name
Dim DTE As String
DTE = Now
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "Batches", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    'loop for deleting previous uploads
    
    sql = "DELETE FROM Batches.filename WHERE Filename = " & fi & ";"
    cn.Execute (sql)
    
    'end of deletion loop
    row = 7    ' the start row in the worksheet
    
    Do While Not IsEmpty(Worksheets("Batch Summary").Range("A" & row))
        
        With rs
            If Range("D" & row).Value <> "" Then
            
            .AddNew    ' create a new record
            .Fields("FileName") = fi
            .Fields("Line") = Worksheets("Batch Summary").Range("A" & row).Value

regards,

Alan
 
Ok,

I have added some quotes and moved the rs.open line till after the deletion criteria as i got a couple of different errors.

It is now running ok but not actually deleting any records. i am just gettign all the new records added with none of the old ones being deleted first.

my full subroutine is now...

Code: 
Sub ExportToDB()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, row As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
            "Data Source=G:\div\MAN\Production\Shift Reports\Production\shift report data.accdb;"
    Dim sql As String
    
Dim fi As String
fi = ActiveWorkbook.Name
Dim DTE As String
DTE = Now
    ' open a recordset
   
    'loop for deleting previous uploads
    
    sql = "DELETE FROM Batches WHERE Filename = "" & fi & "";"
    cn.Execute (sql)
    
    'end of deletion loop
 Set rs = New ADODB.Recordset
    rs.Open "Batches", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    row = 7    ' the start row in the worksheet
    
    Do While Not IsEmpty(Worksheets("Batch Summary").Range("A" & row))
        
        With rs
            If Range("D" & row).Value <> "" Then
            
            .AddNew    ' create a new record
            .Fields("FileName") = fi
            .Fields("Line") = Worksheets("Batch Summary").Range("A" & row).Value
            .Fields("Shift") = Worksheets("Batch Summary").Range("B" & row).Value
            .Fields("Team") = Worksheets("Batch Summary").Range("C" & row).Value
            .Fields("Product Code") = Worksheets("Batch Summary").Range("D" & row).Value
            .Fields("Volume") = Worksheets("Batch Summary").Range("E" & row).Value
            .Fields("Waste KGS") = (Worksheets("Batch Summary").Range("F" & row).Value)
            
            .Fields("Waste %") = Worksheets("Batch Summary").Range("G" & row).Value
            .Fields("Waste £") = Worksheets("Batch Summary").Range("H" & row).Value
            .Fields("Hours Run") = Worksheets("Batch Summary").Range("I" & row).Value
            .Fields("Target Vol") = Worksheets("Batch Summary").Range("P" & row).Value
            .Fields("Performance") = Worksheets("Batch Summary").Range("Q" & row).Value
            .Fields("Total TBGS") = Worksheets("Batch Summary").Range("T" & row).Value
            .Fields("KGS Vol") = Worksheets("Batch Summary").Range("U" & row).Value
            .Fields("Week") = Worksheets("Batch Summary").Range("V" & row).Value
            .Fields("ProdDate") = Worksheets("Batch Summary").Range("W" & row).Value
            .Fields("Upload Time") = DTE
            
            .Update
            Else
        End If
        
        End With
        row = row + 1
        
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
'Windows(fi).Close savechanges:=True
End Sub

thanks for your help,

Regards,

Alan
 
OK so if you are basing this on the date then the sql code you need is as follows (names based on what has been provided):

In your Access Db create a new query called 'DeleteOld' and paste this sql into it and save it
DELETE * FROM Batches WHERE [Upload Time]<>(SELECT Max([Upload Time]) FROM Batches as Tmp)

Create a macro called autoexec and have it run the query
 
Whilst doing my last post I see you have added some more posts - if you want to base it on filename rather than upload time simply change upload time to filename - fortunately your filenames have the right structure to determine the 'max' value
 
I am at a bit of a loss doing that. i do not know where to post the code in.

would we need to do much to alter the code to look for every record in the database with that filename and delete it before the upload?

Regards,
Alan
 
I'm really struggling to understand what you require. You started off referring to products, then moved to upload times, now file names.

which bit of this do you not understand?

In your Access Db create a new query called 'DeleteOld' and paste this sql into it and save it
DELETE * FROM Batches WHERE [FileName]<>(SELECT Max([FileName]) FROM Batches as Tmp)

Create a macro called autoexec and have it run the query
Click to expand...
 
You must log in or register to reply here.

Similar threads

D
Solved How to quickly duplicate Tables & associated subforms within a main form
2 3 4
Replies
71
Views
2,425
dibblermail
D
A
Rename Tables Programmatically in MsAccess 2003
Replies
5
Views
419
Mark_
M
J
Solved SQL problem
2
Replies
29
Views
642
isladogs
isladogs
H
How to insert some data from Excel spreadsheet and the other data from Access combo boxes
Replies
6
Views
689
Pat Hartman
P
P
Long-running delete of duplicates
2
Replies
25
Views
1,567
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom