Accpac VBA

[gord2]

Hi,

I'm writing a macro to read customer database info. I don't know how to loop through the database and list or print all the fields IDCUST, IDGRP, NAMECUST for IDGRP="ADM". Can someone please help?

Thanks.


Dim Session As ACCPACXAPILib.xapiSession
Dim ARCUSTOMER As ACCPACXAPILib.xapiView
Dim ARCUSTOMERFields As ACCPACXAPILib.xapiFields

Set Session = CreateObject("ACCPAC.xapisession&quot
Session.Open "ADMIN", "HOLLAND", "SAMINC", Date, 0

Set ARCUSTOMER = Session.OpenView("AR0024", "AR&quot
Set ARCUSTOMERFields = ARCUSTOMER.Fields

ARCUSTOMER.Init
ARCUSTOMER.Order = 0

???

ARCUSTOMER.Cancel

 

[zemp]

Try this,

With ARCUSTOMER
.init
.Order=0
.browse "IDGRP = " & chr(34) & "ADM & chr(34) & "", true
do while .fetch
debug.print .fields("IDCUST&quot.value
debug.print .Fields("IDGROUP&quot.value
debug.print .Fields("NAMECUST&quot.value
loop
.cancel
end with

Now the browse statement might give you some trouble. It can be touchy. I have also used this method.

dim strBrwose as string
strBrowse = "IDGRP = ADM"

then

.Browse strBrowse, true

Or if the GroupID is a variable,

.Browse &quot;IDGRP = &quot; & <Variable> & &quot;&quot;, True

Thanks and Good Luck!

zemp

 

[zemp]

You will notice that I don't bother with the xAPIFields objects. I just use the .fields collectionj of the view objects. Why duplicate.

Thanks and Good Luck!

zemp

 

[gord2]

Thank you zemp!

With ARCUSTOMER
.init
.Order=0
.browse &quot;IDGRP = &quot; & chr(34) & &quot;ADM & chr(34) & &quot;&quot;, true
do while .fetch
debug.print .fields(&quot;IDCUST&quot.value
debug.print .Fields(&quot;IDGROUP&quot.value
debug.print .Fields(&quot;NAMECUST&quot.value
loop
.cancel
end with

The above codes works. But it is very slow. It takes about 40 seconds to loop through 16000 records. Is there a way we can bypass the view &quot;AR0024&quot; and access the table ARCUS directly?

By the way, what is the purpuse of Chr(34) in the following line:
.browse &quot;IDGRP = &quot; & chr(34) & &quot;ADM & chr(34) & &quot;&quot;, true

Thanks again.

Gordon.

 

[zemp]

Actually 40 sec for 16000 is not bad. 1 record every 0.0025 seconds or 400 records per second.

Yes you can access the ARCUS table directly. But not with the xAPI. YOu would have to use an ODBC connection and ADO or a direct connection to the database with ADO. This however will not greatly increase the speed. The speed can also be affected by your network and its traffic volume (if not setup locally).

Also when you use the xAPI views you are using Accpacs business logic so you ensure that your data does not become corrupted. With an ADO connection you will have to create all the checks yourself. Noe if you are only reading the data then obviously that is not an issue.

Chr(34) inserts a quotation mark in the string. So the string looks like this.

IDGRP = &quot;ADM&quot;

But if we place quotes around it so it is recognized as a valid argument for the browse event it errors because of the &quot;extra&quot; quotes.

.Browse &quot;IDGRP = &quot;ADM&quot;&quot;, True.

As a result we need to replace the quotes around ADM so that they will be recognized properly. There are a couple of ways of doing this. That's the one I prefer. A second would be to create the string first and pass the string as the argument for the .browse event (as shown in my previous post).


Thanks and Good Luck!

zemp