[AccessD] A2K and .Net

Griffiths, Richard R.Griffiths at bury.gov.uk
Wed Jul 6 04:06:21 CDT 2005


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




More information about the AccessD mailing list