calling VB procedure from Access2000 query

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

if you make the function public

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

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

sorry dovholuk you got there before me !

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

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

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

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

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

Thanks very much!!
Now I remember that test is not valid name for the function !!! :)

all set then?

dovholuk

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

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

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

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

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

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!

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

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

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