473,837 Members | 1,601 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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,Settleme nt,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.Ole Db.OleDbExcepti on: Undefined function 'JustATest' in
expression. at
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextErrorHandli ng(Int32 hr) at
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextForSingleRe sult(tagDBPARAM S
dbParams, Object& executeResult) at
System.Data.Ole Db.OleDbCommand .ExecuteCommand Text(Object& executeResult) at
System.Data.Ole Db.OleDbCommand .ExecuteCommand (CommandBehavio r behavior,
Object& executeResult) at
System.Data.Ole Db.OleDbCommand .ExecuteReaderI nternal(Command Behavior
behavior, String method) at
System.Data.Ole Db.OleDbCommand .ExecuteReader( CommandBehavior behavior) at
System.Data.Ole Db.OleDbCommand .System.Data.ID bCommand.Execut eReader(Command Behavior
behavior) at System.Data.Com mon.DbDataAdapt er.FillFromComm and(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Com mon.DbDataAdapt er.Fill(DataSet
dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) at
System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet) at
ASP.plquery_asp x.BindData(Stri ng sFilter) in ...

Jul 21 '05 #1
1 6963
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 Interoperabilit y. 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***@discussi ons.microsoft.c om> wrote in message
news:78******** *************** ***********@mic rosoft.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,Settleme nt,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.Ole Db.OleDbExcepti on: Undefined function 'JustATest' in
expression. at
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextErrorHandli ng(Int32 hr)
at
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextForSingleRe sult(tagDBPARAM S
dbParams, Object& executeResult) at
System.Data.Ole Db.OleDbCommand .ExecuteCommand Text(Object& executeResult)
at
System.Data.Ole Db.OleDbCommand .ExecuteCommand (CommandBehavio r behavior,
Object& executeResult) at
System.Data.Ole Db.OleDbCommand .ExecuteReaderI nternal(Command Behavior
behavior, String method) at
System.Data.Ole Db.OleDbCommand .ExecuteReader( CommandBehavior behavior) at
System.Data.Ole Db.OleDbCommand .System.Data.ID bCommand.Execut eReader(Command Behavior
behavior) at System.Data.Com mon.DbDataAdapt er.FillFromComm and(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Com mon.DbDataAdapt er.Fill(DataSet
dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) at
System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet) at
ASP.plquery_asp x.BindData(Stri ng sFilter) in ...

Jul 21 '05 #2

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

Similar topics

5
19328
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 MyCriteria() Select Case Forms!MyForm!MyCbx Case 1 My Criteria = "<0" Case 2 MyCriteria = "0"
22
3821
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 am now thinking about upgrading the database from Access to SQL Server. If I do, how can I implement the custom row functions? Is Visual Basic integrated with SQL Server just as it is with Access? Or does T-SQL in SQL Server offer the...
4
1948
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 and other kind of data. I'm using MS-VC++6.0-Enterprise (with sp6) at NT4-Workstation (sp6a) and MS-Access97 (from MS-Office97). Reason that I won't upgrade to latest Office/VisualStudio is that I must ensure that any application I develop still...
2
489
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 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...
5
2418
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 assign_i from dbo_assign a where a.scenario_i=1 and a.assign_i = TestMyFunction(FundedPositions.PositionNumber))AS
8
11266
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 using this method with store procs that dont return a value back to Access. Hope this makes sense. Set Cmd = New Command With Cmd Set .ActiveConnection = get_XE_Conn 'makes a connection Oracle XE
3
5118
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, col2) FROM TABLE1 (real query is complex, using unions, etc, but I need one column in
2
19507
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 will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge. I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that...
0
2897
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 will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge. I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10881
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10275
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9406
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7004
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5670
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5850
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4475
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4043
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.