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

David

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.ExecuteCommandTextE rrorHandling(Int32 hr) at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior,
Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior behavior) at
System.Data.OleDb.OleDbCommand.System.Data.IDbComm and.ExecuteReader(CommandBehavior
behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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 ...

Jul 21 '05 #1

Subscribe Post Reply

6901

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
"David" <Da***@discussions.microsoft.com> wrote in message
news:78**********************************@microsof t.com...

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.ExecuteCommandTextE rrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior,
Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior behavior) at
System.Data.OleDb.OleDbCommand.System.Data.IDbComm and.ExecuteReader(CommandBehavior
behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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 ...

Jul 21 '05 #2

Similar topics

Function In Query Criteria

by: Steve | last post by:

I need help with a function to be used as the criteria for a query. The field in the query is InventoryStatus: - . The function is built around a Select Case set of about twelve cases. Function...

Microsoft Access / VBA

Custom Row Functions: Access vs. SQL Server

by: TC | last post by:

I have an Access database application with a lot of custom row functions written in VBA. In other words, a lot of queries contain calculated fields which use functions defined in the modules. I...

Microsoft Access / VBA

How to handle custom-messages in VBA from a custom DLL

by: LhK-Soft | last post by:

Hi, I'm active in VC++ for several years now, so I know some things (I think). Using MS-Access I store a db with lots of entries and uses the VBA techniques to export those data to e.g. HTML, XML...

.NET Framework

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

by: David | last post by:

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...

.NET Framework

Why does a VBA function call from a sub-query cause an infinite loop?

by: marcsirois | last post by:

I have an SQL Query that looks like this. I simplified it for the purpose of this example, but it's still a runnable query SELECT TOP 1 FundedPositions.PositionNumber AS , (select top 1...

Microsoft Access / VBA

return Oracle stored function value back to Access VBA

by: colmkav | last post by:

Can someone tell me how I can access the return value of a function called from Oracle as opposed to a store proc from oracle? my oracle function is get_num_dates_varposfile. I am only used to...

Microsoft Access / VBA

calling access vba function from asp?

by: Alain Bourgeois | last post by:

Dear all, I have an asp module connecting to a MS-access database. I have a vb function MKDate(date, time) in a vba module of this database. I would like to query : SELECT MKDATE(col1,...

ASP / Active Server Pages

Custom Error Messages in Access

by: hyperpau | last post by:

Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I...

Visual Basic 4 / 5 / 6

Custom Error Messages

by: hyperpau | last post by:

Microsoft Access / VBA

Cloud Servers without Credit Card and Email Registration: A Simpler Way to Get on the Cloud

by: CloudSolutions | last post by:

Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...

General

Access Europe: Command bars, the Access Shortcut Tool and a simple Audit Log - Wed 3 April

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

General

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware