[AccessD] A2K and .Net

Sub queries are another solution that can provide criteria for queries in
cases where functions won't work. For example, I use a sub query to fetch
current month and year from a period table.
Jim Hale

-----Original Message-----
From: Ken Ismert [mailto:KIsmert at texassystems.com]
Sent: Tuesday, July 05, 2005 12:38 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] A2K and .Net


Yes, they will fail. This is not unique to .NET. Simply put, if you open
a Jet database using DAO, ADO, or ADO.NET, any queries that reference
VBA functions like DateAdd will fail. 

Your earlier thinking was correct: to get DateAdd to work in a query,
you would need Access installed on the machine, and would have to open
it via automation. The Access application instance would then provide
the VBA environment required to make sense of VBA function calls.

.NET won't interpret the function calls, nor can you substitute .NET
functions. This is because your ADO calls are going to a separate Jet
server instance, which has no knowledge of the context in which it is
called. 

In short, you are limited to native Jet SQL for your queries. This
includes the aggregate functions like Sum and Avg, mathematical
operators and string concatenation, and the expressions Between, In and
Like. You may also be able to extend your reach by using the ODBC Scalar
functions, although I haven't tried this.

-Ken


-----Original Message-----
From: Griffiths, Richard [mailto:R.Griffiths at bury.gov.uk] 
Sent: Tuesday, July 05, 2005 8:21 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] A2K and .Net

Do you know if my queries (stored procedures) that use say dateadd (ie
hard coded into the query) will fail?

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.