Access ODBC to SQL prob

maincomputer

New member
Local time
Today, 06:42
Joined
Jun 22, 2012
Messages
9
Hi

Complete n00b here so please be gentle.

I run a query to an SQL Server across our network from Access. My query has date criteria: between #date# and #date#.

I use this every day so wanted to get a user-input date with: between [start date] and [end date].

This causes an ODBC connection fail, however. I've tried every date format possible and I'm completely confused as to why it won't work.

Could anyone help, please?
 
Did you set up a pass through query or as a regular select query on a linked table?
 
A regular select query on a linked table.
 
Are you absolutely sure that the field in SQL Server is specifically set as a date/time field?

What happens if you just open the linked table? Does it open okay?
 
Main,

Over an ADO connection, use single-quotes as a delimiter instead of the "#".

Also, use "%" instead of "*" as the wildcard.

hth,
Wayne
 
Main,

Over an ADO connection, use single-quotes as a delimiter instead of the "#".

Also, use "%" instead of "*" as the wildcard.

hth,
Wayne
Doesn't sound like an ADO connection to me. ODBC SQL driver doesn't use ADO for linked tables.
 
Did you try sending the date as #YYYY/MM/DD#. Also try using < and > rather than between and double check that in SQL these are datatime and not date fields (date fields can cause problems with some versions of MDAC).
 
Thanks for coming back to this. Yes, I've tried using all those methods. Querying normally works fine, but if I try to use user input methods, either in the query [], or by using a form, I still get the ODBC error.

The SQL warehouse stores the dates as HUGEDATE format. I guess that is the problem, but it cannot be changed.
 
Thanks for coming back to this. Yes, I've tried using all those methods. Querying normally works fine, but if I try to use user input methods, either in the query [], or by using a form, I still get the ODBC error.

The SQL warehouse stores the dates as HUGEDATE format. I guess that is the problem, but it cannot be changed.

Which Operating System is your computer on and which SQL Server version are you using? The reason I ask is that the ODBC Driver you use may need to be different from the one you are currently using depending on the answers you give to those questions.
 
I'm on Windows XP, Office 2003. Not sure which SQL Server version we're connecting to, sorry. It's housed in a different location to my workplace, and the DBAs are "very busy" and not very responsive to emails (they won't even accept phone calls!).
 
I'm on Windows XP, Office 2003. Not sure which SQL Server version we're connecting to, sorry. It's housed in a different location to my workplace, and the DBAs are "very busy" and not very responsive to emails (they won't even accept phone calls!).
Well, it is very important you know which SQL Server version they are using. So find out. The reason? The ODBC driver you are using may not be the one that should be used.

So, for example -

If you are on Windows XP, and the SQL Server in use is 2000, then you can use the simple SQL Server ODBC driver that is listed as SQL Server.

If you are on Windows XP, and the SQL Server in use is 2005, and they are using the same datatypes and options that were available in SQL Server 2000, then you can use the simple SQL Server ODBC driver. But if they are using any new 2005 features (I believe HugeDate may be one of those), then you would need to use the ODBC driver named SQL Server Native Client.

If you are on Windows XP, and the SQL Server in use is 2008, and they are using the same datatypes and options that were available in SQL Server 2000, then you can still use the simple SQL Server ODBC driver. But if they have used any newer 2008 features then you would need to use the ODBC driver named SQL Native Client 10.

If you are on Windows Vista or Windows 7 and using SQL Server 2000 or 2005 then you can use the simple SQL Server driver.

If you are on Windows Vista or Windows 7 and using SQL Server 2008, then you need to use the SQL Native Client 10 driver.

If you are on Windows 7 SP1, then you can use the simple SQL Server ODBC driver for any of them.


Does that show how important it is that you know what your platforms are?
 

Users who are viewing this thread

Back
Top Bottom