Access and Groupwise Part Duex: The need for speed!

[Tarnish]

Ok, Eupher really got me into the whole groupwise automation in access thing and now I can accomplish all kinds of things but I have no idea if there's a faster way. Here's some code I wrote to check our main address book for a matching email address to one provided by the user, and if found to populate various fields in my access form from the groupwise record for that email.

However, the main address book in groupWise has just over 10,000 entries and, by my vary unscientific testing procedure, I'm getting around 292 entries/minute searched via this procedure, which is just too slow. Any suggestions to speed it up via code improvement?

What I'm looking for is a way to skip down to a particular letter of the alphabet in the groupwise address book and begin my search there. the letter would be the first letter in the email address so it wouldn't require any additional work by the user.

Any ideas?

Here's the code that works, but is too slow:

Private Sub cmdTest_Click()

    Dim MyGWAuto As GroupwareTypeLibrary.Application2
    Dim gwAccount As GroupwareTypeLibrary.Account2
    Dim gwAddressBook As GroupwareTypeLibrary.AddressBook2
    Dim gwAddress As GroupwareTypeLibrary.AddressBookEntries2
    Dim i As Integer
    Dim x As Integer

    On Error GoTo cmdTest_Click_Error

    Set MyGWAuto = New GroupwareTypeLibrary.Application2
    Set gwAccount = MyGWAuto.Login()
    Set gwAddressBook = MyGWAuto.Login.AddressBooks.Item("Novell GroupWise Address Book")
    x = gwAddressBook.AddressBookEntries.Count

    For i = 1 To x
        If gwAddressBook.AddressBookEntries(i).EMailAddress = Me.txtEmailAddress Then
            Me.txtBusinessPhone.SetFocus
            Me.txtBusinessPhone.Text = gwAddressBook.AddressBookEntries(i).Fields(6)

            Me.txtLastName.SetFocus
            Me.txtLastName.Text = gwAddressBook.AddressBookEntries(i).Fields(7)

            Me.txtFirstName.SetFocus
            Me.txtFirstName.Text = gwAddressBook.AddressBookEntries(i).Fields(5)

            Me.cmbFacility.SetFocus
            Me.cmbFacility.Text = gwAddressBook.AddressBookEntries(i).Fields(8)
            Exit Sub
        Else
        End If
    Next i

    Set MyGWAuto = Nothing
    Set gwAccount = Nothing
    Set gwAddressBook = Nothing

Exit_cmdTest_Click:
    Exit Sub

cmdTest_Click_Error:

    Call ErrorLog(Err.Description, Err.Number, Me.Name)

End Sub

Thanks in advance for any pointers!
T

 

[AlexCuse]

Ont thing that jumps out is this:

Me.txtLastName.SetFocus
            Me.txtLastName.Text = gwAddressBook.AddressBookEntries(i).Fields(7)

You can set Me.txtLastName.Value without moving the focus around. This will speed things up a bit

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Eupher]

Hi, Tarnish,

Never worked with it, but you might take a look at the .Find() method of the AddressBookEntries collection.

Ken S.

 

[Tarnish]

Thanks for the replies!

Alex:
I wouldn't normally use the text property if there wasn't some reason. I remember there being a reason with one of those fields but I don't remember what it is. I'll take a look at it but I think I had to set one to text and didn't figure it made that much difference to just set them all that way.

Ken,
I took a look at the .find method, but didn't get anything much accomplished. No groupwise at home so I'll get back to it monday.

I did finally get to use the last of that code you provided in my first gw post months ago, along with that dev guide I finally found. The code to print the fields out in the immediate window saved some time, as well as the other section that, along with what I found in the dev guide, was the basis of the above code.

Anyway, thanks again.
T

 

[TheAceMan1]

How are ya Tarnish . . .

Never worked with that library but using [blue]access objectivity[/blue] for what its worth try this ([purple]be sure to backup your code so you can come back to square 1[/purple]):

[blue]Private Sub cmdTest_Click()
   Dim MyGWAuto As GroupwareTypeLibrary.Application2, _
       gwAccount As GroupwareTypeLibrary.Account2, _
       gwAddressBook As GroupwareTypeLibrary.AddressBook2, _
       gwAddress As GroupwareTypeLibrary.AddressBookEntries2, _
       [purple][b]Entry[/b][/purple] As Object
   
