473,320 Members | 1,930 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
1 6891
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
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...
22
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...
4
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...
2
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...
5
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...
8
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...
3
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,...
2
hyperpau
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...
0
hyperpau
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.