Problem inserting time values from Access into SQL Server

[George Atkins]

I am using Access 2007 and SQL Server 2000, with MS ADO 2.8 Library (I've
tried other versions, too).

I'm trying to post records from an Access table into SQL Server 2000, but I
error out when trying to add time values stored in a regular Access date/time
field. Error: "Invalid date format" error -2147467259. Here is my code:

Dim cnSQL As ADODB.Connection
Dim rsAU As ADODB.Recordset ' SQL table
Dim rsACC As DAO.Recordset ' the ACCESS tblCurrentAttendanceTemp table
Dim MyMinutes As Integer

On Error GoTo ErrorHandler

Set cnSQL = New ADODB.Connection
Set rsAU = New ADODB.Recordset

Set rsACC = CurrentDb.OpenRecordset("tblCurrentAttendanceTemp",
dbOpenDynaset)


With cnSQL
.Provider = "sqloledb"
.Properties("Data Source").Value = "12.69.31.1"
.Properties("Initial catalog").Value = "rdale" ' Production Server
.Properties("User id").Value = "gka"
.Properties("Password").Value = "nutcase"
.Open
End With

rsAU.Open "AttendanceUnit", cnSQL, adOpenKeyset, adLockOptimistic

With rsACC ' the Access source table
.MoveFirst
While Not .EOF
rsAU.AddNew ' the SQL Server table
rsAU.Fields("CalendarID") = .Fields("CalID")
rsAU.Fields("personID") = .Fields("PID")
rsAU.Fields("sectionID") = .Fields("SecID")
rsAU.Fields("Date") = .Fields("AttendanceDate")
rsAU.Fields("startTime") = .Fields("StartTime") ' THIS FAILS!
rsAU.Fields("endTime") = .Fields("EndTime") ' THIS FAILS,
TOO!
MyMinutes = ((.Fields("EndTime") * 24) - (.Fields("StartTime") *
24)) * 60
rsAU.Fields("units") = MyMinutes
rsAU.Update
.MoveNext
Wend
End With

The Access date fields are standard date/time fields with no special
formatting, storing only entered times, such as 9:30 AM. The SQL "startTime"
and "endTime" fields are SmallDateTime data types.

If I remove the StartTime and EndTime fields, there is no issue with adding
the records, of course. There is also no issue with the AttendanceDate field.

What do I need to do in order to get the SQL table to accept my Access time
values?

 

[Stefan Hoffmann]

hi George,

The Access date fields are standard date/time fields with no special
formatting, storing only entered times, such as 9:30 AM. The SQL "startTime"
and "endTime" fields are SmallDateTime data types.

This is not possible as "no date" portion means the not displayed date
of your value is
1899-12-30 which does not fit into the SMALLDATETIME datatype:

http://msdn.microsoft.com/en-us/library/ms182418.aspx

He starts 1900-01-01.


mfG
--> stefan <--

 

[George Atkins]

Hah! I see. I would have thought that the ODBC driver or at least the ADO
library would handle that kind of conversion. There must be something I can
do about this, which means I have to somehow append a date to the time entry,
correct? And if so, I should at least put in the actual date. I presume that
I have to somehow format the date ina yyyymmdd format and append it to the
time value in the Access StartTime field, or example?

 

[George Atkins]

Hah! I see. I would have thought that the ODBC driver or at least the ADO
library would handle that kind of conversion. There must be something I can
do about this, which means I have to somehow append a date to the time entry,
correct? And if so, I should at least put in the actual date. I presume that
I have to somehow format the date ina yyyymmdd format and append it to the
time value in the Access StartTime field, or example?

 

[Stefan Hoffmann]

hi George,

Hah! I see. I would have thought that the ODBC driver or at least the ADO
library would handle that kind of conversion. There must be something I can
do about this, which means I have to somehow append a date to the time entry,
correct? And if so, I should at least put in the actual date. I presume that
I have to somehow format the date ina yyyymmdd format and append it to the
time value in the Access StartTime field, or example?

I would recommend changing the data type. Otherwise you can simply add 1
to your time values before storing them.

If read these values in Access/VBA use the TimeValue() function to
extract the time value.


mfG
--> stefan <--

 

[david]

Hah! I see. I would have thought that the ODBC driver or at least the

The Jet/DAO library handles that conversion. That was kind of the
intention of the Jet/DAO library. "Access" allowed you to access
any kind of data in any kind of storage.

ODBC and ADO expect values to be ODBC/ADO format. That
was kind of the intention of ODBC/ADO. A strictly defined interface
that anybody could write strictly defined values to.

The Jet/DAO conversion did have some obscure gotchas.
If you knew how, you could write a date that you couldn't
find, because the translation only applied if the translator
realised that you needed a date translation.

(david)