Insert date to SQLServer from Access

[Rowen]

I'm trying to insert data into SQLServer from access, I've got the connection working but I'm having trouble inserting the date. I'm getting the following error message.

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. "

I think that this is because the dates in access are in English date format and so it is having trouble recognising that the 13/6/09 is a valid date. Can anyone help with this problem?

My insert code is as follows:

Dim rs1 As New ADODB.Recordset
rs1.Open "Select * From tblEstateInspections1", cnFrom, adOpenStatic, adLockOptimistic
While Not rs1.EOF
strInsertCommand = "Insert into dbo.tblEstateInspections (StairID, InspectionDate) " & _
                     " Values (" & rs1("StairID") & ", '" & rs1("InspectionDate") & "')"
cnTo.Execute (strInsertCommand)
rs1.MoveNext
Wend

I have tried taking out the single quotes around the inspection date, but this just resulted in the date being entered as if it was 0, so in SQLServer it's all just 1/1/1900.

 

[SQL_Hell]

This will convert your date to American, Try and use it in your values bit of code and post back if you have any issues

select convert(varchar,getdate(),101)

 

[Rowen]

I have found this piece of code and am attempting to make it work at the moment, but really I'd like the date to stay in English format, I've seen things that say Set DateFormat DMY, but I'm not sure how to get this code to work in VBA.


Edit: I changed it from 101 to 103 and it's working fine. Thanks.

 

[SQL_Hell]

Either your machine local or server local is setting American dates or the sqlserver settings are doing it.

Either you change these settings or you format the date

 

[SQL_Hell]

Oh, just saw your edit.. glad you got it sorted

 

[Rowen]

Raising the topic from the dead a bit but this issue is pretty much related. I'm now trying to update an existing record with a date value that is based on when a check box is ticked. I need to insert the date as dd/mm/yyyy format.

I've got the following code:

DoCmd.RunSQL "Update tblTECases  " & _
        "Set ASB7 = Convert(datetime, #" & Date & "#, 103) " & _
        " Where Forms.frmTECases.TECaseNo = tblTECases.TECaseNo"

I'm not really sure if this is the right code, but it's giving me a Run-Time error 3085. Undefined function 'Convert' in expression.