Run Code in Background

[worldwidewall]

Hello

I am using Access 2003.

I have a form and on it a button that runs some VBA code. The VBA code can take a while to execute. I would like my user to be able to continue using the form while the code runs in the background. As opposed to what happens right now which is the form becoming frozen while the code runs.

Thanks

 

[Galaxiom]

Access VBA only supports a single thread so unfortunately there is no way to do it within the same application.

You could open a separate hidden Access session and trigger code to run in that but that is not a very elegant solution.

A better way is to put the backend on a database server like MS SQL Server. Then you can pass the command to the server for execution. Not only will it let you do other taks while it is processing, the task will probably take a tiny fraction of the time required by Access.

 

[worldwidewall]

Galaxiom

Thanks for the suggestion, I have been meaning to switch to mysql for a while so I did. The operation takes even longer now.

I know this is not a mysql forum but since we started in access perhaps you can help?

I have 1 table with about 9 million records. The first column is id which is an auto_increment primary key. The second column is CHAR(14) it contains only 14 digit text codes. I have to search through the codes to see if there is a match. In access I was using Dlookup and for some searches it is faster than using a select query in SQL. I need to be able to search for full or partial codes including wild cards. It takes about 8 seconds for the longest search I have run. This needs to be closer to 1 second. Any pointers?

I have read through indexing and I don't think that will help me?

Thanks

 

[Galaxiom]

Unfortunately I don't have any experience with MySQL so anything I suggest will be based on Access and MS SQL Server.

I don't know if MySQL supports an equivalent of stored procedures but that is what would provide the fastest performance with the least work in MS SQL Server.

Indexes wouldn't help with partial searches from the middle of the values although they can when the search is from the first character. I assume this is what you have already determined.

The ultimate way to speed up partial searches is to create a table of partial strings from the data and perform the search against that. Basically that is what search engines do with web pages. It can be practical if the data is not often changed but it is a substantial overhead to keep that index up to date. As you can imagine there would ba a lot of records and potential some complex processes to optimise the search.

The partial string index table should have an ID field that is related to a join table with a record for each of the the main table's record IDs where that string occurs. You can make use of the database's own indexes to search from the beginning of the strings in the partial string index table.

The partial string index would be maintained by a stored procedure run from a trigger when any record changes or new records are appended.

 

[worldwidewall]

Galaxiom

Thanks for your response. There would be way to many combinations for my to partial strings table. I will look at the stored procedures.

Thanks

 

[Galaxiom]

There would be way to many combinations for my to partial strings table.

It isn't anywhere near as bad as it sounds at first.

You won't want it case sensitive so you are are only looking for 70 characters even if you include all punctuation, space and special characters.

The choice of the number of characters in the partial strings stored is a trade off. The more characters in the substring the more unique combinations that need to be stored but the less the number of records in the join table.

Taking the simplest as an example (the two character substring) there are only 4900 possible two character sequences with 70 different characters.

The process begins by finding all entries that include the first two characters of the search string. The crudest implimentation then checks characters 2 and 3, then 3 and 4 and so on. Only the records which match all those results are possible matches to the full search string.

Using three character substring will vastly reduce the number of matches and only increase the maximum number of substring records to 343K even if every possible combination existed. The real figure will be much less.

There will be many false positives but after a few iterations the whole test string can be applied to the reduced result set. How many iterations depends on the number of character in the record being searched.

An improved algorith stores the substring position in the join table and uses this to determine matches much more quickly. In the case of the two character window it looks at (1,2);(3,4) etc and analyses the character positions returned for the appropriate sequence spacing.

The database index can be used so the searches are very fast and a very small number of iterations will quickly reduce the possible matches.

Feed algorithm that into a Stored Procedure and you have a very rapid search that is potentially capable of matching in the OnChange event of the search box depending on the number of users and the grunt available at the server.

 

[worldwidewall]

Wow, you are defintely over my head. But I will try to figure it out and work through it, it sounds promising. Thanks.

 

[sparks80]

Hi,

In addition to the advice above it is worth taking a look at DoEvents.

A word of warning though is to be careful about how many times you call DoEvents. It can be useful to prevent the application from hanging. But if it is called many times then it can cause the computer to hang, or cause an "out of stack" error.

 

[worldwidewall]

Hi.

I started down the road you were suggesting. And realized that the column I was searching on was not indexed. I thought because I had a primary key and only one other column that was the same as having the column indexed. When I added an index to the column itself the searches drop to less that one second.

Thanks for you help.

 

[gemma-the-husky]

i doubt if indexing a text column would help you find stuff in the middle of the string anyway

i expect the techniques galaxiom is describing must be heading towards the sort of context searches that search engines use.

advanced stuff.

 

[VilaRestal]

I got the impression world is not even searching for a substring.

One thing I'd like to add: I find it's good to write your own domain aggregate functions, that pass parameters to stored procedures or udfs. If you call them something similar to the regular ones (say DLookup2()) and have the same arguments in the same order you can effectively find and replace them all and use yours in exactly the same way. The difference being yours will be immensely faster and perhaps more versatile too.

So, for example:

Public Function DCount2(ByVal strField As String, ByVal strTable As String, Optional ByVal strFilter As String = "") As Long
    Dim s As String
    s = "SELECT COUNT(" & strField & ") FROM " & strTable
    If strFilter = "" Then
        DLookup2 = con.Execute(s)(0)
    Else
        DLookup2 = con.Execute(s & " WHERE " & strFilter)(0)
    End If
End Function

Doesn't use SPs or UDFs (and could be improved by doing so) but still much faster than the regular DCount as it will be server-side processing. (And more versatile because you can use subqueries for the table.)

 

[worldwidewall]

gemma-the husky

You are correct. I wasn't searching for mid string at the time, it still takes up to 8 seconds if I search mid string. But I think I have a way around that now.

Dave

 

[gemma-the-husky]

out of interest - if you are running a "looped" code, adding doevents somewhere in the loop will let you do other things at the same time.

it's just that a bulk access query is "atomic" -you cannot put a doevents inside it.

(if there was a function call you know that query is making, maybe a doevents INSIDE that function would be useful)