Solved

calling VB procedure from Access2000 query

Posted on 2002-04-05
20
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

622 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