Access query doesn't run on workbook close

Question

I'm trying to run an Access delete query when I close my Excel workbook. I want the DELETE query to delete any record that is prior to today's date in the "ResDate" field. Below is the code I have written. The workbook closes but the records remain in the database (TR table).

I thought this would work but am striking out so far. Any suggestions on getting this to work would be greatly appreciated. Thanks for the help...…….

Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\MyDatabase\TRC.mdb;Persist Security Info=False"

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Set Connection = New ADODB.Connection
    Connection.ConnectionString = ConnectionString
    Connection.Open

    VSQL = "DELETE * FROM [TR] WHERE [TR].ResDate < " & Date

    Set RecSet1 = Connection.Execute(VSQL, dbrows, adCmdText)

    Connection.Close

    Set RecSet1 = Nothing

End Sub

@HansUp...….I use the Immediate window and the sql looks like this: DELETE * FROM [TrainingRoom] WHERE [TrainingRoom].ResDate < 11/8/2019 — Shaves, Nov 08 '19 at 16:23
Access SQL interprets `11/8/2019` as 11 divided by 8 divided by 2019. If you want it interpreted as a date instead, include `#` delimiters like this ... `#11/8/2019#` But if your intention is the date will always be today's date, just use the `Date()` function --- no delimiters or formatting issues to deal with. — HansUp, Nov 08 '19 at 16:35

Erik A · Answer 1 · 2019-11-08T16:29:05.283

ALWAYS USE PARAMETERS!

When you do VSQL = "DELETE * FROM [TR] WHERE [TR].ResDate < " & Date, depending on your locale, that can result in several issues.

For example, DELETE * FROM [TR] WHERE [TR].ResDate < 2019-11-08, and 2019 minus 11 minus 08 is equal to 2000 and CDate(2000) is 1905-06-22, so this is a valid query, but probably won't delete what you want to delete.

In an alternate locale, it might be 11/08/2019, and 11 divided by 8 divided by 2019 is approximately 0 and CDATE(0) is 1899-12-30.

Instead, pick and choose from How do I use parameters in VBA in the different contexts in Microsoft Access?, for example:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Set Connection = New ADODB.Connection
    Connection.ConnectionString = ConnectionString
    Connection.Open

    VSQL = "DELETE * FROM [TR] WHERE [TR].ResDate < " & Date
    With CreateObject("ADODB.Command")
         Set .ActiveConnection = Connection
         .CommandText = "DELETE * FROM [TR] WHERE [TR].ResDate < ?"
         Set RecSet1 = .Execute(, Array(Date))
    End With
    Connection.Close

    Set RecSet1 = Nothing

End Sub

...….thanks for your help and the information provided. This has a limited release so I don't have to worry about locale but I learned something with your answer.... — Shaves, Nov 08 '19 at 17:19

score 0 · Accepted Answer · answered Nov 08 '19 at 16:31

Use the Access Date() function in your SQL statement, instead of inserting the date as text into the statement.

Include Option Explicit in your module's Declarations section and run Debug->Compile from the VB Editor's main menu. Fix the first compile error Access complains about, and then Debug->Compile again. Repeat until no more errors.

Opening a recordset based on a DELETE query is not useful because a DELETE does not return records. Simply execute your query instead.

Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\MyDatabase\TRC.mdb;Persist Security Info=False"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim cn As ADODB.Connection
    Dim VSQL As String
    Dim dbrows As Long
    Set cn = New ADODB.Connection
    cn.ConnectionString = ConnectionString
    cn.Open

    VSQL = "DELETE * FROM [TR] WHERE [TR].ResDate < Date()"

    cn.Execute VSQL, dbrows, adCmdText

    cn.Close
End Sub

...……...Thanks for your time today. this worked just like I was expecting it to. It deletes the old records (prior to today) just like I needed it to. Thanks again for your help — Shaves, Nov 08 '19 at 17:17

Access query doesn't run on workbook close

2 Answers2