[X]My Assistant

[marinko888]

Hi,
I have a SQL INSERT Query to linked SQL Database table in VBA:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO dbo_Session (ID, Date,Start) VALUES " & _
"(" & ID & ",'" & Date & "','" & Time & "')"
DoCmd.SetWarnings True

but it's not working...I have checked the format of the fields in Access are ShortText and in SQL Database are Date and the other one is Time:

 Data.PNG ( 2.43K )Number of downloads: 0


How can I format Date and the other one Time to make the insert query working?
This post has been edited by marinko888: Oct 31 2019, 05:42 AM

[cheekybuddha]

CODE

  Dim strSQL As String

  strSQL = "INSERT INTO dbo_Session (ID, [Date], Start) VALUES " & _
             "(" & ID & ", " & Format([Date], "\#yyyy\-mm\-dd\#") & ", " & Format([Time], "\#hh:mm:ss\#") & ");"
  Debug.Print strSQL
  CurrentDb.Execute strSQL, dbFailOnError

Don't use DoCmd.RunSQL - if there is an error then you risk being left in a state with Warnings turned off.

hth,

d

[nvogel]

"not working" doesn't tell us much. What result do you see? What error messages do you get?

I suggest you set system warnings ON (i.e. true) rather than off while you are debugging. That may give you a clue about what the problem is.

[GroverParkGeorge]

This is often caused by using DateTime2 datatype in the SQL Server table coupled with the old SQL Server driver that comes default in Windows. That driver does not "understand" the precision of the DateTime2 datatype and renders it as short text.

 WorDate2.png ( 10.31K )Number of downloads: 0


You need to use one of the newer drivers to avoid that problem.

If you are sure they are actual Date and Time in YOUR SQL server table, then we'll need to dig further.

[GroverParkGeorge]

Yes, TotalTime is a calculated field stored in that table in my screenshot. I acknowledge my guilt and beg your forgiveness.

[marinko888]

I have tried with the suggested Formatting solution but it has failed again. The error I am getting is the same:

 Data.PNG ( 6.71K )Number of downloads: 3


Data types in SQL Database Table:

 Data1.PNG ( 952bytes )Number of downloads: 0

This post has been edited by marinko888: Oct 31 2019, 04:29 PM

[GroverParkGeorge]

I am not aware that the Date and Time datatypes have the same problem as the DateTime2(7) datatype.

Which Driver are you using to connect to the SQL Server? I am only guessing that it might be the (truly old) default SQL Server driver in your case.

Another thing to try is to remove that field from your attempted insert statement. If you can add a test record without it, it'll confirm that's the actual culprit.

It may also be that the Time field is the problem.

And finally, are we sure it's not the ID field that's causing the problem. Is it an Identity field in the SQL Server table by any chance?

[marinko888]

I have applied what you have suggested and removed Date part from the INSERT statement and it has finally worked. So this is working:

DoCmd.RunSQL "INSERT INTO dbo_Session (ID, Start) VALUES " & _
"(" & ID & ",'" & Format(Time, "hh:mm:ss") & "')"

and when I include Date part INSERT to the table fails:

DoCmd.RunSQL "INSERT INTO dbo_Sesija (ID, Date, Start) VALUES " & _
"(" & ID & ",'" & Format(Date, "dd/mm/yyyy") & "','" & Format(Time, "hh:mm:ss") & "')"

so the problem is with Date formatting...

[cheekybuddha]

Format(date, "yyyy-mm-dd")

[marinko888]

Thank you, David!
That has worked out just fine.

So the solution that works looks like this:

DoCmd.RunSQL "INSERT INTO dbo_Session (ID, Date,Start) VALUES " & _
"(" & ID & ",'" & Format(Date, "yyyy\/mm\/dd") & "','" & Format(Time, "hh:mm:ss") & "')"

This post has been edited by marinko888: Oct 31 2019, 05:38 PM