CODEWITHSUNDEEP
vbams-accessms-access-2013
AwkwardBowman
AwkwardBowman

Reputation: 123

MS Access: Date format for SQL Insert using VBA

This may be a very simple question, but I've had difficulty understanding the information I've found online so far. When I try to run SQl from within my VBA, it gives me a type mismatch on my strSQl.

I have a table [tbl_DatabaseUpdateLog] with 4 columns

UpdateID (Autonumber)

Start (Date/Time)

End (Date/Time)

Duration (Date/Time)

I have to regularly pull information from company databases for a smaller analysis project, and I want to log how often and how long it takes to run these queries. Every time starts the update, it runs the following:

Dim StartUpdate, EndUpdate, LengthUpdate, strSQl As Date
StartUpdate = Time()
    
Call UpdateAll 'This calls the collection of queries and is irrelevant for my problem

EndUpdate = Time()
LengthUpdate = EndUpdate - StartUpdate
Forms!frm_Timer.Caption = "Update Completed at " & EndUpdate & " (" & Format(LengthUpdate, "HH:MM:SS") & ")"

    DoCmd.SetWarnings (0)
    strSQl = "INSERT INTO tbl_DatabaseUpdateLog ( Start, [End], Duration ) " & _
            "SELECT '" & StartUpdate & "' AS Started, '" & EndUpdate & "' AS Ended, '" & LengthUpdate & "' AS Lasted"
    DoCmd.RunSQL strSQl
    DoCmd.SetWarnings (-1)
    
DoCmd.Close acForm, Me.Name

I have tried using #'s around the dates and using Now() instead of Time() but I feel like I'm missing a basic concept to help me fix the problem. I just need the Time of Day and duration (not so much the date itself) if that helps.

Upvotes: 2

Views: 8670

Answers (4)

HansUp
HansUp

Reputation: 97101

When I try to run SQl from within my VBA, it gives me a type mismatch on my strSQl

Examine these 2 statements ...

Dim StartUpdate, EndUpdate, LengthUpdate, strSQl As Date
strSQl = "INSERT INTO tbl_DatabaseUpdateLog ( Start, [End], Duration ) " & _
    "SELECT '" & StartUpdate & "' AS Started, '" & EndUpdate & "' AS Ended, '" & LengthUpdate & "' AS Lasted"

When you declare strSQl As Date, that means strSQl can only hold Date/Time values. A string such as "INSERT INTO ... whatever" is not a Date/Time value. So when you attempt to store such a string to strSQl, Access complains the datatypes are not compatible: "Type mismatch".

Since you intend strSQl to hold the text of a SQL Statement declare it this way (instead of As Date): Dim strSQl As String

That change should get you past the error. For your larger goal, use a parameter query with DAO.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim StartUpdate As Date, EndUpdate As Date, LengthUpdate As Date
Dim strSQl As String

StartUpdate = Time()
Call UpdateAll 'This calls the collection of queries and is irrelevant for my problem
EndUpdate = Time()

LengthUpdate = EndUpdate - StartUpdate
Forms!frm_Timer.Caption = "Update Completed at " & EndUpdate & " (" & Format(LengthUpdate, "HH:MM:SS") & ")"

strSQl = "INSERT INTO tbl_DatabaseUpdateLog ([Start], [End], Duration) " & _
    "VALUES ([p1], [p2], [p3]);"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSQl)
With qdf
    .Parameters("p1").Value = StartUpdate
    .Parameters("p2").Value = EndUpdate
    .Parameters("p3").Value = LengthUpdate
End With
qdf.Execute dbFailOnError
DoCmd.Close acForm, Me.Name

Note that with this approach for the INSERT, date format is not even an issue. Since StartUpdate, EndUpdate, and LengthUpdate will all be valid Date/Time values, you can simply feed them to the parameters without bothering about format and # delimiters.

Upvotes: 3

Gustav
Gustav

Reputation: 55806

A more straight method is using native DAO:

Dim rs As DAO.Recordset
Dim StartUpdate As Date
Dim EndUpdate As Date
Dim LengthUpdate As Date

StartUpdate = Time    
Call UpdateAll 'This calls the collection of queries and is irrelevant for my problem.    
EndUpdate = Time
LengthUpdate = EndUpdate - StartUpdate
Forms!frm_Timer.Caption = "Update Completed at " & EndUpdate & " (" & Format(LengthUpdate, "h:mm:ss") & ")"

Set rs = CurrentDb.OpenRecordset("Select Top 1 * From tbl_DatabaseUpdateLog")
rs.AddNew
    rs!Start.Value = StartUpdate
    rs!End.Value = EndUpdate
    rs!Duration.Value = LengthUpdate
rs.Update
rs.Close    
Set rs = Nothing

DoCmd.Close acForm, Me.Name

Upvotes: 1

Dai
Dai

Reputation: 155045

I see you're using INSERT INTO SELECT FROM (literal) when a straightforward INSERT INTO (columns) VALUES (values) would be simpler. You can also have multiple VALUES sub-statements in a single INSERT statement, which is neat.

I also see you're storing denormalized data, it's clear that Duration can be derived from End - Start, so you might want to drop that column.

Also, avoid Hungarian Notation, in this case prefixing a table's name with tbl_. It isn't the 1980s anymore. Don't do it.

Terrible short and easy answer using string concatenation:

(Never do this)

SQL Server

If you're using SQL Server you need to enclose dates in single-quotes and you need to convert to an unambiguous date format string, such as yyyy-MM-dd HH:mm:ss.

strSQL = "INSERT INTO neverDoThis VALUES ( '" & Format( startUpdate, "yyyy-MM-dd HH:mm:ss" & "' )"

Access / Jet

If you're using Access or Jet you need to enclose dates in hash characters '#' and use the 'MM/dd/yyyy hh:mm:ss tt' format (dumb Americans...).

strSQL = "INSERT INTO neverDoThis VALUES ( #" & Format( startUpdate, "MM/dd/yyyy hh:mm:ss tt" & "# )"

Here's a reference for the Format function: https://msdn.microsoft.com/en-us/library/office/gg251755.aspx?f=255&MSPPError=-2147217396

Better answer: Use parameters

(Do this instead)

SQL Server

SQL Server uses named parameters like @foo:

cmd.CommandText = "INSERT INTO databaseUpdateLog ( [Start], [End], [Duration] ) VALUES ( @start, @end, @duration )"
cmd.Parameters.Append cmd.CreateParameter( "@start", , , startUpdate )
cmd.Parameters.Append cmd.CreateParameter( "@end", , , endUpdate )
cmd.Parameters.Append cmd.CreateParameter( "@duration", , , lengthUpdate )

Access / Jet

Access uses anonymous placeholders accessed by the order they're added, use `?':

cmd.CommandText = "INSERT INTO databaseUpdateLog ( [Start], [End], [Duration] ) VALUES ( ?, ?, ? )"
cmd.Parameters.Append cmd.CreateParameter( "?", , , startUpdate )
cmd.Parameters.Append cmd.CreateParameter( "?", , , endUpdate )
cmd.Parameters.Append cmd.CreateParameter( "?", , , lengthUpdate )

The Append method in VBA is a Sub so calling it doesn't use parenthesis.
The CreateParameter method has 3 optional arguments in the middle: Type, Direction, and Size which can be inferred from the values, so they are omitted by putting , , ,.

Upvotes: 3

innomatics
innomatics

Reputation: 369

To calculate the time of day only, in VBA you can use this expression:

CDate(Now - Date())

Enclose the result in #'s as the result is still a Date type, even though only time info is being stored.

You can use the DateDiff function to calculate the duration, you need to choose appropriate units. e.g. :

DateDiff(DateInterval.Second, EndUpdate, StartUpdate)

The result is a long integer so you don't need to enclose the result in your SQL.

BTW parameterising your SQL is recommended, but not required.

Upvotes: -1

Related Questions