2

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.

Improve this question
3
  • 1
    I suggest you should try making insertions using parameters. This is much safer and ignores the formatting. So it will be easier for you to insert the date and time. Otherwise you have to deal with formatting which is a big headache... Commented Feb 18, 2016 at 0:17
  • 2
    MS Access is not MySQL or SQL-Server. Please don't use inappropriate tags. Commented Feb 18, 2016 at 0:17
  • My apologies. I kind of got thrown in the deep end with learning this stuff and I'm a bit hazy on differences since I'm only familiar with basic. Thanks for the fix. Commented Feb 19, 2016 at 20:36

4 Answers 4

Reset to default
3

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.

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

5 Comments

I can't believe I missed that small error of putting strSQl as a date. That small fix resulted in the code doing what I want, but I tried out your code as well so I can learn some better coding habits in the future. But when I attempted it, it gave me run-time error 3061, "Too few parameters. Expected 3."
It doesn't ask for anything but it shows me the simplified table with the parameters of [Start], [End], and [Duration] I also noticed that your code lists .Parameters("p2") twice. I assumed the second to be "p3"
I corrected the third .Parameters line and bracketed the parameter names in the VALUES list. Does it work for you with those changes?
Yes that works now! I had already changed the "p2" to "p3", so it must have been the parameters in VALUES that needed to be updated. May I also ask why that is important?
I don't actually believe the brackets were important --- I added them (like out of desperation) before I noticed I originally had "p2" twice. But the brackets shouldn't hurt, so I left them.
3

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 , , ,.

Improve this answer

4 Comments

Your "Better answer: Use parameters" section would be even more better if it gave VBA examples instead of VB.NET ones.
@GordThompson I have amended my answer to use ADO in VBA syntax.
If you amended it to hold the correct SQL syntax, it would be even useful. Here's the reference for the Format function: Format Function (Visual Basic for Applications)
What's the 'tt' for in 'hh:mm:ss tt' for Access/Jet? 'tt' is not documented in the VBA documentation AFAICS and it does NOT work with 'tt' in Access 2016. It does work without 'tt'.
1

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
Improve this answer

Comments

-1

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.

Improve this answer

2 Comments

Why did you recommend using CDate(Now - Date()) instead of Time()? Either gives you a Date/Time value with Dec 30 1899 as the date part: Debug.Print Format(CDate(Now - Date()), "mmm d yyyy hh:nn:ss"), Format(Time, "mmm d yyyy hh:nn:ss")
TBH the question wasn't very clear, but I was trying to answer the part "I just need the Time of Day and duration (not so much the date itself)" by showing a general way to remove date information from a Date expression. As you point out both give the same answer. I wanted the OP to learn something here about how to manipulate date is VB

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.