MS Access, SQL and Java

[LightRaven]

I have defined a VBA function inside a module like this:

Public Function MyFunction() As Integer
MyFunction = 2
End Function

In my Java program i have the follow:
....
String query = "SELECT * FROM MyTable WHERE ID = MyFunction()";
....
statement.execu te(query);
.....

It is an MS-ACCESS database. I get the following error:
"function MyFunction is not defined" any idea why?

is it not enough to define the function in modules and then reference it from Java program in sql statemente

[sicarie]

I have defined a VBA function inside a module like this:

Public Function MyFunction() As Integer
MyFunction = 2
End Function

In my Java program i have the follow:
....
String query = "SELECT * FROM MyTable WHERE ID = MyFunction()";
....
statement.execu te(query);
.....

It is an MS-ACCESS database. I get the following error:
"function MyFunction is not defined" any idea why?

is it not enough to define the function in modules and then reference it from Java program in sql statemente

The variable query is read exactly as you see it - MyFunction() is never called as it is considered to be part of the string variable. You need something like (though I'm not sure exactly how to interface the VB to Java...)

String s_query = "SELECT * FROM MyTable WHERE ID = " + MyFunction();

That is, of course, assuming you can properly call a VBA function from your Java code without have to instantiate anything else, and that MyFunction() returns something of type String.

[BigDaddyLH]

I don't think you want to try to call Vb code from Java. Why not rewrite it in Java?

[LightRaven]

due I don't want to read the entire database for processing in java. I was hoping the for that SQL statement sent, it would try to look in the module section when it saw this function call.

[r035198x]

due I don't want to read the entire database for processing in java. I was hoping the for that SQL statement sent, it would try to look in the module section when it saw this function call.

How would it try to look in the module section? Do you know the structure of a Java program?

[BigDaddyLH]

I don't know what a "module section" is, since I don't touch VBA or ACCESS. Is it a NON-STANDARD part of the database? The only clean think I can think of is that JDBC lets you call stored procedures. If you design your database with an interface made of stored procedures, you're laughing:

http://java.sun.com/docs/books/tutorial/jdbc/index.html

Especially:

http://java.sun.com/docs/books/tutorial/jdbc/basics/sql.html