Using Access 2003 VBA functions through JET

Question

Hallo all.

I need to run the 'replace([column], [new], [old])' in a query executing on n Access 2003 DB. I know of all the equivalent stuff i could use in SQL, and believe me I would love to, but i don't have this option now. I'm trying to do a query where all the alpha chars are stripped out of a column ie. '(111) 111-1111' simply becomes '1111111111'. I can also write an awsum custom VBA function and execute the query using this, but once again, can't use these functions through JET. Any ideas?

---

Thanx for the replies guys. Ok let me clarify the situation. I'm running an .NET web application. This app uses an Access 2003 db. Im trying to do an upgrade where I incorporate a type of search page. This page executes a query like: SELECT * FROM [table] WHERE replace([telnumber], '-', '') LIKE '1234567890'. The problem is that there are many records in the [telnumber] column that has alpha chars in, for instance '(123) 123-1234'. This i need to filter out before i do the comparison. So the query using a built in VBA function executes fine when i run the query in a testing environment IN ACCESS, but when i run the query from my web app, it throws an exception stating something like "Replace function not found". Any ideas?

Answer 1

Your question is a little unclear, but Access does allow you to use VBA functions in Queries. It is perfectly legal in Access to do this:

SELECT replace(mycolumn,'x','y') FROM myTable

It may not perform as well as a query without such functions embedded, but it will work.

Also, if it is a one off query and you don't have concerns about locking a bunch of rows from other users who are working in the system, you can also get away with just opening the table and doing a find and replace with Control-H.

Answer 2

As JohnFx already said, using VBA functions (no matter if built in or written by yourself) should work.

If you can't get it to work with the VBA function in the query (for whatever reason), maybe doing it all per code would be an option?
If it's a one-time action and/or not performance critical, you could just load the whole table in a Recordset, loop through it and do your replacing separately for each row.

---

EDIT:

Okay, it's a completely different thing when you query an Access database from a .net application.
In this case it's not possible to use any built-in or self-written VBA functions, because .net doesn't know them. No way.

So, what other options do we have?
If I understood you correctly, this is not a one-time action...you need to do this replacing stuff every time someone uses your search page, correct?

In this case I would do something completely different.
Even if doing the replace in the query would work, performance wise it's not the best option because it will likely slow down your database.

If you don't write that often to your database, but do a lot of reads (which seems to be the case according to your description), I would do the following:

• Add a column "TelNumberSearch" to your table
• Every time when you save a record, you save the phone number in the "TelNumber" column, and you do the replacing on the phone number and save the stripped number in the "TelNumberSearch" column

--> When you do a search, you already have the TelNumberSearch column with all the stripped numbers...no need to strip them again for every single search. And you still have the column with the original number (with alpha chars) for displaying purposes.

Of course you need to fill the new column once, but this is a one-time action, so looping through the records and doing a separate replace for each one would be okay in this case.

Answer 3

Based on the sample query from your comment, I wonder if it could be "good enough" to rewrite your match pattern using wildcards to account for the possible non-digit characters?

SELECT * FROM [table] WHERE telnumber LIKE '*123*456*7890'