• Upsizing to SQL 2000 backend (Access2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Upsizing to SQL 2000 backend (Access2000)

    Author
    Topic
    #399975

    I have used the DTS utilities to import from Access and this has been mostly sucessful. My real issue is that in one table in access i was utilizing Date/Time fields set to a format of SmallTime,ie fields “TimeIn” and “TimeOut”. Further, I also used an update query in access to calculate the time between the In and Out. The SQL table/field definition of “smalldatetime” does not seem compatible for the import of existing data and the update query simply reports an Overflow. Any suggestions????

    Regards

    toncc

    Viewing 1 reply thread
    Author
    Replies
    • #776775

      Access datatypes don’t always have an exact equivalent in SQL Server. The date/time fields are perhaps the worst case of that, since Access doesn’t understand SQL Server datestime stamps and vice versa. Don’t try to force them to use the “same” field type because it won’t work. If you upsized the table, the date fields would have been converted from normal Access format datetimes to a SQL Server date/time field. Then in views, you would have to convert the value back to a date/time that Access could work with.

      • #776815

        I’m confused – as usual confused
        AFAIK, Access has only one kind of date/time field – you can apply a format called short time to it, but it still is stored as an 8 byte decimal value with the integer portion being days, and fractional value being the portion of the day. The format SQL dates use is very similar – they do store the value with less precision of the fraction however, and allow a larger number of days from 12/31/1899. If I remember correctly, SQL dates are only accurate to something like 10 milliseconds, while Access is precise to about 1 msec. So why wouldn’t a simple format choice work to display Short Time on a SQL field just as well as an Access one?

        (On the other hand the SQL datetimestamp field is a completely different beast, and nothing in Access corresponds to it – they are useful only in certain situations that you don’t often encounter with Access front-ends.)

        • #776943

          I wasn’t talking about formatting, Wendell, but about datatypes. Sorry if I confused you.

        • #776944

          I wasn’t talking about formatting, Wendell, but about datatypes. Sorry if I confused you.

      • #776816

        I’m confused – as usual confused
        AFAIK, Access has only one kind of date/time field – you can apply a format called short time to it, but it still is stored as an 8 byte decimal value with the integer portion being days, and fractional value being the portion of the day. The format SQL dates use is very similar – they do store the value with less precision of the fraction however, and allow a larger number of days from 12/31/1899. If I remember correctly, SQL dates are only accurate to something like 10 milliseconds, while Access is precise to about 1 msec. So why wouldn’t a simple format choice work to display Short Time on a SQL field just as well as an Access one?

        (On the other hand the SQL datetimestamp field is a completely different beast, and nothing in Access corresponds to it – they are useful only in certain situations that you don’t often encounter with Access front-ends.)

    • #776776

      Access datatypes don’t always have an exact equivalent in SQL Server. The date/time fields are perhaps the worst case of that, since Access doesn’t understand SQL Server datestime stamps and vice versa. Don’t try to force them to use the “same” field type because it won’t work. If you upsized the table, the date fields would have been converted from normal Access format datetimes to a SQL Server date/time field. Then in views, you would have to convert the value back to a date/time that Access could work with.

    Viewing 1 reply thread
    Reply To: Reply #776944 in Upsizing to SQL 2000 backend (Access2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




    Cancel