insert datetime sql syntax?

Question

I have a button which executes some vba code which lists files and their date-created. I'm stumped on the sql insert with the datetime format insertion. The error returns a code 128 upon db.execute. Any ideas?

Do While Len(strFile) > 0
    'Debug.Print strFolder & strFile

        sSQL = "INSERT INTO tblVideos ( FileName , FileDate) VALUES ('" & strFile & "'," & Format(StrToDate(dateCreated(strFolder & strFile)), "\#yyyy-mm-dd hh:nn:ss\#") & ")"

        Debug.Print sSQL

        db.Execute sSQL, dbFailOnError

    strFile = Dir()
Loop

end sub 

Public Function StrToDate(strIn As String) As Variant
    Dim var As Variant
    Dim yr As Variant

    If Len(strIn & "") Then
        'StrToDate = CDate(Mid$(strIn, 3, 4) & "/" & Left$(strIn, 2) & "/" & Right$(strIn, 4))
        var = Split(strIn, "/")
        yr = Split(var(2), " ")
        StrToDate = var(0) & "/" & var(1) & "/" & yr(0) & " " & yr(1)
    Else
        StrToDate = Null
    End If
End Function

HansUp · Accepted Answer · 2013-08-08 11:17:36Z

You get error 128 at this line:

db.Execute sSQL, dbFailOnError

If the INSERT statement was faulty, you would get an error from the db engine. However, error 128, "Application-defined or object-defined error", is an error from Access' VBA host, not from the db engine. That makes me suspect the problem is not the INSERT statement (sSQL), but rather something else in that line.

You didn't show how you declare db and give it a value. In order for that Execute to work, db must be a valid DAO.Database object reference.

If yours is set up correctly, this should show you the full path to the db file ...

Debug.Print db.Name

If that gives you an error, or anything other than the db file path, examine how you set up db. Often it is set to the current database like this ...

Dim db As DAO.Database
Set db = CurrentDb

Show us how you're setting db if you need help with that.

Carsten Massmann · Accepted Answer · 2013-08-08 05:08:22Z

0

Maybe this can help you?

How To Code For Compatible Date Formats for Access and SQL Server:

'/////// ASSIGN A FORMAT BASED ON 'TIME STAMP' OR 'DATE'
If UseTimeAndDate Then
  '---- DATE & TIME
  strDate = "{ts '" & Format(DateToConvert, "yyyy-mm-dd hh:mm:ss") & "'}"
Else
  '----- DATE
  strDate = "{d '" & Format(DateToConvert, "yyyy-mm-dd") & "'}"
End If

answered Aug 8, 2013 at 5:08

Carsten Massmann

28.4k3 gold badges25 silver badges49 bronze badges

Comments

david · Accepted Answer · 2013-08-08 10:42:58Z

What is 'datecreated()', and are you converting a date, to a string, to a date, to a string to a date?

DateCreated returns date
StrToDate implicitly converts parameter to string
StrToDate takes string and converts it to date
Format takes date and converts it to string
SQL takes string and converts it to date.

If DateCreated returns a date, then

sSQL = "INSERT INTO tblVideos ( FileName , FileDate) VALUES ('" & strFile & "', dateCreated(" & strFolder & strFile & "))"

Values don't have to be strings, they can be numbers, or dates (which are a special case of number).

Even when I am inserting strings into date fields, I don't always use "Format" to do it. If you are just using Access/VBA, CDBL works just as well:

SQL = "WHERE (mydate = " & cdbl(myOtherDate) & ")"

CDBL avoids all the problems of getting the year/month/day order correct, but only works well if the database date field has the same structure as VBA date values. Which is the case for Access MDB, but not for SQL Server (a date in SQL Server is not a CDBL).

Collectives™ on Stack Overflow

insert datetime sql syntax?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related