Help: 'Data Overflow' error while exporting [from Ms Access] to Ms SQL 2000

hi there,

I would like to export tables in my existing Ms Access database to Ms SQL 2000 database...

However, for tables which have 'date/time' data type in Ms Access... would give me an error message : "(<table_name>, DBTYPE_DBTIMESTAMP), status 6ata overflow)".

Does anyone know how to solve this problem? What should I do to export date/time field correctly to Ms SQL without overflwo error?

Help is much appreciated. Thanks...

Hi there,

The reason you are getting that error is because you have a date in your Access table that is either too small or too large for the SQL data type. The first thing to try is setting the datatype of your SQL field to DateTime as opposed to SmallDateTime, as this allows for a larger date range. Secondly, manually check the smallest and largest dates in your Access table before tranferring. Look for erroneous dates such as 1/1/199, as this is probably just a data entry error. Change the value or delete the records.

thanks for da help.. ll go and try it out...

Despite problem solved (?) long time ago, decided to give an advice for future users who use SQL Server 2000 and tryin` to export data from MSAccess mdb...

To convert MSAccess`s short format date/time columns` data to SQLServer format without errors simply select 'datetime' in 'Mappings' (see Import and Export Data application options) - it works ok.

Timestamp is for DATE, such as yyyy.mm.dd hh:mm:ss, and SmallDateTime is for yyyy.mm.dd (it`s to small for hh:mm).
Sincerely...
AG aka AndyCODE ;-)