DTS with Access 2000

  • I'm trying to convert an access database to SQL Server. I got a (DBTYPE_DBTIMESTAMP)error.

    How do I change the datatype in Access so that SQL DTS can accept the date type.

    I appreciate any help.

  • This was removed by the editor as SPAM

  • I'm no Access expert, but to get me started could you post the table script from access so one of us could create it and try the upgrade. I'm assuming table scripts are possible in access, but if not, could you list the datatypes of the access columns in the offending table.

    Paul Ibison

    Paul.Ibison@blueyonder.co.uk


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • you can handle the DBTYPE_DBTIMESTAMP conversion of datetime to SQL Server timestamp in DTS Transform task using activex script

  • Did you try upsizing wizard of Access??

    A While back i tried that and it worked fine with me??

    Its a simple straight forward wizard

    Regards,

    Affan

  • I did it some time ago and also experienced problems with date fields, but it was the date _values_, what caused the problem (some stange dates got into the access.mdb).

    Check first, if all your dates are in the allowed range.

    Werner

  • First you should look at your data types in Access. Access can store some non-standard dates and times. For example Access can store a time as 4:55:02 AM SQL Server only knows a date time and if you try to bring in the time to a datetime field you will receive the dbtimestamp error. You may need to transfrom the data, either in DTS or in a Query in Access and then dts the data in from the query. Or you can modify the receiving sql data type to a varchar instead of a datetime.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply