Question Database Query help

[Kasteel7]

Hi

Hope someone can help.

I have an Access database with 3 columns. (FirstName, LastName and EndDate)

I am trying to create a button that will delete all records in the database that is older than the current date. Today()

Any help would be appreciated.

Is this possible?

Here is what I have so far:
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0; data source=C:\Database1.mdb")

Dim cmd As OleDbCommand = New OleDbCommand("SELECT LastName, FirstName, EndDate FROM Table1 WHERE EndDate < NOT SURE WHAT TO DO HERE", con)

con.Open()
Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim myDataSet As DataSet = New DataSet()
myDA.Fill(myDataSet, "Table1")
Table1DataGridView.DataSource = myDataSet.Tables("Table1").DefaultView
End Sub

 

[capedech]

Here's what I would do :

Dim StrToday as String = Format(Now, "dd/MM/yyyy")
...
Dim cmd As OleDbCommand = New OleDbCommand("DELETE FROM Table1 Where EndDate < " & StrToday & " ;", con)
....

In MySQL you need to reverse the date, from :
"dd/MM/yyyy" become "yyyy/MM/dd".
I don't know in Access, you should try them both.

 

[InertiaM]

MSDN oledbconnection

    Using AccessConnection As New OleDbConnection("Provider=Microsoft.jet.oledb.4.0; data source=C:\Database1.mdb")
        Dim command As New OleDbCommand("DELETE FROM Table1 Where EndDate < NOW()")
        command.Connection = AccessConnection
        Try
            AccessConnection.Open()
            command.ExecuteNonQuery()
        Catch ex As Exception
            Messagebox.Show(ex.Message)
        End Try
    End Using