Access filter on time

Question

I have a table with a time in a datetime field on an MSSQL2k8R2 server. I have linked the table in Access 2007. The table was migrated with the migration assistant from Microsoft.

i.e. the Managemantstudio will give on SELECT TOP 3 [AbZeit], [AbBrP] FROM [dbo].[tSollFahrten] the Result

AbZeit                  AbBrP
1899-12-30 12:53:00.000 LOR
1899-12-30 09:27:00.000 BAT
1899-12-30 10:54:00.000 LOR

When I am going to open an ADO recordset and set a filter, it worked fine (but slow).

Dim rs As New ADODB.Recordset
rs.Open "Select * from tSollFahrten;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rs.Filter = "AbZeit=#12:53#"
Debug.Print rs.RecordCount
rs.Close

The result is one recordset. But to open the recordset already filterd won’t work at all. I have no result.

Dim rs As New ADODB.Recordset
rs.Open "Select * from tSollFahrten where AbZeit = #12:53#;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Debug.Print rs.RecordCount
rs.Close

Has anyone an idea what’s going on there?

Answer 1

The problem is, that the time is in the SQL server stored as datetime. So the field in the linked table is a datetime. When the time is stored as a time(7) in the SQL server, the field in the linked table will get a text. And then the select will work with AbZeit='12:53' as expected.

Answer 2

Since it is a Date/Time type I would suggest you look for the complete Date and time. Something like,

Dim rs As New ADODB.Recordset

rs.Open "SELECT * FROM tSollFahrten " & _
        "WHERE AbZeit >= #1899-12-30 12:53:00# AND AbZeit <= #1899-12-30 12:54:00#;" , _
        CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Debug.Print rs.RecordCount

rs.Close

Answer 3

There is an important difference between the two statements:

rs.Open "Select * from tSollFahrten;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rs.Filter = "AbZeit=#12:53#"

==> In this case, you get first the data from SQL server, with SQL SERVER SYNTAX, and then you filter them LOCALLY with ACCESS SYNTAX.

rs.Open "Select * from tSollFahrten where AbZeit = #12:53#;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

==> In this case, you do everything with SQL SERVER SYNTAX. However, the Hashtag ## is specific to Access, and SQL Server does not understand it.

EDIT: In SQL Server, you can query the hour and minute like this:

rs.Open "Select * from tSollFahrten where datepart(hh, AbZeit)=12 and datepart(mi, abZeit)=53", CurrentProject.Connection, adOpenKeyset, adLockOptimistic