Macro to find PO comment too slow 1

[johnhugh]

Hello,
In Accpac 5.6 I use the code below to find the header comment of a PO.
Is there any way to make this faster because right now it's just starting from the last PO and working it's way up which can take a long time?

Function SearchHeaderComment(strHeaderComment)

'get PO header comment
POPOR2header.GoBottom
Do
    If txtPO.Text = POPOR2headerFields("PONUMBER") Then
        strHeaderComment = POPOR2headerFields("COMMENT")
        Exit Do
    End If
Loop While POPOR2header.GoPrev

End Function

 

[Jay Converse]

Comments aren't indexed, of course it will be slow.

 

[johnhugh]

Thanks for clarifying that tuba.

What could I do to make it faster?
Use an SQL query or index comments?

 

[Jay Converse]

Use a SQL query, you can't add indexes to views.

 

[ettienne]

Use an SQL query and the CS0120 view.

 

[johnhugh]

Thanks.
Why use the CS0120 view?

 

[Jay Converse]

So you don't have to use ADO.

 

[ramam1]

You can't add an index to a view? I think in SS2k8 you can - not sure about others

 

[Jay Converse]

No, Ramam, not a SQL view, an Accpac view, they're different.

 

[ramam1]

Tuba, I get that; but why not add an index to the relevant field in the DB directly?

 

[Jay Converse]

Yes of course, and then you need to use pure SQL queries, not Accpac views, except for CS0120, which is a pure SQL call.

 

[DjangMan]

Just for clarification Accpac Views still see the data in terms of Tables and Records vs. a Record Set. So the views will call .Next, .Next, .Next as it works through the database. However, if you add an index to the database under MSSQL then a non-indexed .browse statement may return results faster.

The nice thing about CS0120 is that you can make use of things like a SQL view or stored procedure to take advantage of the power of your database engine.

 

[ramam1]

Since we're there... why use CS0120 instead of ADO?

 

[DjangMan]

CS0120 will work on the database that its DBLink is connected to. That allows you to write a macro that needs database access to work on any Accpac company without needing to write anything company specific or have one macro per company.

As an aside you can clone a session in a macro to point to another database and CS0120 will work on the correct database.

CS0120, however, is a barebones ODBC implementation. I've had problems with some SQL statements returning array of characters instead of text strings.

 

[ramam1]

I see; some of my macros use ADO - we sniff the Accpac Org to select the appropriate DB to connect to.