Solved

calling VB procedure from Access2000 query

Posted on 2002-04-05
20
213 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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now