question

EDIT

I checked what a Date/Time-datatype in Access translates to using OPENROWSET, and it does translate to a DateTime-datatype (I did a SELECT * INTO... using OPENROWSET and then checked the datatype of the columns in the created table).

So I'm wondering if the column you have in Access is really of Date/Time datatype? If that's the case, I'd say you should check if you have values in the column which won't fit into a smalldatetime in SQL Server, like Oleg suggests.

END EDIT

I always try to convert a datetime to the string format "YYYYMMDD hh:mm:ss.nnn" when I'm moving dates between systems. That way, I'm always sure the date conversion will work, regardless of my regional settings.

I no longer remember MS Access syntax for DatePart-function, but that's probably what you want to look into, so that you can convert your dates to something like "20080124 07:00:00.000"

It appears that your problem is caused by the dateformat mismatch. Here are the snippets which both use one of the values you supplied in your question:

set dateformat 'dmy';
select cast('1/24/2008 7:00:00 AM' as smalldatetime) test;

This results in the following message:

Msg 296, Level 16, State 3, Line 2
The conversion of char data type to smalldatetime data 
type resulted in an out-of-range smalldatetime value.

set dateformat 'mdy';
select cast('1/24/2008 7:00:00 AM' as smalldatetime) test;

This runs just fine:

test
-----------------------
2008-01-24 07:00:00

Please try to add the correct dateformat to the top of your query, and I hope it works:

set dateformat 'mdy';
insert into [BIAS]([EFFECTIVE_TIMESTAMP])
select * 
    from openrowset
    (
        'Microsoft.Jet.OLEDB.4.0','c:\SYSTEM2DB.mdb';'Admin';, 
        'select [EFFECTIVE_TIMESTAMP] from [BIAS]'
    ) as bias;
go

If the dateformat is not an issue then please try to look for some out of range values in your Access table. The range of the smalldatetime in SQL Sever is January 1, 1900, through June 6, 2079, so if one of the records in Access has value outside of this range it won't fit into smalldatetime.

Oleg