Access to SharePoint Date/Time Conversion

[Matt]

All,

I've linked my Access 2007 database to a SharePoint 2007 list as a table.
I'm using ADO to update the records in the list. Everything works fine
except when I try to update a date/time field in the SharePoint List. I'm
using the Now() function in Access and I receive a Runtime " '-2147217887
(80040e21)' The field is too small to accept the amount of data you attempted
to add. Try inserting or pasting less data." error.

Does anyone know if I have to format the Access date/time value to a
specific format for SharePoint to accept it?

Thanks in advance,

Matt

 

[Brendan Reynolds]

All,

I've linked my Access 2007 database to a SharePoint 2007 list as a table.
I'm using ADO to update the records in the list. Everything works fine
except when I try to update a date/time field in the SharePoint List. I'm
using the Now() function in Access and I receive a Runtime " '-2147217887
(80040e21)' The field is too small to accept the amount of data you
attempted
to add. Try inserting or pasting less data." error.

Does anyone know if I have to format the Access date/time value to a
specific format for SharePoint to accept it?

Thanks in advance,

Matt

I'm not a SharePoint expert, by any means, but when you create a date/time
column in SharePoint, you can specify that the column allows a date and
time, or a date only. The default is date only. So you might want to check
the setting for that column in SharePoint, or try using Date() instead of
Now(), or formatting the date to remove the time part.

 

[Matt]

Brendan,

Unfortunately, I wasn't lucky enough to have made that mistake. I did have
the columns set to date/time format. I did some playing around and found a
work around. Access stores date/time in double integer format. It just
converts the number to a date/time when it displays it. So, with that in
mind, I converted all of my date/time columns in the SharePoint list to
number columns and it worked!

The only drawback is that SharePoint will only display it in the raw
number format. So, if you plan to manipulate the date/time data in
SharePoint, this probably isn't the solution. But, if you are stricktly
using the data in Access, this did the trick!

Thanks for your help and I hope someone else can use this info to save them
a lot of heartache

Matt

 

[Brendan Reynolds]

Brendan,

Unfortunately, I wasn't lucky enough to have made that mistake. I did
have
the columns set to date/time format. I did some playing around and found
a
work around. Access stores date/time in double integer format. It just
converts the number to a date/time when it displays it. So, with that in
mind, I converted all of my date/time columns in the SharePoint list to
number columns and it worked!

The only drawback is that SharePoint will only display it in the raw
number format. So, if you plan to manipulate the date/time data in
SharePoint, this probably isn't the solution. But, if you are stricktly
using the data in Access, this did the trick!

Matt, I just tried a test. I created a list in SharePoint and added a
date/time column, set to allow a time part. I linked to that list in Access
2007, created a form bound to that list, and added a command button to the
form with the following line of code:

Me.TestDate = Now()

TestDate is the name of a text box bound to the date/time column, also
called TestDate, in the linked SharePoint list.

It works, successfully assigning the current date and time, without any
error.

I can't say what the cause of your problem is, unfortunately, but as far as
I can see it is possible to assign a date to a SharePoint date/time column
from Access, and it should not be necessary to change the type of the
SharePoint column to Number.

 

[Matt]

I tried a blank database from scratch following your example and still
receive the same error. Perhaps it is a configuration problem with our
server or we are running different versions. We have SharePoint Server 2007
Standard. Which version are you running?

I do appreciate all the help and hopefully this will help narrow down the
problem.

Matt

 

[Brendan Reynolds]

I tried a blank database from scratch following your example and still
receive the same error. Perhaps it is a configuration problem with our
server or we are running different versions. We have SharePoint Server
2007
Standard. Which version are you running?

Windows SharePoint Services 2.0 on Small Business Server 2003.

 

[eliza81]

Format Input for SharePoint DateTime field

Provided below are code samples written in C# to format an input as per SharePoint DateTime field.
/* -- Format for Date Only Field -- */
private static String ToSPDate(String strDt)
{
if (strDt == String.Empty)
return strDt;
else
return (Convert.ToDateTime(strDt)).ToString("yyyy-MM-dd");
}

Eliza

http://www.mindfiresolutions.com/Format-Input-for-SharePoint-DateTime-field-186.php