MS Access talking to Spark core

Hi

I am working on a large scale project for Spark Core and have just received my first development kit. What I am hoping to do is produce an add on for machines that currently exist in their hundreds in the field. I am looking to monitor and report back to an MS Access application that has machine location information. I hope the application will be able to receive information from Spark Cores and run functions to update machine settings. I have quite a lot of experience in Access so thought it would be a good starting point for me.
I would like to ask if anyone thinks this would even be possible, it is my first experience with Spark Core and so far I have got my iPhone to turn on and off the Blue LED using the Spark Core app and done this same from a web site Atomiot. i am inexperienced with the Spark Core and just looking for some pointers.

Thanks in advance for any thoughts or advice.
Clive

Hi,

I think that sharpspark (C# client for spark) will help you get started. I think you can use a C# program to use as an interface between MS Access and spark, correct me if I’m wrong :wink:

Link: http://community.spark.io/t/spark-sharp-for-c-developers/7040

1 Like

Thanks for the reply I m looking at the Spark Sharp github page. I was hoping to do this direct from Access maybe that is not possible. I will continue looking.

Thanks,
Clive

Hi @casm,
I’m working on major features to wrap perfectly Spark Core API with easy-to-use C# API.
A database API core could be added to the nice to have in order to keep track of some values, but let me know if some support for access db is needed!

Haven’t played with MS Access for many years but if Access Basic has support for Microsoft’s TCP/IP stack (as I recall it dose) you should have no problem creating a connection with the spark.

I am really sorry I have been travelling on business for the past two weeks. I think that the wrapper including Access mdb would be great. I also use mySQL and support for this would be great also. I have been looking at ways to work with Access but so far nothing has come to light. I cant even get Internet Explorer to open a link, I have to use Chrome to open any link to the API.

Thanks,
Clive

@casm, if you want a pure VBA solution, you could do something like this

Option Explicit
 
Sub DoSomeJob()
  Const sUrl As String = "http://sim.mci.ir/get-free-nums"
 
  Dim oRequest As WinHttp.WinHttpRequest
  Dim sResult As String
 
  On Error GoTo Err_DoSomeJob
 
  Set oRequest = New WinHttp.WinHttpRequest
  With oRequest
    .Open "GET", sUrl, True
    .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
    .Send "{range:9129370}"
    .WaitForResponse
    sResult = .ResponseText
    Debug.Print sResult
    sResult = oRequest.Status
    Debug.Print sResult
  End With
 

 
Exit_DoSomeJob:
  On Error Resume Next
  Set oRequest = Nothing
  Exit Sub
    
Err_DoSomeJob:
  MsgBox Err.Description, vbExclamation, Err.Number
  Resume Exit_DoSomeJob
 
End Sub

This I found with minimal googling for “vba http request” at http://www.codeproject.com/Questions/794960/How-Do-I-Get-Http-Request-In-Excel-Vba

1 Like

Hi scruffr

I will try this out. I have been searching Google for a long time with REST, API, JSON, ACCESS and multiple combinations of these. I didn’t go back to the http request.

Thanks,
Clive

Hi,

I have been trying to work out the correct configuration for winHTTP. I can get 400, 404 and occasionally 401 returned from the server but have not managed to connect. I think it is to do with authorisation and I am now in to “territory unknown”. Has anyone tried to connect to the spark cloud API using winhttp and can you give me some pointers? Here is the response to the below code,

{

“code”: 400,
“error”: “invalid_request”,
“error_description”: “The access token was not found”
}
400

I have tried almost innumerable ways to send the data. Any help much appreciated.

Private Sub query_api_Click()

  Const sUrl As String = "https://api.spark.io/v1/devices"
 
  Dim oRequest As WinHttp.WinHttpRequest
  Dim sResult As String
 
  On Error GoTo Err_DoSomeJob
 
  Set oRequest = New WinHttp.WinHttpRequest
  With oRequest
    
    .Open "GET", sUrl, True
    .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
    .Send "\?access_token=my_access_token"
    .WaitForResponse
    sResult = .ResponseText
    Debug.Print sResult
    Me.return_data = sResult
    sResult = oRequest.Status
    Debug.Print sResult
    Me.return_data = Me.return_data & vbCrLf & sResult
    
  End With
 

 
Exit_DoSomeJob:
  On Error Resume Next
  Set oRequest = Nothing
  Exit Sub

Best regards,
Clive

1 Like

Here is working code, it was just getting the HTTP link correct. This now returns the Json data as a sting so I can pare it in to my application.

Thanks for everyone’s help.

Best regards,
Clive

  Private Sub query_api_Click()
  Const sUrl As String = "https://api.spark.io/v1/devices/my_core_id/LED_state?access_token=my_access_token"
  Dim oRequest As WinHttp.WinHttpRequest
  Dim sResult As String
  On Error GoTo Err_DoSomeJob
  Set oRequest = New WinHttp.WinHttpRequest
  With oRequest
    .Open "GET", sUrl, True
    .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
    .Send ""
    .WaitForResponse
    sResult = .ResponseText
    Debug.Print sResult
    Me.return_data = sResult
    sResult = oRequest.Status
    Debug.Print sResult
    Me.return_data = Me.return_data & vbCrLf & sResult
  End With
Exit_DoSomeJob:
  On Error Resume Next
  Set oRequest = Nothing
  Exit Sub
Err_DoSomeJob:
  MsgBox Err.Description, vbExclamation, Err.Number
  Resume Exit_DoSomeJob
End Sub
2 Likes

Hi
I am now working on Post method I have a working https string from cUrl and tansferred this in to winhttp and changed method to post from get. It does not work I trined many variations but all I get are 400 errors. Can anyone advise on Post mthod set up in winhttp?

Best regards,
Clive

Hi,

I spent a few more hours and got it working, I paste below working code from Access VBA in case anyone needs it. It pulls from a form the command to send and /i have it so it is called from a button click. My code uses cmd as the variable name for the function I put the result in a box on the form so I can check what comes back.

    Option Compare Database



Private Sub query_api_Click()
      
   send_cmd = Me.result
   send_cmd = "cmd=" & send_cmd
   
   

  Const sUrl As String = "https://api.spark.io/v1/devices/device_id/function_name?access_token=my_access_token"
      
  Dim oRequest As WinHttp.WinHttpRequest
  Dim sResult As String
  Dim fRestult As Boolean
  
  
  Me.no_response.Visible = False
   
  On Error GoTo Err_DoSomeJob
 
  Set oRequest = New WinHttp.WinHttpRequest
  
  With oRequest
    
    .Open "POST", sUrl, True
    .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
    .Send (send_cmd)
     fResult = .WaitForResponse(10)
     If fResult = False Then
        Me.no_response.Visible = True
        .Abort
        GoTo Exit_DoSomeJob
    
    Else
        
    sResult = .ResponseText
    Debug.Print sResult
    Me.return_data = sResult
    sResult = oRequest.Status
    Debug.Print sResult
    Me.return_data = Me.return_data & vbCrLf & sResult
    End If
         
  End With
 

 
Exit_DoSomeJob:
  On Error Resume Next
  Set oRequest = Nothing
  Exit Sub
    
Err_DoSomeJob:
  MsgBox Err.Description, vbExclamation, Err.Number
  Resume Exit_DoSomeJob
  
End Sub
1 Like

Hi @casm, I’m glad to hear things are taking shape with your project :+1:

On thing I’d like you to do for us is to reformat your code blocks.

If you wrap your code blocks between a starting line ``` (three grave accent characters - you don’t need cpp as in the Tip and Tricks - it’s VBA anyhow) and a finishing line of the same, your code will look neater.

You’ll find some more tips and tricks here
http://community.spark.io/t/forum-tips-and-tricks/3999

Hi @casm and @ScruffR

I tried to fix this for you, but not knowing VBA much, I wasn’t sure about that first line. @casm you can edit your own posts by clicking on the little pencil icon.

Thanks for the pointers I was lazy in not reading the forum notes, sorry. Hopefully all are corrected now.

Best regards,
Clive

2 Likes

Hello casm and ScruffR,

Thanks for your VBA code. I used it in Excel, It saved me a lot of time and it works great. I used it for the example code for the "Controlling LEDs over the Internet"example. If a starter comes along he/she can past it right into a VBA module in an Office Application and run it. The only thing that must be done is making a reference to the Microsoft WinHTTP Services, version 5.1

Const COREID = "YOUR_CORE_ID"
Const ACCTKN = "YOUR_ACCESS_TOKEN"

'Controlling LEDs over the Internet

Private Sub PostMe()
  Dim send_cmd As String
  'send_cmd = "params=l1,HIGH"
  send_cmd = "params=l1,LOW"
  Const sUrl As String = "https://api.spark.io/v1/devices/" & _
                          COREID & _
                          "/led?access_token=" & _
                          ACCTKN
      
  Dim oRequest As WinHttp.WinHttpRequest
  Dim sResult As String
  Dim fResult As Boolean
  
  On Error GoTo Err_DoSomeJob
 
  Set oRequest = New WinHttp.WinHttpRequest
  
    With oRequest
        .Open "POST", sUrl, True
        .SetRequestHeader "Content-Type", _
                          "application/x-www-form-urlencoded; charset=UTF-8"
        .Send (send_cmd)
        fResult = .WaitForResponse(10)
        If fResult = False Then
            .Abort
            GoTo Exit_DoSomeJob
        Else
            sResult = .ResponseText
            Debug.Print sResult
            sResult = oRequest.Status
            Debug.Print sResult
        End If
    End With

Exit_DoSomeJob:
    On Error Resume Next
    Set oRequest = Nothing
    Exit Sub
    
Err_DoSomeJob:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_DoSomeJob
  
End Sub

Hi Marcel

Sorry for not mentioning needing to dd the reference to WINHHTP. I am glad the code worked for you. I actually pull the core id and access token from a table so I can talk to multiple cores.

best regards,
Clive

Hi Marcel,

I noticed you are hard coding the id and access token as constants. As I said in a previous note I am using a table to supply these values. I suggest you do no use constant, particularly for the access token or you will have to rewrite your code for any change. Access tokens expire after three months so it would mean a rewrite of the code every three months to update. I have removed all constant declarations from my code.

Best regards,
Clive

Thanks for your advice. This code is just a quick and dirty example. In real world programming I set these on a parameter worksheet in Excel.