Recordset fields problems 2

[jordanking]

Hello

I am trying to capture the values of a recordset's field that is nested inside a loop. I am doing this becasue I need to speed up the procedure (currently 6.5 min). I am working with Access 2003.

here is what i am trying (truncated version)

Dim rstNumInv As New ADODB.Recordset
Dim lADBCusID As Field
Dim sqlNumInv As String


    sqlNumInv = "SELECT tblSales.lngzCustomer AS AccessID, tblCustomer.numInvoiceType, tblCompany.txtName, tblCompany.txtBranch, tblCustomer.txtLastName, tblCustomer.txtFirstName, tblCustomer.lngSDBID AS SdbID, tblCustomer.lngzAccountStatus FROM tblCompany RIGHT JOIN (tblCustomer RIGHT JOIN tblSales ON tblCustomer.idsCustomerID = tblSales.lngzCustomer) ON tblCompany.idsCompanyID = tblCustomer.lngzCompanyID GROUP BY tblSales.lngzCustomer, tblCustomer.numInvoiceType, tblCompany.txtName, tblCompany.txtBranch, tblCustomer.txtLastName, tblCustomer.txtFirstName, tblCustomer.lngSDBID, tblCustomer.lngzAccountStatus HAVING ((Not (tblSales.lngzCustomer) = 5192) And ((tblCustomer.numInvoiceType) = 1) And ((tblCustomer.lngzAccountStatus) < 3)) ORDER BY tblCompany.txtName, tblCompany.txtBranch, tblCustomer.txtLastName, tblCustomer.txtFirstName;"
    
    rstNumInv.Open sqlNumInv, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    Set lADBCusID = rstNumInv.Fields("AccessID")

for i = 1 to 380
  if rstNumInv.EOF = False then
    ''run this code that uses lADBCusID
  end if
next

the error returned is a "type mismatch".

so if I chagne my declaration to:

    Set lADBCusID = rstNumInv.Fields("AccessID").Value

the error returned is "object required"


Is there another

 

[RoyVidar]

First issue -> explicit declaration:

[tt]Dim lADBCusID As ADODB.Field[/tt]

Second issue, here you're getting the value of the field

[tt]dim lngMyVar as long ' if numeric ;-)
lngMyVar = rstNumInv.Fields("AccessID").Value[/tt]

Roy-Vidar

 

[jordanking]

Thanks Roy-Vidar

The first issue is resolved. As for the second, I was wondering if there is any way to pass the value of the field directly without adding another step of creating a variable to capture the fields data. I have seen an example of it ("Access 2003 bible"):

Dim myField as field
....
Set myField = tdl![Field]
Do until tbl.EOF
myTotal = MyTotal + myField
Loop

 

[DaveInIowa]

I've never used the .Fields property of a RecordSet, but use syntax like rstNumInv!AccessID to access the field directly.

Also, I believe you're running into problems using Set instead of just assigning it with the equals sign.

 

[RoyVidar]

DaveInIowa - take a look at the discussions here thread222-394313. I think you should also be able to find more info supporting this as one of the fastest/the fastest method.

jordanking - I'm afraid I'm not able to directly point at anything, except if you're only after sums, why not fetch it through a query?

Since you're using Access, perhaps also have a look in some of the seven Access fora (for instance the SQL/Query forum forum701 for hints on how to improve the SQL?).

Using a stored query in stead of dynamic SQL, can also make it faster (you can pass parameters, too)

I'm a fan of ADO too, but DAO can often prove faster on Jet data.

Roy-Vidar