Access 365 VBA ADODB not returning all fields in query

Question

So I'm running Access 365 (64 bit), using vba to code some functionality for an update.
I have:
Select * from tblA where partnumber = '12345'
tblA has 61 fields.
60 of the fields are returned when I execute the ADODB code below:

Dim CN As ADODB.Connection
Set CN = New ADODB.Connection
Dim Sql As String

' gblODBCString = "ODBC;Description=;DRIVER=SQL Server;SERVER=" + + ";Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=;"

CN.Open gblODBCString

Set CM = New ADODB.Command
CM.ActiveConnection = CN

sql = "Select * from tblA where partnumber = '12345'"

CM.CommandText = Sql

Set rs9 = New ADODB.Recordset
Set rs9 = CN.Execute(Sql, varparams, adCmdText)

If rs9.EOF Then
    reset_display
    Exit Sub

Else
' display the info...
txtField0.value = rs9(0)
txtField1.value = rs9(1)

and so on...
when it gets to txtField59.value = rs9(59), it returns nothing... a null value...
for the other 60 fields, it properly returns their values.
I've double checked in the SQL table it's pulling from, and the value is 5.
I've double checked the Linked Table being used, and that table shows the value as 5.
But no matter what I do, I cannot get it to populate.

I've replaced the 'Select * from tblA...' logic with 'Select field0, field1, field2... field61 from tblA', and it makes no difference.
If I check rs9![field59], that returns the same as rs9(59).
But 58 works fine, and 60 works fine.
I'm just pulling my hair out.
Any thoughts?
Steve

Answer

Well, I think I've figured it out, but it makes no sense...
If I have (actual code):

    txtWIPSupplierLT.value = nzz(rs9(57), 0)

    txtWIPSupplierMOQ.value = nzz(rs9(58), 0)

    txtWIPSupplierUOM.value = Trim(nzz(rs9(59), "EA"))

if works... nzz is a function that 'enhances' the nz function, so if the field is null or blank, it returns the default value.
But if I have:

    txtWIPSupplierMOQ.value = nzz(rs9(58), 0)

   txtWIPSupplierLT.value = nzz(rs9(57), 0)

    txtWIPSupplierUOM.value = Trim(nzz(rs9(59), "EA"))

then 57 and 59 return proper values, but 58 does not.

If I switch it around again to be:

    txtWIPSupplierUOM.value = Trim(nzz(rs9(59), "EA"))

  txtWIPSupplierMOQ.value = nzz(rs9(58), 0)

   txtWIPSupplierLT.value = nzz(rs9(57), 0)

then 59 works, but not 58 or 57...

For some reason it is super fussy about the recordset fields being accessed in order...
No idea why that is the case, but I guess I'll just double check this in the future...
Thanks for the support.
Steve

Access 365 VBA ADODB not returning all fields in query

1 answer