Excel VBA to update an SQL date field

Question

I'm trying up date an SQL table (Date field) with a date cell in excel and getting an error

Error #-2147217913: Operand type clah int is incompatible with Date

Connection to the database is fine.

This is the code i'm using

Private Sub CommandButton1_Click()
On Error GoTo FormLoadError
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim FromDate As Date
    Dim ToDate As Date
    Dim FromNumber As String
 
    Sheet2.Range("a2:zz9999").ClearContents
 
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=SQL;" & _
                  "Initial Catalog=M2MTECLIVE;" & _
                  "Integrated Security=SSPI;"
      
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
     ' Open the connection and execute.
    conn.Open sConnString
    FromDate = Worksheets("Parameters").Range("b4")   'Cell be contains a date in the format YYYY-MM-DD
    Set rs = conn.Execute("update dbo.M2MDates set FD = " & FromDate)
    conn.Close

Any ideas ?

According to the following link, the date should be enclosed between single quotes : stackoverflow.com/questions/31883448/… — Random User
– Random User, Commented Jun 12, 2021 at 15:42
Adding the single quotes worked, but not it updates as a European date (i.e. '01/06/2021' becomes '06/01/2021' in SQL, but progress ....) — Robert Hardy
– Robert Hardy, Commented Jun 12, 2021 at 17:01

Parfait · Accepted Answer · 2021-06-12 17:02:06Z

2

Consider using ADO parameters via ADO command object which avoids need of concatenating and punctuating values to SQL statement:

conn.Open sConnString

' PREPARED STATEMENT WITH QMARKS ?
strSQL = "update dbo.M2MDates set FD = ?"
FromDate = Worksheets("Parameters").Range("b4") 

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = conn
    .CommandText = strSQL
    .CommandType = adCmdText

    '  BIND DATE PARAMETER
    .Parameters.Append .CreateParameter("date_prm", adDate, adParamInput, , FromDate)

    '  EXECUTE ACTION QUERY (NO RECORDSET)
    .Execute
End With

conn.Close
Set cmd = Nothing: Set conn = Nothing

answered Jun 12, 2021 at 17:02

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

3 Comments

Cole Over a year ago

Thanks - I did not know VBA could parameterize SQL. Is there a way to have multiple parameters? e.g., SELECT * FROM t1 WHERE id = ? AND type = ? If you can point me towards documentation, I'd appreciate it. I already +1 but I can create a new question as well if you'd like.

Parfait Over a year ago

There are many SO posts about parameterization in VBA. Nearly every modern programming language have libraries like ADO that support this recommended method to run SQL at application layer. Start with SO search or Google/Bing, etc. With ADO, for each qmark (?) in SQL, add a corresponding .Parameters.Append line.

Cole Over a year ago

Thank you @Parfait. When I took over maintenance of some VBA for internal network queries years ago, it was unparameterized. While I have learned a lot since including parameterization, I never put it together that of course VBA would be able to parameterized. If my goal were not to try to port some of these data retrievals to more dashboard like implementations such as tableau, I would go forward with this. Thank you again.

Collectives™ on Stack Overflow

Excel VBA to update an SQL date field

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related