Solved

calling VB procedure from Access2000 query

Posted on 2002-04-05
20
217 Views
Last Modified: 2012-05-04
is it possible to create a query in Access2000 that calls VB procedure inside some module within the same database

0
Comment
Question by:BobSamonik
  • 8
  • 8
  • 4
20 Comments
 
LVL 8

Expert Comment

by:dovholuk
ID: 6920643
sure. the question is if that's what you really want to do or not though. as it will usually run once for every record in your query.

all you do is make a function in a module that returns some value. such as:

Function MyTest(someText as variant)
    MyTest = lcase(nz(sometext))
end function

(this is a very SIMPLE example of course)

then all you do is in your criteria row you put something like:

MyTest(MyTable.Field1)

and run your query. access will run the method once for ever record it finds.

the SQL behind this query would look like:

SELECT MyTest(Field1) FROM MyTable

(if you wanted to know...)

dovholuk
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6920660
if you make the function public

Public Function myFunction() As String
    myFunction = "Hello World"
End Function

SELECT YourTable.YourField, myfunction() AS Expr1
FROM YourTable;
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6920663
sorry dovholuk you got there before me !
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:BobSamonik
ID: 6920713
ok
now i see that i was not clear enough although this could work too

I wanted to create procedure , not select query.
I said 'Query' as opposed to 'Table' or some other Access Object as seen on Objects bar

So inside that Procedure a would call vb function inside a module
BTW I get 'undefined function test' error

Public Function test() As String
test = "qw"
End Function

select test() from table
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6920803
ok, so now i'm more confused...

what are you trying to accomplish? when you say "procedure" what do you mean? do you mean an access query, or a "stored procedure" (in an .adp file)?

if you're using access to sql server, you can't use a local vb function without creating two queries. one query will be the "stored procedure" and the other query (an access based query) would select * from that "stored procedure" and run the local access method on the data set.

hope this helps...

dovholuk
0
 

Author Comment

by:BobSamonik
ID: 6921511
Not 'stored procedure' in .adp file
It would be like:
***
PROCEDURE MyProcedure;
call to vb function;
***
written inside MS Access object called 'Query' and named MyProc

which I would call from outside VB program using RDO
Cn.OpenResultset('{call MyProc()}');

but since you brought the idea of calling VB function inside Select query that would work too.
Cn.OpenResultset('{call MyProc()}');
MyProc beeing:
select MyProcedure()
and MyProcedure an VB function in a module

so lets focus on getting your suggestion to work
As I said I get 'undefined function test' error
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6921560
i think i'm understanding better... i would guess that you've used oracle (or another non MS RDB) in the past with the whole "procedure" syntax. i didn't pick up on that.

the reason your "procedure" isn't working is due to the way access was designed... you NEED to reference at least ONE table in your sql "procedure....

try this set of instructions:

1.) create a new table with one field name MyTable and MyField (column type isn't important, this is just a demo) put ONE and ONLY ONE row into this table... it can be anything, we'll NEVER EVER use it... (you'll see)

2.) create a new function (you've done this)

3.) create a new query called MyQuery. set MyQuery's sql to this:

SELECT Test() as TestColumn FROM MyTable

run the query. you SHOULD get one column and one row back that says "qw"...

that should get you going... let me know if that works for you.

-dovholuk
0
 

Author Comment

by:BobSamonik
ID: 6922582
dovholuk
Did you try it and did it work??
cos I keep getting the same error
I used another table that has more than one row but I doubt it can be the reason why access does not recognize my function

The best thing is I did this a while ago and it worked
Back than different question was in question, pardon my expresion
0
 
LVL 8

Accepted Solution

by:
dovholuk earned 200 total points
ID: 6923859
BobSamonik,

yes i did test it, but i tested it in access 97... so i tried it in a2k (i have a2k at home) and sure enough... it DIDN'T work!!!! the reason it didn't work is just plain stupid though... for some reason, the procedure name "Test" is NOT a method name that will work. if you change it to MyTest, it should work just fine...

VERY VERY VERY STRANGE!!!!

i've put a sample db at http://www.geocities.com/dovholuk/bobsamonik.mdb

for you to look at.

enjoy! :)

