Hi,

I have a question that might seem strange.

I would like to have an action button on my main form, that gives me the opportunity to delete data from the database. When I press the action button I would like to have an option group to choose from which table I would like to delete the data. The tables would be 'tblOpvAanvragenTD', 'tblOpvAlarmen', 'tblOpvKassatickets', 'tblOpvTelnr', 'tblOpvTT' and 'tblOpvWagens'. Some of these table or linked by their primary key to another table, so it would be cascade deletion for some of them.
If I could also have an option in this group to delete data from all tables at once instead of repeating the operation for each table, it would be nice. Their should be an OK and Cancel button on this pop-up form.
Of course, the deletion should be confirmed with a password (on another pop-up form as a dialog box with a OK and Cancel button), to make sure only the allowed persons can delete data. This deletion should be operated by date (all data before the given data have to be deleted). So I would like to have a date field on this 'deletion menu'.

Can this be done or not? The relations on the table does not authorise deleting at this moment. Do I have to change this value then in the relationship window?

If this can not be done, or if it is to stupid to do, is it possible to archive these data somewhere, so the data would not be in the database anymore? Is it than easy to look up some data in this archive?

Does anyone understands this? Or does anyone have a better idea, because some data will probably not be usefull anymore after 2 or 3 years... that's why I would like to delete them.

You can create queries to export data after a certain age. Without too much thought, export the data to spreadsheets or an archive database, then delete the data in the real time tables. You could use date range selectors for this.

I do this on SQL server, but I use a DTS package to move the data out to an Access archive, then I use delete queries to remove it from the server. You could do the same sort of thing.

What you will need is to create queries with the data you want to move out first. Use a paramter query that takes a date range from your form. The action button will do the following:

Run the query or queries to pull all the data limited by date.
Export the query results to a database or spreadsheets.
Set Warnings to False
Delete the data just pulled from the main tables using a copy of the parameter query that was used to pull the data to export.
Set Warnings to True

Your delete queries will need to delete the data from the tables in the proper order so you don't have to cascade deletes. If you want to do that, then you can cascade from the top down.

What part do you need help on?


mmcdonal

The code I have so far is:

Private Sub cmdOK_Click()
    ' make sure a valid date is entered to export the data
    If IsNull(Datum) Then
        MsgBox "Er moet een geldige datum ingevuld worden." _
            & vbCrLf & "Gelieve opnieuw te proberen.", vbExclamation, _
            "Ongeldige invoer."
        Exit Sub
    End If
    ' confirmation with password to archieve the data
    ' HERE I WOULD LIKE TO HAVE A COMMAND THAT ASKS THE USER TO ENTER A PASSWORD BEFORE THE _
    OPERATION OF ARCHIVING AND DELETING BEGINS
    ' IF ANYONE CAN TELL ME HOW I CAN DO THIS IT WOULD BE REALLY GREAT (the password can be _
    shown in this code, I do not have to create a seperate table for this password...)
    ' export the data from the queries to different excell-sheets
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvaanvragentd", _
    "C:\Documents and Settings\Vincent\Desktop\Archieven aanvragen TD.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvalarmen", _
    "C:\Documents and Settings\Vincent\Desktop\Archieven alarmen.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvkassatickets", _
    "C:\Documents and Settings\Vincent\Desktop\Archieven kassatickets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvtelnr", _
    "C:\Documents and Settings\Vincent\Desktop\Archieven telefoonnummers.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvtt", _
    "C:\Documents and Settings\Vincent\Desktop\Archieven Transtypes.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvwagens", _
    "C:\Documents and Settings\Vincent\Desktop\Archieven wagens.xls"
    ' HERE I WOULD LIKE THE DATA TO BE DELETED FROM THE DATABASE. THE DATA SHOULD BE DELETED _
    UNTILL THE DATE THAT WAS ENTERED IN THE DATE FIELD ON THE FORM.
    ' SO IF THE DATE WAS 31/12/2005? ALL DATA BEFORE OR EQUAL TO THIS DATA SHOULD BE DELETED _
    IN THE TABLES
    ' close the pop-up form for archiving the data
    DoCmd.Close acForm, "frmParameterQryArchiverenGegevens"
    ' telling the user the archiving procedure is finished
    MsgBox "Het archiveren en verwijderen" & vbCrLf & "van de gegevens is voltooid.", _
    vbInformation, "Archiveren beëindigd"
    'reopen the main menu
    DoCmd.OpenForm "frmHoofdmenu", acNormal, acEdit
End Sub

I have tested this already and it works. What I would like is to add the password confirmation and the deletion of the data in the tables. I really need some help on these 2 items, because I really don't know how to do those thing. A detailed information would really help me here.

thanks

The first thing I would do is to create a DELETE query that does what you want it to do with deleting data. You may need to create several delete queries to get all the data.

Then in the Date field of the DELETE query, put a parameter something like:

>=[Forms]![frmYourFormName].[dtYourDateField]

This will go out to the date selected by the user and use that for the delete query.

Then do:

DoCmd.SetWarnings False
DoCmd.OpenQuery 'qryDELETEYourDeleteQuery"
DoCmd.SetWarnings True


As for the password issue - for the On Click event of the delete button, do:

Dim sPass As String
Dim sPass1 As String

sPass1 = "YourPassword"

sPass = InputBox("Please enter the password to delete this data:")

If sPass <> sPass1 Then
   MsgBox "You do not have permission to delete this data." & vbCrLf & _
   "Please see the database administrator."
   Else

   'Your Delete actions

End If

How close are we?



mmcdonal

Actually, that might be <= the date. I always have to try this one to get it right. I am ambidextrous and this effects my sense of direction. Lame. =)

mmcdonal

ok, I changed the code to:

Private Sub cmdOK_Click()

    ' make sure a valid date is entered to export the data

    If IsNull(Datum) Then
        MsgBox "Er moet een geldige datum ingevuld worden." _
            & vbCrLf & "Gelieve opnieuw te proberen.", vbExclamation, _
            "Ongeldige invoer."
        Exit Sub
    End If

    ' confirmation with password to archieve the data

    Dim sPass As String
    Dim sPass1 As String

    sPass1 = "IKEA Anderlecht"

    sPass = InputBox("Gelieve het paswoord in te voeren om" & vbCrLf & _
            "de gegevens te kunnen archiveren:")

    If sPass <> sPass1 Then
        MsgBox "Het ingegeven paswoord komt niet overeen." & vbCrLf & "De gegevens zijn niet gearchiveerd!" & vbCrLf & _
        "Incorrect paswoord."
    Else
        ' export the data from the queries to different excell-sheets
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvaanvragentd", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven aanvragen TD.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvalarmen", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven alarmen.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvkassatickets", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven kassatickets.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvtelnr", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven telefoonnummers.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvtt", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven Transtypes.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvwagens", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven wagens.xls"

    ' HERE I WOULD LIKE THE DATA TO BE DELETED FROM THE DATABASE. THE DATA SHOULD BE DELETED _
    UNTILL THE DATE THAT WAS ENTERED IN THE DATE FIELD ON THE FORM.
    ' SO IF THE DATE WAS 31/12/2005? ALL DATA BEFORE OR EQUAL TO THIS DATA SHOULD BE DELETED _
    IN THE TABLES

    ' close the pop-up form for archiving the data
    DoCmd.Close acForm, "frmParameterQryArchiverenGegevens"

    ' telling the user the archiving procedure is finished
    MsgBox "Het archiveren en verwijderen" & vbCrLf & "van de gegevens is voltooid.", _
    vbInformation, "Archiveren beëindigd"

    'reopen the main menu
    DoCmd.OpenForm "frmHoofdmenu", acNormal, acEdit


    End If



End Sub

The password I enter is not 'case sensitive' a=A and A=a. Is it possible to change this, so A=A but A and a are different?

Everything works fine untill here. How do I create the remove query? Do I have to put the update cascade deleting to yes in the relationship window or not?

I am not sure about the password issue. Perhaps another poster can comment on regular expressions. Does it make a difference with that password? If it were "PaSsW0rd" it might.