On Error GoTo cmdTest_Click_Error
   
   Set MyGWAuto = New GroupwareTypeLibrary.Application2
   Set gwAccount = MyGWAuto.Login()
   Set gwAddressBook = MyGWAuto.Login.AddressBooks.Item("Novell GroupWise Address Book")
   
   For Each [purple][b]Entry[/b][/purple] In gwAddressBook.AddressBookEntries
      If [purple][b]Entry[/b][/purple].EMailAddress = Me.txtEmailAddress Then
         Me.txtBusinessPhone = [purple][b]Entry[/b][/purple].Fields(6)
         Me.txtLastName = [purple][b]Entry[/b][/purple].Fields(7)
         Me.txtFirstName = [purple][b]Entry[/b][/purple].Fields(5)
         Me.cmbFacility = [purple][b]Entry[/b][/purple].Fields(8)
         [purple][b]Exit For[/b][/purple]
      End If
   Next
   
   Set gwAddressBook = Nothing
   Set gwAccount = Nothing
   Set MyGWAuto = Nothing
   
Exit_cmdTest_Click:
   Exit Sub
   
cmdTest_Click_Error:
   
   Call ErrorLog(Err.Description, Err.Number, Me.Name)

End Sub[/blue]

See Ya! . . . . . .

 

[Tarnish]

Thanks for the reply, AceMan1!

I'll give it a try on Monday and report back.

T

 

[Tarnish]

Update:

Well, I've tried everything but the find function so I'll look at that later today hopefully.

I put all the rest of the suggestions together and modified it slightly (based on some info I found in the dev guide), and I went from something like 292 entries/minute in groupwise looking for the right one up to a whopping 1500 a minute. I suspect that represents mostly real gains in speed, too, because I did my original test during a time of fairly light network traffic.

It's still too slow. Unless I can sort or filter records (find?) so that I'm essentially just searching one letter of the alphabet each time, it will be too slow. One letter is ~15 seconds if it has to search all the way to the end of the entries for that letter. So a minimum time of ~1 second to a maximum of 15-18 seconds might be worthwhile. Beyond that the value diminishes significantly.

Again, thanks to everyone for the suggestions and if get any further I'll throw in another update.

T

 

[AlexCuse]

Well, its good that you made some progress

You've probably looked into this already, but is there any way you can read the Groupwise address book into a recordset and loop through that?

Another option (and I actually like this the best I think) would be to write a process to populate a table containing all the groupwise info. You could store this table on the user's machine and set it to run (at some time), and link to it from your app? What kind of app is this anyway, that would probably help to know. It needs to get Individual User's group wise address book, right?

These are all 'shot in the dark' type of things though, I have no idea about what kind of interface the Groupwise Address Book exposes, and whether or not you can do your filtering through there.

Hoep this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[Tarnish]

Thanks Alex,

I've thought about importing all the records from the main gw address book, but there area over 10k of them and I suspect it would be a royal pain. Even then, new names are added frequently and it takes some of the fun out of it if there's a possibility that the 'import contact' won't work even when someone is already in the gw book (because they were recently added and aren't in the local 'copy').

As for a recordset, I did think about it but I still only have limited experience opening and closing recordsets and this is a little different. I'm barely comfortable with recordsets using data already inside my app. I'll have to think about that a little more...

What kind of app is it? Well, it started out as an issues type db to store/track/report on work done within one office. Then they wanted some kind of 'paperless office' functionality to so built that around the issues. Then I added a good deal of 'admin' features so that some critical things could be accomplished without using any menus/toolbars in access but only forms and controls in my app (backend backup/compact, kick users off and lock backend, add user, password admin, audit table viewing, and some data management/admin features like finding duplicate contacts and altering tables used for combobox population, etc. Finally, I've added both some groupwise automation (calendaring of issue due dates/times) and some word automation (mostly document creation using data from my app).

Anyway, I really appreciate the input. Maybe I'll get it figured out. I'll have to play around with some of the alternatives to really know how viable they are...starting with that find method to see if that does anything useful.

Thanks again,
T

 

[PHV]

A shoot in the dark:
...
Set gwAddressBook = MyGWAuto.Login.AddressBooks.Item("Novell GroupWise Address Book"
Set myObj = gwAddressBook.AddressBookEntries.Find("(<E-Mail Address> = """ & Me.txtEmailAddress & """)")
Me.txtBusinessPhone.Value = myObj.Fields(6)
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tarnish]

Hi PHV,

Says "missing closing parenthesis" and highlights:

Set myObj = gwAddressBook.AddressBookEntries.Find("(<E-Mail Address> = """ & Me.txtEmailAddress & """)")

If I hover over the expression after find it pops up with this:

Me.txtEmailAddress = "theEmailAddressIentered"

T

 

[Eupher]

Hiya, Chuck,

Here's some code which may help you:

Public Sub GW_Lookup()
Dim oApp As Application2
Dim oAccount As Account3
Dim oBook As AddressBook2
Dim oContact As Address
Dim oContacts As Addresses
Dim sSyntax As String
Dim i As Integer

Set oApp = New Application2
Set oAccount = oApp.Login("[blue]yourUserID[/blue]")

Set oBook = oAccount.AddressBooks.Item("Novell GroupWise Address Book")
sSyntax = "(<E-Mail Address> CONTAINS ""*" & InputBox("Find contact", "", "") & "*"")"
Set oContacts = oBook.AddressBookEntries.Find(sSyntax)

MsgBox "Contacts Found:  " & oContacts.Count

For Each oContact In oContacts
    MsgBox oContact.DisplayName
    For i = 1 To oContact.Fields.Count
        Debug.Print oContact.Fields(i).Name & ":  " & oContact.Fields(i).Value
    Next i
Next oContact

Set oContacts = Nothing
Set oAccount = Nothing
Set oApp = Nothing

End Sub

HTH,

Ken S.

 

[AlexCuse]

Tarnish - the missing closing paren in PHV's code was on the line prior to that. After ("Novell GroupWise Address Book".

Eupher is the expert on this type of stuff, he is probably on the right track





Ignorance of certain subjects is a great part of wisdom

 

[Tarnish]

Hi again Alex,

I saw that one at the start. I'm pretty sure I fixed it but now I'm away from work so I'll have to verify tomorrow :/

Thanks again,
T

 

[Eupher]

Heh, well, I think calling me an expert is overstating things a bit, but thanks for the compliment. ;-)

Seems the keys to the search string argument are the keywords (i.e. field names) and operands. According to the API, only Name, <First Name>, <Last Name>, <E-Mail Address>, and Department are valid keywords. MATCHES, CONTAINS, BEGINSWITH, and DOESNOTCONTAIN are valid operands for the filter expression of the find method - when used with the AddressBookEntries object. Many others are available for use with message objects.

The syntax of the filter string is a little jinky, IMO - single quotes don't seem to work in delimiting and concatenating the parts of your string, but doubled double quotes do. Dunno why, just a syntax thing with the GW object, I guess.

HTH,

Ken S.

 

[Eupher]

p.s. Should have qualified... the keywords above are the valid keywords for the AddressBookEntries object. As with the operands, many others are available for the message object.

Ken S.

 

[Eupher]

Hiya, Chuck, still there? Any luck with the find() method? For reference, in my little bit of code, the find method searched our entire global address book ( ~ 9,000-10,000 addresses) and returned results within 2-3 seconds. In one of my personal address books ( ~ 70 entries) results were instantaneous - as you might expect.

Let me know how you get on...

Ken S.

 

[Tarnish]

Hiya Ken,

I've made one attempt earlier (last week i think) but I had only a few minutes to try and I'm just not that good. I have to bang a little before stuff gives way

This week, I've had to prepare something for a meeting tomorrow morning so I've been mostly working on that instead. After that meeting I plan to give it a shot again.

That's really encouraging news about your results with it. I suspect your hardware/network performance far exceeds ours but I may be wrong. Still, like I said earlier, anything under around 15 seconds and it might be worthwhile. Under 10 and I'd be stoked

Thanks again, and I'll post back sometime tomorrow or Friday.

T