Accessing 400 data through VB Script

[Guest.Visitor]

Are you sure it's not the SQL itself that is slow? What release are you on? Which version of CA?

[J.Wells]

Accessing 400 data through VB Script

Brian, No, I am not sure that it's not the SQL itself. As a matter of fact, I am reviewing it now to see if I can improve upon it. I was just curious if there was a better way to access the data.... Thanks, Joe

[Guest.Visitor]

Accessing 400 data through VB Script

ADO's not too bad. Lots of functionality, pretty easy to use, and can be made to perform. There are a bunch of articles on the site about it, some of which are here. There's also a bunch of stuff about tuning SQL performance, too. Brian

[J.Wells]

Accessing 400 data through VB Script

I have written a VB script that validates OCR data against data on the 400. Here is what I am doing:

 Dim objConnection Dim objRecordset Dim strSQLQuery Set objConnection = CreateObject("ADODB.Connection") objConnection.Provider = "MSDASQL" objConnection.ConnectionString = "myas400" objConnection.Open strSQLQuery = "select fscins, amount, dosfrom, dosto, servcodes.svccode, " &_ " servcodes.svcasacode from invheader, invdetail, servcodes, " &_ "patheader where invdetail.invnum = " & zAptNum_aac.CurrentLine.Value &_ " And invdetail.svccode = servcodes.svccodeih And transtype='C' " &_ "and svctype1 = 'C' And invheader.invnum=invdetail.invnum " &_ "and patheader.mrn=invheader.mrn " Set objRecordset = objConnection.Execute(strSQLQuery) 

This works fine, except that it seems rather slow. What other connection methods are there that might be faster? Thanks, Joe

[J.Wells]

Accessing 400 data through VB Script

Brian, Thanks for the link. I'll check it out. Joe