SQL syntax for Access Pass Through queries (ODBC)

[mrazanaqvee]

I have been using Access 2003 for a while now retreiving data through ODBC
connection to a SQL 2005 server. Works fine most of the time but as soon as
the queries involve multiple Access tables/queries AND 'not simple' SQL
views, one loses the will to live because this arrangement simply fails.
Mostly ODBC times out and even if the interval is adjusted, records simply do
not come back even after 5-10 minutes of wait.
I've been trying the Pass-through queries lately, as a solution. Although I
have managed to grasp the basic SQL syntax, unfortunately this syntax between
Access and SQL differs. My question is whether someone can direct me to a
comprehensive resource/s which list syntax which will work in this situation
ie Pass through queries.

Thanks in advance

 

[Tom van Stiphout]

On Sun, 24 Jan 2010 14:41:01 -0800, mrazanaqvee

The word "passthrough" means that Access does not touch the SQL at
all, and passes it on to the back-end database (SQL Server in your
case) for processing. So your passthrough query can be any legal T-SQL
statement, and it is defined in SQL Server Books Online which installs
with the product.

-Tom.
Microsoft Access MVP

 

[mrazanaqvee]

Thanks for the reply Tom. Unfortunately I do not have access to SQL side of
things. All I have is Access.
So far I have been trying to use the SQL generated by Access in the
background. Major stumbling block has been differences in data recognition
between SQL and Access. eg Apparently # is not used by SQL for dates.
Even before this my queries fail because the view name (on sql) is not
recognised because it has got a space in [Microbiology Current]. I do not
know how to enclose a table name if it has a space.
ODBC help is useless. Can the SQL Server Books be accessed without an
installation?

Appreciate your help

 

[Douglas J. Steele]

You can download SQL Server 2008 BOL from

http://www.microsoft.com/downloads/...FamilyID=765433f7-0983-4d7a-b628-0a98145bcb97

See http://msdn.microsoft.com/en-us/library/ms166018(SQL.90).aspx for BOL
for SQL Server 2005.

SQL Server uses single quotes as the delimiter for dates:

WHERE TransactionDate BETWEEN '2009-12-01' AND '2009-12-31'

To be honest, I didn't think SQL Server allowed spaces in names, so I'm
surprised that you'd have a view with spaces in it.

 

[Brendan Reynolds]

Even before this my queries fail because the view name (on sql) is not
recognised because it has got a space in [Microbiology Current]. I do not
know how to enclose a table name if it has a space.

Enclosing the name of the view in square brackets works for me in SQL Server
Management Studio (2008). I haven't tested it via ODBC but would expect that
to work too. Here's the test SQL that worked for me ...

select * from [test view]