UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL Insert To Linked SQL Database Table - Data Type, Access 2016    
 
   
marinko888
post Oct 31 2019, 05:42 AM
Post#1



Posts: 74
Joined: 9-April 19



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:

Attached File  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
Go to the top of the page
 
cheekybuddha
post Oct 31 2019, 05:50 AM
Post#2


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
nvogel
post Oct 31 2019, 06:54 AM
Post#3



Posts: 1,039
Joined: 26-January 14
From: London, UK


"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.
Go to the top of the page
 
GroverParkGeorge
post Oct 31 2019, 07:15 AM
Post#4


UA Admin
Posts: 36,177
Joined: 20-June 02
From: Newcastle, WA


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.

Attached File  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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Oct 31 2019, 04:02 PM
Post#5


UA Admin
Posts: 36,177
Joined: 20-June 02
From: Newcastle, WA


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


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
marinko888
post Oct 31 2019, 04:21 PM
Post#6



Posts: 74
Joined: 9-April 19



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

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


Data types in SQL Database Table:

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

This post has been edited by marinko888: Oct 31 2019, 04:29 PM
Go to the top of the page
 
GroverParkGeorge
post Oct 31 2019, 04:40 PM
Post#7


UA Admin
Posts: 36,177
Joined: 20-June 02
From: Newcastle, WA


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?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
marinko888
post Oct 31 2019, 05:06 PM
Post#8



Posts: 74
Joined: 9-April 19



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...
Go to the top of the page
 
cheekybuddha
post Oct 31 2019, 05:23 PM
Post#9


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
marinko888
post Oct 31 2019, 05:29 PM
Post#10



Posts: 74
Joined: 9-April 19



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
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2019 - 03:32 PM