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
> Inserting Time From Ms Access To SQL Server, Access 2016    
 
   
ordnance1
post Oct 16 2019, 09:43 AM
Post#1



Posts: 692
Joined: 7-May 11



Using MS Access as a front end and SQL Server as a back end, I have an insert into routine to insert some data.

It works fine until I add the last line to insert a time value. When the code runs I get an error " ODBC-Insert on a linked table dbo_Group' failed "

Any thought on what the issue might be?

CODE
        SQL = "Insert Into dbo_Group (ClubNumber, GroupCategory_ID, User_ID, Name, EIN, " & _
        "Date_AgentAssigned, Date_CharterSuspended," & _
        " Week_1, Week_2, Week_3, Week_4, " & _
        " Meeting_Day_Members, Meeting_Time_Members)" _
        & "" _
        & " Values (" & Me.txt_ClubNumber & ", " & Me.opt_Create & ", " & TempVars!User_ID & "," _
        & " '" & IIf(IsNull(Me.txt_Name), Null, Me.txt_Name) & "'," _
        & " '" & IIf(IsNull(Me.txt_EIN), Null, Me.txt_EIN) & "'," _
        & " '" & Me.txt_AgentAssignent & "'," _
        & " '" & IIf(IsNull(Me.txt_SuspenedDate), Null, Me.txt_SuspenedDate) & "'," _
        & " '" & IIf(IsNull(Me.chk_1st), Null, Me.chk_1st) & "'," _
        & " '" & IIf(IsNull(Me.chk_2nd), Null, Me.chk_2nd) & "'," _
        & " '" & IIf(IsNull(Me.chk_3rd), Null, Me.chk_3rd) & "'," _
        & " '" & IIf(IsNull(Me.chk_4th), Null, Me.chk_4th) & "'," _
        & " '" & IIf(IsNull(Me.cbo_MtgDay_Member), Null, Me.cbo_MtgDay_Member) & "'," _
        & " '# & IIf(IsNull(Me.txt_MtgTime_Member), Null, Me.txt_MtgTime_Member) & #');"

    CurrentDb.Execute SQL
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 09:54 AM
Post#2


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


It would be useful to see the actual SQL being concatenated out of that VBA.

I wonder if your SQL Server table allows nulls in the time field.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
nuclear_nick
post Oct 16 2019, 09:57 AM
Post#3



Posts: 1,798
Joined: 5-February 06
From: Ohio, USA


I think you may have the quote marks in the wrong place, and combined the single quote and octothorpes.

Try...

CODE
  & IIf(IsNull(Me.txt_MtgTime_Member), Null, "#" & Me.txt_MtgTime_Member & "#") & ");"


… as your last line.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
cheekybuddha
post Oct 16 2019, 10:03 AM
Post#4


UtterAccess VIP
Posts: 11,680
Joined: 6-December 03
From: Telegraph Hill


First, try:
CODE
' ...
        & " '# & IIf(IsNull(Me.txt_MtgTime_Member), Null, Format(Me.txt_MtgTime_Member, "hh:mm:ss")) & #');"
' ...

If that doesn't work, then check that the field's datatype on the server is actually a [Date]Time field and not a string field.

hth,

d

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


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 16 2019, 10:05 AM
Post#5


UtterAccess VIP
Posts: 11,680
Joined: 6-December 03
From: Telegraph Hill


Oh yes - only delimit the time portion as Nick points out - not NULL !!!

blush.gif

d

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


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 10:07 AM
Post#6


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


Good catch David. If Access sees a DateTime2 field as string, that can be a problem. This will happen if you are still using the old SQL Server driver, not one of the newer drivers that DOES handle DateTime2 properly.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ordnance1
post Oct 16 2019, 10:21 AM
Post#7



Posts: 692
Joined: 7-May 11



I get an error " Expected end of statement " and hh is highlighted
Go to the top of the page
 
ordnance1
post Oct 16 2019, 10:24 AM
Post#8



Posts: 692
Joined: 7-May 11



Your last post has me wondering. I set the SQL server field to Time(0). Is DateTime2 what I should be using?
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 10:25 AM
Post#9


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


Attached File  DateTime2.png ( 12.57K )Number of downloads: 0


When the old SQL Server driver (which is the default you get with Windows) see the datetime2 field, it can't resolve it as a date and renders it as a string in Access instead. This may or may not be part of the problem here.

One of the newer drivers will work, but you'll need to download and install it on each computer where your Access FE runs.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 10:27 AM
Post#10


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


It's up to you which datatype is appropriate. This might be sort of a red herring, though, as the problem might be the syntax.

I usually add a debug.Print line to print the concatenated SQL into the immediate window in order to see exactly what's being produced. Sometimes it takes a few tries to get the picky details right.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ordnance1
post Oct 16 2019, 10:39 AM
Post#11



Posts: 692
Joined: 7-May 11



I have no clue as how to do that
Go to the top of the page
 
arnelgp
post Oct 16 2019, 10:47 AM
Post#12



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


CODE

       SQL = "Insert Into dbo_Group (ClubNumber, GroupCategory_ID, User_ID, Name, EIN, " & _
        "Date_AgentAssigned, Date_CharterSuspended," & _
        " Week_1, Week_2, Week_3, Week_4, " & _
        " Meeting_Day_Members, Meeting_Time_Members) SELECT p0, p1, p2, p3, p4, p5, p6,"  & _
        " p7, p8, p9, p10, p11, p12;"
        
        With Currentdb.CreateQuerydef("", SQL)
            .Parameters("p0") = Me.txt_ClubNumber
            .Parameters("p1") = Me.opt_Create
            .Parameters("p2") = TempVars!User_ID
            .Parameters("p3") = Me.txt_Name
            .Parameters("p4") = Me.txt_EIN
            .Parameters("p5") = Me.txt_AgentAssignent
            .Parameters("p6") = Me.txt_SuspenedDate
            .Parameters("p7") = Me.chk_1st
            .Parameters("p8") = Me.chk_2nd
            .Parameters("p9") = Me.chk_3rd
            .Parameters("p10") = Me.chk_4th
            .Parameters("p11") = Me.cbo_MtgDay_Member
            .Parameters("p12") = Me.txt_MtgTime_Member
            .Execute
        End With

        ''CurrentDb.Execute SQL

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
ordnance1
post Oct 16 2019, 11:13 AM
Post#13



Posts: 692
Joined: 7-May 11



Thanks to all of you for all your help.

I would still like to know how I would go about debug.print the SQL statement so I could file it away for the future.
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 11:32 AM
Post#14


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


Right after the line in which you create the SQL string by concatenation:

Debug.Print SQL

It'll go to the immediate window, which you can open using ctl-G

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ordnance1
post Oct 16 2019, 05:18 PM
Post#15



Posts: 692
Joined: 7-May 11



Thank you Sir
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 09:04 PM