dovholuk
0
 

Author Comment

by:BobSamonik
ID: 6925068
Thanks very much!!
Now I remember that test is not valid name for the function !!! :)
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6925147
all set then?

dovholuk
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6925317
stranger still is the fact that i ran into the very same problem when i was constructing my example for BobSamonik.
i just didnt spot it !!!
0
 

Author Comment

by:BobSamonik
ID: 6925865
dovholuk
Unfortunately not.
I will have to stick to my original idea of writeing a PROCEDURE and calling VB function within it.
If you cant answer this I will award you points and ask question again.
If you can I will raise stake to 300 points!!

0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6926095
do you know how to call the procedure from access as is?

for instance, have you created some sort of library in VB and used the references to refer to the library and tested the routines?

if you know how to call the VB procedure from an ACCESS procedure, just wrap your VB procedure inside an access procedure and call it a day.

for example. i created an activex.dll file called Project1, with a class name of Class1 and the following code:

Function StrLen(str As String) As Long
     StrLen = Len(str)
End Function

and compiled it.

i then made a reference to the .dll file from a new module in access.

i then called the module using this bit of code:

Function TestLen(str As Variant) As Long

     Dim a As Class1

     Set a = New Class1
     TestLen = a.StrLen(Nz(str))
End Function

this worked just fine for me. it'll return the length of whatever you pass it...

if you want the example file (compiled .dll), just let me know...

dovholuk
0
 

Author Comment

by:BobSamonik
ID: 6926336
I am quoteing my answer to one of your previous questions:

Not 'stored procedure' in .adp file
It would be like:
***
PROCEDURE MyProcedure;
call to vb function;
***
written inside MS Access object called 'Query' and named MyProc

which I would call from outside VB program using RDO
Cn.OpenResultset('{call MyProc()}');

So I am not talkeing about VB Procedures but lets say SQL PROCEDURE that calls VB Procedure
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6929099
bobsamonik,

i STILL say you're contradicting yourself with your statements.  first you say, "not 'stored procedure' in .adp file" then you say, "written inside MS Access object called 'Query'".

i'd really like to be able to help you out, but i have NO IDEA what you're trying to do.  

Q.) why do you need to make a call to a vb procedure? can't you just return the result set and work from it as needed? why is it necessary for the query to use a custom vb method?

i'm sorry i can't give you any more info. i don't really know what you're trying to do or why... this is the best info i can give you now... again, sorry...

dovholuk
0
 

Author Comment

by:BobSamonik
ID: 6929675
ok
open new Query and instead of typeing SELECT * from table
just type SELCT then try to execute the query.

You will get the message:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'

See that PROCEDURE?
Thats the PROCEDURE that I want to write!

0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6930011
ok bobsamonik....

i tried it in access 2000, the error message i get is:
"The SELECT statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect."

when i try "SELECT * from table" i get this error message:
"Syntax error in FROM clause"

so.... again... i ask you, WHAT are you trying to accomplish? what is your final goal? why are you trying to run this mysterious "procedure"?

if you could tell me WHAT you're trying to do, i can help.  are you just trying to learn about "procedures"? is that it? you just want to write any ol' procedure?

i'm lost here...

i think that you are taking the error message far too literally. you NEED to include one of the reserved words SELECT UPDATE DELETE... there's NO WAY around that fact.

good luck. if you can give me yet more information, i might be able to help you, but with the info i have now... i can't.

dovholuk
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6930758
BobSamonik :

***************************************
See that PROCEDURE?
Thats the PROCEDURE that I want to write!
**************************************
do you want to write an SQL Select statement ?
you want to write a query with 'SELCT' as the only piece of SQL in the query ???










0
 

Author Comment

by:BobSamonik
ID: 6931296
PROCEDURE Clause
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/F1/D2/S5A332.asp

The answer is:
It is imposible to call VB subroutine from such PROCEDURE because it accepts only one SQL statement (SELECT , DELETE , INSERT, UPDATE)

dovholuk gets the points
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question