Date() in query runs extremely slow on Windows 7

[TagYoureIt]

I'm not sure if this is an Access issue or a Windows 7 issue. I've recently had my desktop at work upgraded from Windows XP to Windows 7. Now all my queries that use the date() function have come to a crawl. I query that would take 5 seconds before (and still does on my laptop that has not yet been upgraded) easily takes over a minute. I've narrowed it down to whether or not I use date(). Using a criteria of 03/05/201 is 3 on the date Activity_Date field is fine, it takes just a second or two to run. If I use date()-10 instead, it easily takes over a minute.

Since the company has greatly restricted access on machines that have migrated to Windows 7 we believe it might be related to that (myself and the IT guy I'm working with), however we have no idea what would cause things to slow down when using date(). We have tried having the IT guy logging in since he has admin rights, but that has made no difference.



Any suggestions?

EDIT: This problem does not occur if querying a locally stored table, it happens when querying a OBDC linked table.

[June7]

Is it an issue if you use Date() or when using Date() in an expression like you show in post?

Is your computer new with Windows7 or was it an old computer with software upgrade?

Have you tried on another Windows7 machine?

[TagYoureIt]

I tried just having date() as an additional field and it still runs extremely slow.

I've tried it on three different Windows 7 machines. My desktop was initially running Windows XP and upgraded to Windows 7. Since we thought the migration may be part of the problem, we wiped the drive and did a fresh install of Windows 7.

After several hours with IT remoted in to my machine, we came up with a work around. If I set the criteria to "> #01/01/2013# and > date() - 10" then it runs fine. Again we don't know why we need to do this on Windows 7 with Access 2010 but not on Windows XP with Access 2010.

[June7]

Very interesting. We are starting to get Windows7 in our office, all new machines have it. So far not many quirks. I have one new machine that one piece of code doesn't work on but on an even newer machine it is fine. Bizarre!

[TagYoureIt]

Well it seems to be specific to when I'm pulling data from an ODBC linked table. Pulling data from local tables is fine. I even tested where I had a linked table and a local table together with a relationship between them and used the Date() as a criteria on the local table and it ran fine. I have even tried multiple versions of Oracle to see if there was something with the drivers.