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
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
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.
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.
@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
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.
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
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
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?
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
Hi @casm, I’m glad to hear things are taking shape with your project
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.
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 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
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.
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.
