StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMS Access CREATE PROCEDURE Or use Access Macro in .NET
text
Body
copied!<p>I need to be able to run a query such as </p> <pre><code>SELECT * FROM atable WHERE MyFunc(afield) = "some text" </code></pre> <p>I've written MyFunc in a VB module but the query results in "Undefined function 'MyFunc' in expression." when executed from .NET</p> <p>From what I've read so far, functions in Access VB modules aren't available in .NET due to security concerns. There isn't much information on the subject but this avenue seems like a daed end.</p> <p>The other possibility is through the CREATE PROCEDURE statement which also has precious little documentation: <a href="http://msdn.microsoft.com/en-us/library/bb177892%28v=office.12%29.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/bb177892%28v=office.12%29.aspx</a></p> <p>The following code does work and creates a query in Access:</p> <pre><code>CREATE PROCEDURE test AS SELECT * FROM atable </code></pre> <p>However I need more than just a simple select statement - I need several lines of VB code.</p> <p>While experimenting with the CREATE PROCEDURE statement, I executed the following code:</p> <pre><code>CREATE PROCEDURE test AS </code></pre> <p>Which produced the error "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."</p> <p>This seems to indicate that there's a SQL 'PROCEDURE' statement, so then I tried</p> <pre><code>CREATE PROCEDURE TEST AS PROCEDURE </code></pre> <p>Which resulted in "Syntax error in PROCEDURE clause."</p> <p>I can't find any information on the SQL 'PROCEDURE' statement - maybe I'm just reading the error message incorrectly and there's no such beast. I've spent some time experimenting with the statement but I can't get any further.</p> <p>In response to the suggestions to add a field to store the value, I'll expand on my requirements:</p> <p>I have two scenarios where I need this functionality.</p> <p>In the first scenario, I needed to enable the user to search on the soundex of a field and since there's no soundex SQL function in Access I added a field to store the soundex value for every field in every table where the user wants to be able to search for a record that "soundes like" an entered value. I update the soundex value whenever the parent field value changes. It's a fair bit of overhead but I considered it necessary in this instance.</p> <p>For the second scenario, I want to normalize the spacing of a space-concatenation of field values and optionally strip out user-defined characters. I can come very close to acheiving the desired value with a combination of TRIM and REPLACE functions. The value would only differ if three or more spaces appeared between words in the value of one of the fields (an unlikely scenario). It's hard to justify the overhead of an extra field on every field in every table where this functionality is needed. Unless I get specific feedback from users about the issue of extra spaces, I'll stick with the TRIM & REPLACE value.</p> <p>My application is database agnostic (or just not very religious... I support 7). I wrote a UDF for each of the other 6 databases that does the space normalization and character stripping much more efficiently than the built-in database functions. It really annoys me that I can write the UDF in Access as a VB macro and use that macro within Access but I can't use it from .NET.</p> <p>I do need to be able to index on the value, so pulling the entire column(s) into .NET and then performing my calculation won't work.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload