Missing Values in The Code in Ms Access

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
Sorry all the out there I have made a mistake to close the earlier question on the  ms access vba /Json code below:

Private Sub CmdSales_Click()
Const SQL_SELECT As String = "SELECT * FROM Qry1;"

  Dim coll As New Collection
  Dim dict As Scripting.Dictionary
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Set coll = New Collection
  Set db = CurrentDb
  Set rs = db.OpenRecordset(SQL_SELECT, dbOpenSnapshot)
  If Not rs.BOF And Not rs.EOF Then
    Do While Not rs.EOF
      Set dict = New Scripting.Dictionary
      For Each fld In rs.Fields
        dict.Add fld.Name, rs.Fields(fld.Name)
      Next fld

      coll.Add dict
      rs.MoveNext
    Loop
  End If

  rs.Close
  Set fld = Nothing
  Set rs = Nothing
  Set db = Nothing
  Set dict = Nothing
  MsgBox JsonConverter.ConvertToJson(coll, Whitespace:=3)
  Set coll = Nothing
End Sub

Open in new window


The value are missing I did not notice that my appology


Sorry-missing-values.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
That's why i suggested Dictionary insted of Collection
Dictionary stores Key and Value.
In the key you save the FieldName and in the Value the Value of the Field....
Collection will only save 1 single value....(it also has an index but is not easy to refer to it)
I think your code can use Dictionary...SO start slowly...create a Dictionary ....insert a couple of Keys/Values and feed to the Json Converter...check if it works OK....just check the output string....make a quick and dirty example...don't feed your complete recordsource....just a small test
Points gone from previous question ... :(
ste5anSenior Developer

Commented:
How does the Json convert work?
Senior Developer
Commented:
Mea culpa. Sic.

The Dictionary.Add() method is the picky one. We must explicitly specify what we hand over otherwise the object is taken. Default properties are ignored.

Thus:

Private Sub CmdSales_Click()
  
  Const SQL_SELECT As String = "SELECT TOP 3 * FROM Qry1;"
  
  Dim coll As VBA.Collection
  Dim dict As Scripting.Dictionary
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  
  Set coll = New VBA.Collection
  Set db = CurrentDb
  Set rs = db.OpenRecordset(SQL_SELECT, dbOpenSnapshot)
  If Not rs.BOF And Not rs.EOF Then
    Do While Not rs.EOF
      Set dict = New Scripting.Dictionary
      For Each fld In rs.Fields
        dict.Add fld.Name, rs.Fields(fld.Name).Value
      Next fld

      coll.Add dict
      rs.MoveNext
    Loop
  End If

  rs.Close
  Set fld = Nothing
  Set rs = Nothing
  Set db = Nothing
  Set dict = Nothing
  MsgBox JsonConverter.ConvertToJson(coll, Whitespace:=3)
  Set coll = Nothing
End Sub

Open in new window

Top Expert 2014

Commented:
My recommendation is to pass the (populated) recordset into a conversion routine.  Recordsets are, basically, a collection of dictionaries (if you squint at them).
Dear Ste5an

Thank you so much this is un believable it is done for good! what a great job!

Thank you so much once more!

Regards

Chris

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial