Custom Function (VBA) in Access Stored Query, Called from ASP.net

[Guest]

Hi everyone.

I have read every page that Google returns on this topic, but can't find
anything that resolves my problem.

Basically, I have an Access Database that does a number of different
calculations. One of these calculations is rather complex, and could not be
implemented properly using just nested IIF statements or anything similar, so
was coded using VBA (in the VBE) as a function. This calculation is included
in a query in the database, which calculates a value based on the passed
parameters (a "parameter query"), with the function itself being called as
simply [Accrued]: CalcAccrd(Date,Coupon,Settlement,Maturity, etc..).

This works just fine in Access, with great resuts (i.e. gives right #s).
However, when I try and run this stored query (procedure?) in ASP.net -
ultimately where I had hoped it would reside - I get the error message at the
bottom of this question. In short, it does not recognize the function I try
to pass to it as existing when run from ASP.net.

Any help would be most appreciated. I have tried everything I can think of...

Thanks!

David

ASP.net Error Message
--------------------------

System.Data.OleDb.OleDbException: Undefined function 'JustATest' in
expression. at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet
dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at
ASP.plquery_aspx.BindData(String sFilter) in ...

 

[Sylvain Lafontaine]

Basically, you can divide an Access application into two parts: the
Graphical User Interface (GUI), which also include all VBA modules and
functions - and the Database part (tables, relations, etc.; also known as
DAO or the JET engine) which can be manipulated by the ODBC and OLEDB
drivers. The JET engine cannot run any VBA code; however, the GUI can run
JET, ask it to return all relevant records (which would mean *all* records
on many occasions) and then apply your VBA functions on the result.

The GUI part is only accessible from Access and cannot be manipulated
outside of it; which means that you cannot access your VBA code directly
from .NET; which also means that you cannot do (directly) what you want to
do. (The only possibility would be to open an Access application under IIS
on the server via COM Interoperability. However, not only this would be a
little complicated to realize but also would be a real performance hog when
executed on a web server under IIS.)

In brief, you cannot do that and you will have to recode your function in
VB.NET or C#. It is also at this stage that you can begin to see the
difference between an Access database (when used as a backend) and
SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Hi everyone.

I have read every page that Google returns on this topic, but can't find
anything that resolves my problem.

Basically, I have an Access Database that does a number of different
calculations. One of these calculations is rather complex, and could not
be
implemented properly using just nested IIF statements or anything similar,
so
was coded using VBA (in the VBE) as a function. This calculation is
included
in a query in the database, which calculates a value based on the passed
parameters (a "parameter query"), with the function itself being called as
simply [Accrued]: CalcAccrd(Date,Coupon,Settlement,Maturity, etc..).

This works just fine in Access, with great resuts (i.e. gives right #s).
However, when I try and run this stored query (procedure?) in ASP.net -
ultimately where I had hoped it would reside - I get the error message at
the
bottom of this question. In short, it does not recognize the function I
try
to pass to it as existing when run from ASP.net.

Any help would be most appreciated. I have tried everything I can think
of...

Thanks!

David

ASP.net Error Message
--------------------------

System.Data.OleDb.OleDbException: Undefined function 'JustATest' in
expression. at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet
dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at
ASP.plquery_aspx.BindData(String sFilter) in ...