MS Access VBA / Airtable Integration Available

If you could post the code JSON code which you used to connect to airtable, I am sure many people would be able to benefit from this and be able to produce some brilliant reports and documents.

I for one would be very interested in this.

Thanks
Gary

Hi Jon_Jaques. Do you have this code available? I’m looking to call Airtable data into an Excel spreadsheet.

Did you guys ever get this code ?
I have a Xamarin app that calls the API and it’s written in C# if you want to chat or share.

Hello!

Sorry I didn’t see this sooner, looks like my spam filter got ahold of it

In order to “talk JSON” from VBA/VBScript, you need a handy dandy parser object, I use this one:

http://www.aspjson.com

In one of my projects, I have a table called Assets. Here is the code to create an Asset record from within MS Access:

Public Function AddAsset(strDesc As String) As String
    Dim oj As New clsJSON, data, strOut As String, s As String
    Dim authnetStatus, authnetVal, xml, strURL As String
   ' On Error GoTo getAssetTags_Error
   On Error Resume Next
   
    strURL = "https://api.airtable.com/v0/123123/Assets" '  & strID '
    ' Debug.Print strURL

    Set oj = New clsJSON
    Set xml = CreateObject("Microsoft.XMLHTTP")
    xml.Open "POST", strURL, False
    xml.setRequestHeader("Content-type") = "application/json"
    xml.setRequestHeader("Authorization") = "Bearer key456456"
    
    oj.data.Add oj.Collection()
    oj.data.Add "fields", oj.Collection
    oj.data("fields").Add "Description", strDesc
    
    data = oj.JSONoutput
    xml.send data


    authnetStatus = CStr(xml.Status)
    authnetVal = xml.responseText
    
    AddAsset = authnetVal ' oj.JSONoutput '

   On Error GoTo 0
   Exit Function

getAssetTags_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getAssetTags of Module modAirTables"
    
    
End Function

The trick to getting it to work as expected is in referencing and dereferencing the JSON object, but once you get the hang of it, it’s not too bad.

–Jon

Public Function AddAsset(strDesc As String) As String
    Dim oj As New clsJSON, data, strOut As String, s As String
    Dim authnetStatus, authnetVal, xml, strURL As String
   ' On Error GoTo getAssetTags_Error
   On Error Resume Next
   
    strURL = "https://api.airtable.com/v0/123123/Assets" '  & strID '
    ' Debug.Print strURL

    Set oj = New clsJSON
    Set xml = CreateObject("Microsoft.XMLHTTP")
    xml.Open "POST", strURL, False
    xml.setRequestHeader("Content-type") = "application/json"
    xml.setRequestHeader("Authorization") = "Bearer key456456"
    
    oj.data.Add oj.Collection()
    oj.data.Add "fields", oj.Collection
    oj.data("fields").Add "Description", strDesc
    
    data = oj.JSONoutput
    xml.send data


    authnetStatus = CStr(xml.Status)
    authnetVal = xml.responseText
    
    AddAsset = authnetVal ' oj.JSONoutput '

   On Error GoTo 0
   Exit Function

getAssetTags_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getAssetTags of Module modAirTables"
    
    
End Function

Go change your key … you seem to have posted it in your response :slightly_smiling_face:
Can I ask what you are exchanging data with ?

Nope, you’ll see I posted 456456 for the key, and 123123 for the app id.

In the case of the function above, the database can create an Asset record in Airtable by passing only one value, strDesc.

The purpose of this particular little project was to print dymo labels for Assets in the database, complete with QR codes to lookup the Asset with.

Nice, I am integrating to make custom feature specific mobile apps that interface with Airtable …
Will be integrating with mobile printers later for barcode generation on demand but not sure which ones.

Hi Jon,

Can you contact me?
josteurs@gmail.com
I see you wrote already some code that makes MS Office read from Airtable.
I’m in need of some VBA and I think you have 98% already at hand.
Obviously, any work you do I’ll pay for.

Hope to discuss this with you soon.

Thanks Joris

Hello Joris,

Check your inbox, I just sent you a message!

–Jon