Writing/Reading Dates when using MS Excel VBA to MS Access Database

Question

I'm developing an app in MS Excel (2019) to write/read a date to MS Access (2019, same suite) via MS Excel VBA code.
No matter how I enter and format the date, all I get back from MS Access is gibberish.

Everything I found online has missed the point in providing a simple explanation as to what the problem is.

The code provided is stripped down to the elements required to get at the heart of it all.

Option Explicit

Public Conn                 As New ADODB.connection
Public rs                   As New ADODB.Recordset
Public sconnect             As String
Public Const DATABASE_LOC   As String = "C:\Users\Robert\Documents\ASAP\db1"
Public Const DATABASE_NAME  As String = "Database1.accdb"

'MS Access datatable "TheDate" is a ShortDate
'My system date format is 'standard U.S. -  mm/dd/yyyy
'The cell from which result is read is in Date format
'
'Tried:
'ADate = CDate("12/29/2022")
'ADate = #12/29/2022#
'ADate = 'Format("12/29/2022", "mm/dd/yy")
'
'ALL result in: Result IN:  ? Date IN: 12/29/2022    - Access table field: 12:00:18 AM
'Result Out: ? Date OUT: 12:00:18 AM  - Cell "B1" (Date format) cell value: 1/0/1900  12:00:18 AM

Sub TestDateInAndOut()
     TestDateIntoDB
     TestDateOutOfDB
End Sub

Sub TestDateIntoDB()

    Dim SQLString As StringDim ADate As Date

   'option 1
   ADate = CDate("12/29/2022")

   'option 2
   ADate = #12/29/2022#

   'option 3  
   ADate = Format("12/29/2022", "mm/dd/yy")

    SQLString = "INSERT INTO Table1 (TheDate) Values(" & ADate & ")"    '

    OpenSQLConnection ' open connectionrs.Open SQLString, Conn ' & 
recordsetCloseSQLConnections

   Debug.Print "Date IN: " & CStr(ADate)
End Sub

Sub TestDateOutOfDB()
    Dim sSQLSting   As String

    OpenSQLConnection

   sSQLSting = "SELECT * From [Table1]"    ' get all data from Table1

    rs.Open sSQLSting, Conn

    'write the table to worksheetWorksheets("Sheet1").Range("A1").CopyFromRecordset rs

    'this will have the record just added to DB (first & only record)
    Debug.Print "Date OUT: " & Worksheets("Sheet1").Range("B1")

    CloseSQLConnections
End Sub

'Open Access DB connection
Sub OpenSQLConnection()
    sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DATABASE_LOC & DATABASE_NAME & ";Persist Security Info=False;"
    Conn.Open sconnect
End Sub

'Close any DB Connection
Sub CloseSQLConnections()
    On Error Resume Next
    rs.CloseSet rs = Nothing
    Conn.Close
    Set Conn = Nothing
End Sub

See discussions in these posts regarding use of Dim and New in same line: What is the reason for not instantiating an object at the time of declaration? and Is the poor performance of Excel VBA auto-instancing a myth? — Parfait
– Parfait, Commented Dec 29, 2022 at 4:06

Parfait · Accepted Answer · 2022-12-29 03:46:43Z

2

Consider the best practice of SQL parameterization which is supported with ADO library. This approach which is not limited to VBA or MS Access but any programming language connecting to any backend database allows for binding of values to a prepared SQL query to safely bind literal values and properly align data types:

...
Public cmd As ADODB.Command ' AVOID DECLARING OBJECT WITH New
...

Sub TestDateIntoDB()

    Dim SQLString As String
    Dim ADate As Date
    Const adDate = 7, adParamInput = 1

    'option 1
    ADate = CDate("12/29/2022")

    ' PREPARED STATEMENT WITH QMARK PLACEHOLDER
    SQLString = "INSERT INTO Table1 (TheDate) Values(?)"

    ' CONFIGURE ADO COMMAND
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = Conn
        .CommandText = SQLString
        .CommandType = adCmdText
    
        ' BIND VALUES
        .Parameters.Append .CreateParameter("paramDate", adDate, adParamInput, , ADate)
    End With
    
    ' EXECUTE ACTION QUERY
    cmd.Execute

    Set cmd = Nothing
    Debug.Print "Date IN: " & CStr(ADate)
End Sub

answered Dec 29, 2022 at 3:46

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Honorez Over a year ago

That's the clean right way to go

Gustav · Accepted Answer · 2022-12-29 11:47:42Z

0

Force a format of the string expression for the date value:

Sub TestDateIntoDB()

    Dim SQLString As String
    Dim ADate     As Date

    ' Option 0.
    ADate = DateSerial(2022, 12, 29)

    SQLString = _
        "INSERT INTO Table1 (TheDate) " & _
        "VALUES (#" & Format(ADate, "yyyy\/mm\/dd") & "#)"

    ' <snip>

End Sub

answered Dec 29, 2022 at 11:47

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

Writing/Reading Dates when using MS Excel VBA to MS Access Database

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related