Validating/checking a value (Word / Access 97 VBA)

Topic

New Reply

Using VBA in Word 97 I have a process that reads data from an Access Database whci I then want to compare with a given value to see if the given value is in the list. Currently,
1) The the database values are stored in an array
2) A FOR-NEXT loop then cycles through each of the array values to see if there is a match with the given value. If found then TRUE, if not then FALSE.

As the list gets bigger the FOR-NEXT method would take more time.

Does anyone have any suggestions for a more efficient way to do such a compare?

many thanks for any suggestions,
otk

Reply | Quote

Replies

Could you require a match on that value in your database query? First question, how are you getting the Access data and if you are reading a whole table, is there any reason you could not use a query instead?

Reply | Quote

jscher2000,
OK, that approaches it from the other angle and should work – be interesting what the overhead doing it this way would be i.e. would be using query many times as opposed to reading all values at start and cycling through them…as initially posted.

I may not have my terms right but I was looking to see if an “array variable” could be populated with the values and be tested something like.

testtext=”A”
if testtext IN Array(“A”,”B”,”C”)

Where,
“A”,”B”,”C” are the values read from the database
IN is a term used just to illustrate a union type function.

In the end if performance is not an issue then your suggestion of turning it around will suit me perfectly.

Thanks,
otk

Reply | Quote

I suppose it’s only fair that I tackle your original question, then. In brief, I don’t know of a way to get all the field data out of a recordset other than one record at a time. As long as you have to examine each record anyway, it seems easiest to compare immediately rather than using an intermediate array.

Well, actually now that I think about it, there is the GetString method of an ADO recordset which will jam all of the data into a single large string. You can choose your own field delimiter and search for a match including your delimiter (to make sure it isn’t part of some other field). For example, follwing this syntax:

strTemp = myRS.GetString(,, ColumnDelimiter, RowDelimiter, SwapForNullValue)

you could use:

strTemp = myRS.GetString(,, “!”, “@”, “#”)

and your string would look like this:

rec1field1!rec1field2!rec1field3@rec2field1!rec2field2!rec2field3@ etc.

Reply | Quote

Thanks jscher2000 , I’ll investigate.

otk

Reply | Quote

Hi,
Are you looking for the value in a specific field (you didn’t mention how many fields you were returning – one or many)? If so, you could use the recordset’s findfirst method.
Hope that helps.

Reply | Quote

rory,
Tks for feedback, pls see reply to Kevin

otk

Reply | Quote

Can you “filter” the recordset by creating a query that does not return so many rows?

Reply | Quote

Kevin,
I think it best I outline more specifically whats happenning.
1) From Word I do a search of all files in a nominated folder. Then I load each filename into an array.
2) In Access I have a list of file types (extensions). From Word I read this list at the start (once) and populate an array – I now have an array of extensions that I want to learn more about.
3) For each file found I test using a loop against the valus found in (2). If there is a match then get some properties, If not then move onto the next.

Essentially, I wanted to see if there was a more efficient way to do step (3) e.g. rather than doing a loop (until found or EOF), use a single statement, maybe like MATCH in Excel 97. In english this would be – If filefound has a MATCH in current list then TRUE else FALSE.

If I adopt the the initial reply by jscher2000 I would not bother reading the list from Access but testing each file found using a filter query against the database. While that would work, I am not sure about the performance implications especially if a number of people are doing this at once.

I hope that better clarifies the process.

thanks,
otk

Reply | Quote

Hi,
In that case, I think the array is probably the fastest way to go as it should have the least overhead. I’d suggest using the Filter function on your array (assuming it’s one-dimensional) as it’s probably the fastest way of determining if there’s a match.
Hope that helps.

Reply | Quote

Oh, yes, I thought you only had to do the query once. Bad assumption on my part.

Your instinct to use a string is, I think, a good one. If you only are retrieving the extension field from the database, you can use the GetString function. Otherwise, you can loop once and concatenate all the extensions into a string (preceded by a period; that seems to be a natural delimiter). You then can use Instr to test the entire set in one go. Advisable to make it case insensitive.

Reply | Quote