[AccessD] A2K and .Net

Hi
<Is there a reason you are reluctant to change the queries?  >
No, just I was struggling to get the correct SQL criteria to work
without using DateAdd function or format function
Richard

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
Sent: 06 July 2005 16:33
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] A2K and .Net


If you want to use Access functions, you need to be *in* Access.
However, if the environment doesn't support those functions, they won't
work anyhow.  Left, Mid, etc., are VBA functions, not Access functions,
and trying to use VBA functions in managed code in .Net can give you
entirely unexpected results even when you don't get an outright error.
Is there a reason you are reluctant to change the queries?  

Charlotte Foust


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


Ken
thanks, as I thought. I accept that you would need to install all the
VBA dlls, but are you sure that you would need to instantiate an Access
session.  I'm sure most apps would use left, mid etc in queries and this
would mean say for all the many 1000's of VB apps that have an Access BE
they would need to install Access (runtime or full) and load an instance
each time a query was used.  Have you tried this?  
 
I have tried to use native JetSQL but for this query have struggled ,
maybe someone can offer a solution.......
 
 
I have two datetime fields UnavailableFrom and UnavailableTo (e.g.
01/01/2005 08:30 and 01/01/2005 18:30)
 
Can anyone suggest any SQL (and also native JetSQL without function
calls [or with permitted function calls]) to find whether a date falls
between these two datetimes - so a parameter of say 01/01/2005 would
return this record.
 
Thanks
Richard

________________________________

From: accessd-bounces at databaseadvisors.com on behalf of Ken Ismert
Sent: Tue 05/07/2005 18:38
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


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