Anyway, to create a delete query, just create a normal query that pulls all the data from each record that you will want to delete. Then make sure the paramters (criteria) pull from your form controls. You will want one to pull from the date control, and one to pull from the other parameter control (you mentioned already.) Check the query results and make sure it is pulling what you want.

Then in query design view, select Query > Delete Query. Be forewarned that once you select this property, if you run the query, it will delete the records according to the paramters chosen, so be careful with it. Only open it in design view from now on. Run it with your code, after you set the warnings to false, and then reset the warnings to true after your run code.

You will want to cascade deletes, or create seperate delete queries that delete the downstream data BEFORE you run this query.

Does this help?



mmcdonal

Everything is ready to work but I get an error to debug. I have put it in red.

Private Sub cmdok_Click()

    ' make sure a valid date is entered to export the data

    If IsNull(Datum) Then
        MsgBox "Er moet een geldige datum ingevuld worden." _
            & vbCrLf & "Gelieve opnieuw te proberen.", vbExclamation, _
            "Ongeldige invoer."
        Exit Sub
    End If

    ' confirmation with password to archieve the data

    Dim sPass As String
    Dim sPass1 As String

    sPass1 = "IKEA Anderlecht"

    sPass = InputBox("Gelieve het paswoord in te voeren om" & vbCrLf & _
            "de gegevens te kunnen archiveren:" & vbCrLf & "Het paswoord is IKEA Anderlecht")

    If sPass <> sPass1 Then
        MsgBox "Het ingegeven paswoord komt niet overeen." & vbCrLf & "De gegevens zijn niet gearchiveerd!" & vbCrLf & _
        "Foutief paswoord."
    Else
        ' export the data from the queries to different excell-sheets
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvaanvragentd", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven aanvragen TD.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvalarmen", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven alarmen.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvkassatickets", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven kassatickets.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvtelnr", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven telefoonnummers.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvtt", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven Transtypes.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryarchiverenopvwagens", _
        "C:\Documents and Settings\Vincent\Desktop\Archieven wagens.xls"

        ' deleting the data by using and running a delete query
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryVerwijderenOpvAanvragenTD"
        DoCmd.OpenQuery "qryVerwijderenOpvAlarmen"
        DoCmd.OpenQuery "qryVerwijderenOpvTelnr"
        DoCmd.OpenQuery "qryVerwijderenOpvWagens"
        DoCmd.OpenQuery "qryVerwijderenOpvKassaticketsDetail"
        DoCmd.OpenQuery "qryVerwijderenOpvKassatickets"
        DoCmd.OpenQuery "qryVerwijderenOpvTTDetail"
        DoCmd.OpenQuery "qryVerwijderenopvTT"
        DoCmd.SetWarnings True

        ' close the pop-up form for archiving the data
        DoCmd.Close acForm, "frmParameterQryArchiverenGegevens"

        ' telling the user the archiving procedure is finished
        MsgBox "Het archiveren en verwijderen van de gegevens is voltooid." & vbCrLf & _
            "De gegevens werden gearchiveerd in excell-bestanden en" & vbCrLf & _
            "werden op het bureaublad geplaatst.", vbInformation, "Archiveren beëindigd"

        'reopen the main menu
        DoCmd.OpenForm "frmHoofdmenu", acNormal, acEdit

    End If

End Sub

The name of the query I'm using is correct. Is it possible I get this error because I run all the query one after the other at the same time? If not, where should I change the code to make it work completely?

thanks for helping so far!

Can you post the SQL code of the offending query?

mmcdonal

DELETE tblOpvAlarmen.Volgnummer, tblOpvAlarmen.Datum, tblOpvAlarmen.Uur, tblOpvAlarmen.[Code alarm], tblCodesAlarmen.[Omschrijving alarm], tblOpvAlarmen.[Naam personeelslid], tblOpvAlarmen.[Naam agent], tblOpvAlarmen.[Reden alarm]
FROM tblCodesAlarmen RIGHT JOIN tblOpvAlarmen ON tblCodesAlarmen.[Code alarm] = tblOpvAlarmen.[Code alarm]
WHERE (((tblOpvAlarmen.Datum)<=[Forms]![frmParameterQryArchiverenGegevens]![Datum]));