Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    find duplicate and delete... (2000 sr 1)

    Hi, Hans...
    I have this Mdb with table TOTALE, is possible from vba for Excel controll the duplicate (unique index SERVIZIO) and delete it...?
    For example:
    SERVIZIO
    55-03002-74792-668208594
    55-03002-74792-668208594
    55-03002-74792-668208594
    delete 2 record and remain one....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: find duplicate and delete... (2000 sr 1)

    Assuming that this is for the same workbook in which you already have other code connecting to the database, and also assuming that the records with the same SERVIZIO are complete duplicates, you can use this:

    Sub DeleteDuplicates()
    Dim strCon As String
    Dim strSQL As String
    Dim cnn As Object
    ' Create and open an ADO connection to the database
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & gPROVADatabasePath & ";"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open strCon
    ' Create temporary table without duplicates
    strSQL = "SELECT DISTINCT * INTO TEMP FROM TOTALE"
    cnn.Execute strSQL
    ' Delete TOTALE
    strSQL = "DROP TABLE TOTALE"
    cnn.Execute strSQL
    ' Create new TOTALE table
    strSQL = "SELECT * INTO TOTALE FROM TEMP"
    cnn.Execute strSQL
    ' Delete temporary table
    strSQL = "DROP TABLE TEMP"
    cnn.Execute strSQL
    ' Clean up
    cnn.Close
    Set cnn = Nothing
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find duplicate and delete... (2000 sr 1)

    Hi Hans,
    Why, after this macro the index in SERVIZIO is deleted...?
    Is possibile to re-create or maintan this...
    Existis a sql commnd to make the index in SERVIZIO?

    ... and before starting this macro i have set a font in mdb arial 8 afetr the macro the format not existis?
    ... and before starting this macro i have make a manul autofitt column of the mdb afetr the macro the autifit not existis?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: find duplicate and delete... (2000 sr 1)

    I don't know why SERVIZIO would be deleted.

    The formatting disappears because this code deletes the original table and creates a new one. Try this instead, it clears and repopulates TOTALE instead of deleting it,

    ...
    ' Create temporary table without duplicates
    strSQL = "SELECT DISTINCT * INTO TEMP FROM TOTALE"
    cnn.Execute strSQL
    ' Delete records from TOTALE
    strSQL = "DELETE * FROM TOTALE"
    cnn.Execute strSQL
    ' Repopulate TOTALE
    strSQL = "INSERT INTO TOTALE SELECT * FROM TEMP"
    cnn.Execute strSQL
    ' Delete temporary table
    strSQL = "DROP TABLE TEMP"
    cnn.Execute strSQL
    ...

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: find duplicate and delete... (2000 sr 1)

    But why do you want to do this in code?

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find duplicate and delete... (2000 sr 1)

    GOOD...
    but to autofit column of fileds?
    and to change the format of fonts?
    existis a command?

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find duplicate and delete... (2000 sr 1)

    ... my question is only to leran a sql command
    Is the same if you know and send my a link of reosuce to leran a sql command naturally oriented from excel and access (DAO & ADO) and not only for access...
    Tks as usual.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: find duplicate and delete... (2000 sr 1)

    It's rather technical. See for example the threads starting at <post#= 366819>post 366819</post#> and <post#=341835>post 341835</post#>.

    I have attached an example that shows how to set the font and font size for a table (from within an Access database). You must set a reference to the Microsoft DAO 3.6 Object Library in Tools | References... for it to work.

    I do not know how to autofit columns in code.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find duplicate and delete... (2000 sr 1)

    Kiss....

  10. #10
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find duplicate and delete... (2000 sr 1)

    While I'm not an expert, I have done some basic reformatting with resizing of columns and fonts using the following code as a starting point:

    Range("B1").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("B1").Select
    With Selection.Font
    .Name = "Arial Narrow"
    .FontStyle = "Regular"
    .Size = 11
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With

    Does this help?

    DonL
    Don Liebman
    San Diego, CA 92115

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: find duplicate and delete... (2000 sr 1)

    Sal is speaking about the column widths of a table in an Access database (which he wants to control in code from Excel), not about the columns in a worksheet in Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •