How to use a "POST" method in VBA Ms Access

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
I want to send the data from Ms Access VBA/Json to a general website which does not require authorisation, but the data does not go there below is my "POST" method:

Option Compare Database
Option Explicit
Private Sub CmdSales_Click()
'  Const SQL_SELECT As String = "SELECT * FROM Qry1;"
  Dim http As Object
  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
  Dim qdf As DAO.QueryDef
  Dim prm As DAO.Parameter
  Set http = CreateObject("MSXML2.XMLHTTP")
  http.Open "POST", ",False"
  http.setRequestHeader "Content-Type", "application/Json"
  http.send ConvertToJson(coll, Whitespace:=3)
  Set db = CurrentDb
  Set qdf = db.QueryDefs("Qry1")
For Each prm In qdf.Parameters
    prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset()

Set qdf = Nothing
  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
  End If

  Set fld = Nothing
  Set rs = Nothing
  Set db = Nothing
  Set dict = Nothing
  MsgBox "Post Success"
  Set coll = Nothing
End Sub

Open in new window

Just also in case of the username/password required where do I put it in the code above?


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
As it doesn't matter if you use POST, PUT, or GET, that is not your source of error. However: 

doesn't have a route for /invoices. So, if you this, it returns empty data: "{}" as you have seen.

Try with a supported route like /comments or /photos.

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