question

This is correct. Access does not have an equivalent of **time** data type. However, SQL Server will happily accept a datetime input from your Access query and discard the date part when inserting the **time** value. For example,

declare @t time(0);
set @t = getdate();
select @t;

works just fine Please try formatting your query such that the time value looks like a well formatted datetime (or use can use the **now** function).

@EuniceH Since implicit conversion from time to varchar is allowed, please try to use string but do not specify the AM/Pm part, and format the string as hh:mm:ss meaning include leading zero if needed for any part that is less than 10. For example, to represent 5 past 8 in the morning use "08:05:00" and to represent quarter till 5 in the afternoon use "16:45:00"

I've tried this and always get error 3155 : ODBC--insert on a linked table 'tblBAIIDActivity' failed. I've also used the ActivityTime and dteTime as a Date and that doesn't work either, same error. Here's a portion of my code. If I comment out the "!ActivityTime = dteTime" then the insert works. Public Type BAIIDEvent ... ActivityTime As String ... End Type Public Sub AddBAIIDEvent(udtBAIIDEvent As BAIIDEvent, Optional fSuccessful As Boolean) Dim rst As DAO.Recordset Dim dteTime As String ... dteDate = udtBAIIDEvent.ActivityDate dteTime = udtBAIIDEvent.ActivityTime ... Set rst = gdbTables.OpenRecordset("select * from tblBAIIDActivity where BAIIDID = " & udtBAIIDEvent.BAIIDID & " AND ActivityDate = #" & dteDate & "# AND BAIIDEventID = " & lngEventID, dbOpenDynaset, dbSeeChanges) With rst If .BOF And .EOF Then .AddNew ... !ActivityTime = dteTime .Update ... End If End With

plus I forgot to add that I tried assigning !ActivityTime = Now() and that gave the same error.

that worked! I'll have to mess around with it, but at last there is a way to insert! Thanks much Oleg!

@EuniceH From reading [this article in BOL][1] it appears that **hh:mm:ss** format should work and both **hh:mm:ssAM** and **hh:mm:ss AM** should work as well. This means that including AM or PM with or without space is allowed though not necessary in favour of military format and should work provided that every portion of time (hour, minute, second) is formatted as 2 digits (left padded with zero if needed). This appears to be a critical requirement. In other words, if you original query in question would have '08:00:00 AM' instead of '8:00:00 AM' then it would just work :) Please drop a line here to confirm, this could be very a useful knowledge to other users. [1]: http://msdn.microsoft.com/en-us/library/bb677243.aspx

for me "14:45:25" worked, but "08:00:00 AM" did not work.

@EuniceH This is very good to know, thank you. At least the **hh:mm:ss** works. I will have to remember this one.