datetime field overflow (year 1899)

[Not Me]

Hi,

I'm getting the datetime field overflow error using odbc to connect to an
sql database from access. This happens when I try to enter the date
13/01/1899. It did happen when using smalldatetime, which apparently does
have that problem of only going as far back as 1900, but since changing to
datetime the error still pops up.

Is there anything I can do?

Cheers,
Chris

[Terry Kreft]

Re: datetime field overflow (year 1899)

After you change the datatype to datetime in SQL Server you need to refresh
the linked table in Access.

Terry


"Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
news:bvt32b$ana $1@ucsnew1.ncl. ac.uk...[color=blue]
> Hi,
>
> I'm getting the datetime field overflow error using odbc to connect to an
> sql database from access. This happens when I try to enter the date
> 13/01/1899. It did happen when using smalldatetime, which apparently does
> have that problem of only going as far back as 1900, but since changing to
> datetime the error still pops up.
>
> Is there anything I can do?
>
> Cheers,
> Chris
>
>[/color]

[Not Me]

Re: datetime field overflow (year 1899)

Thanks, but that didn't work :o( still get the conversion errors

Cheers,
Chris


"Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
news:aISdnSRDRp EugL_dSa8jmw@ka roo.co.uk...[color=blue]
> After you change the datatype to datetime in SQL Server you need to[/color]
refresh[color=blue]
> the linked table in Access.
>
> Terry
>
>
> "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> news:bvt32b$ana $1@ucsnew1.ncl. ac.uk...[color=green]
> > Hi,
> >
> > I'm getting the datetime field overflow error using odbc to connect to[/color][/color]
an[color=blue][color=green]
> > sql database from access. This happens when I try to enter the date
> > 13/01/1899. It did happen when using smalldatetime, which apparently[/color][/color]
does[color=blue][color=green]
> > have that problem of only going as far back as 1900, but since changing[/color][/color]
to[color=blue][color=green]
> > datetime the error still pops up.
> >
> > Is there anything I can do?
> >
> > Cheers,
> > Chris
> >
> >[/color]
>
>[/color]

[Terry Kreft]

Re: datetime field overflow (year 1899)

Strange it worked here.

Which version of Access / SQL are you using ?

Terry


"Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
news:bvtfn8$gta $1@ucsnew1.ncl. ac.uk...[color=blue]
> Thanks, but that didn't work :o( still get the conversion errors
>
> Cheers,
> Chris
>
>
> "Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
> news:aISdnSRDRp EugL_dSa8jmw@ka roo.co.uk...[color=green]
> > After you change the datatype to datetime in SQL Server you need to[/color]
> refresh[color=green]
> > the linked table in Access.
> >
> > Terry
> >
> >
> > "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> > news:bvt32b$ana $1@ucsnew1.ncl. ac.uk...[color=darkred]
> > > Hi,
> > >
> > > I'm getting the datetime field overflow error using odbc to connect to[/color][/color]
> an[color=green][color=darkred]
> > > sql database from access. This happens when I try to enter the date
> > > 13/01/1899. It did happen when using smalldatetime, which apparently[/color][/color]
> does[color=green][color=darkred]
> > > have that problem of only going as far back as 1900, but since[/color][/color][/color]
changing[color=blue]
> to[color=green][color=darkred]
> > > datetime the error still pops up.
> > >
> > > Is there anything I can do?
> > >
> > > Cheers,
> > > Chris
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Not Me]

Re: datetime field overflow (year 1899)

SQL 8, Access 2002.... I guess the problem must be hiding someplace else..

cheers,
Chris


"Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
news:Ls-dnRLvjs9q4L_dSa 8jmw@karoo.co.u k...[color=blue]
> Strange it worked here.
>
> Which version of Access / SQL are you using ?
>
> Terry
>
>
> "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> news:bvtfn8$gta $1@ucsnew1.ncl. ac.uk...[color=green]
> > Thanks, but that didn't work :o( still get the conversion errors
> >
> > "Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
> > news:aISdnSRDRp EugL_dSa8jmw@ka roo.co.uk...[color=darkred]
> > > After you change the datatype to datetime in SQL Server you need to[/color]
> > refresh[color=darkred]
> > > the linked table in Access.
> > >
> > > "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> > > news:bvt32b$ana $1@ucsnew1.ncl. ac.uk...
> > > > Hi,
> > > >
> > > > I'm getting the datetime field overflow error using odbc to connect[/color][/color][/color]
to[color=blue][color=green]
> > an[color=darkred]
> > > > sql database from access. This happens when I try to enter the date
> > > > 13/01/1899. It did happen when using smalldatetime, which[/color][/color][/color]
apparently[color=blue][color=green]
> > does[color=darkred]
> > > > have that problem of only going as far back as 1900, but since[/color][/color]
> changing[color=green]
> > to[color=darkred]
> > > > datetime the error still pops up.
> > > >
> > > > Is there anything I can do?[/color][/color]
>[/color]

[Terry Kreft]

Re: datetime field overflow (year 1899)

SQL Server 2000 and Access XP, those are the same versions I used to test
this.

Hmm, strange.

I'd suggest you go back and check through the steps again.

Make sure the field type on SQL is now datetime
Go into Access and use the Tools/Database Utilities/Linked Table Wizard
to refresh the link
Test whether you can add the data through the table


Terry


"Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
news:c00139$l46 $1@ucsnew1.ncl. ac.uk...[color=blue]
> SQL 8, Access 2002.... I guess the problem must be hiding someplace else..
>
> cheers,
> Chris
>
>
> "Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
> news:Ls-dnRLvjs9q4L_dSa 8jmw@karoo.co.u k...[color=green]
> > Strange it worked here.
> >
> > Which version of Access / SQL are you using ?
> >
> > Terry
> >
> >
> > "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> > news:bvtfn8$gta $1@ucsnew1.ncl. ac.uk...[color=darkred]
> > > Thanks, but that didn't work :o( still get the conversion errors
> > >
> > > "Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
> > > news:aISdnSRDRp EugL_dSa8jmw@ka roo.co.uk...
> > > > After you change the datatype to datetime in SQL Server you need to
> > > refresh
> > > > the linked table in Access.
> > > >
> > > > "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> > > > news:bvt32b$ana $1@ucsnew1.ncl. ac.uk...
> > > > > Hi,
> > > > >
> > > > > I'm getting the datetime field overflow error using odbc to[/color][/color][/color]
connect[color=blue]
> to[color=green][color=darkred]
> > > an
> > > > > sql database from access. This happens when I try to enter the[/color][/color][/color]
date[color=blue][color=green][color=darkred]
> > > > > 13/01/1899. It did happen when using smalldatetime, which[/color][/color]
> apparently[color=green][color=darkred]
> > > does
> > > > > have that problem of only going as far back as 1900, but since[/color]
> > changing[color=darkred]
> > > to
> > > > > datetime the error still pops up.
> > > > >
> > > > > Is there anything I can do?[/color]
> >[/color]
>
>[/color]

[Larry Linson]

Re: datetime field overflow (year 1899)

I think the SQL requires US-format for dates: mm/dd/yyyy and it may be
choking up on a thirteenth month.

Larry Linson
Microsoft Access MVP

"Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
news:c00139$l46 $1@ucsnew1.ncl. ac.uk...[color=blue]
> SQL 8, Access 2002.... I guess the problem must be hiding someplace else..
>
> cheers,
> Chris
>
>
> "Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
> news:Ls-dnRLvjs9q4L_dSa 8jmw@karoo.co.u k...[color=green]
> > Strange it worked here.
> >
> > Which version of Access / SQL are you using ?
> >
> > Terry
> >
> >
> > "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> > news:bvtfn8$gta $1@ucsnew1.ncl. ac.uk...[color=darkred]
> > > Thanks, but that didn't work :o( still get the conversion errors
> > >
> > > "Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
> > > news:aISdnSRDRp EugL_dSa8jmw@ka roo.co.uk...
> > > > After you change the datatype to datetime in SQL Server you need to
> > > refresh
> > > > the linked table in Access.
> > > >
> > > > "Not Me" <Not.Me@faker.f ake.fa.ke> wrote in message
> > > > news:bvt32b$ana $1@ucsnew1.ncl. ac.uk...
> > > > > Hi,
> > > > >
> > > > > I'm getting the datetime field overflow error using odbc to[/color][/color][/color]
connect[color=blue]
> to[color=green][color=darkred]
> > > an
> > > > > sql database from access. This happens when I try to enter the[/color][/color][/color]
date[color=blue][color=green][color=darkred]
> > > > > 13/01/1899. It did happen when using smalldatetime, which[/color][/color]
> apparently[color=green][color=darkred]
> > > does
> > > > > have that problem of only going as far back as 1900, but since[/color]
> > changing[color=darkred]
> > > to
> > > > > datetime the error still pops up.
> > > > >
> > > > > Is there anything I can do?[/color]
> >[/color]
>
>[/color]