Importing DVLA API data into table

[Tigereyes78]

Good morning,
I have the below code which is in Excel, it takes a number plate from one cell, requests data from DVLA and pastes the resulting data in the adjacent cell. I would like to do this in Access but am not sure exactly how.
I have a form with a text box containing the registration number, I need a button to run the API request and paste the result into my RegRaw table in a new record.

Any ideas would be gratefully received *Note API key in code has been replaced by XXX as its for personal use only!

Thank you


Sub TestDVLA()
Dim DvlaService As Object
Dim Response As Object

API_KEY = "XXX"

Set DvlaService = CreateObject("MSXML2.XMLHTTP")

With DvlaService
.Open "POST", "https://driver-vehicle-licensing.api.gov.uk/vehicle-enquiry/v1/vehicles", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"


.setRequestHeader "x-api-key", API_KEY
.send "{""registrationNumber"": """ & Range("A2") & """}"
Sheets("DVLAdata").Select
Range("B2") = .responseText
End With
End Sub

[Edgar]

You can do this:

Sub Test()

    Dim DvlaService As Object
    Dim Response As String
    
    API_KEY = "XXX"
    
    Set DvlaService = CreateObject("MSXML2.XMLHTTP")
    
    With DvlaService
        .Open "POST", "https://driver-vehicle-licensing.api.gov.uk/vehicle-enquiry/v1/vehicles", False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "x-api-key", API_KEY
        .send "{""registrationNumber"": """ & RegistrationNumber & """}"
        Response = .responseText
    End With
    
    CurrentDb.Execute "INSERT INTO RegRaw (ServerResponse) VALUES (" & Response & ");"

End Sub

I do not know where you're gonna get RegistrationNumber from. I also don't know how your table looks. I also don't know why your Response variable was an object. And I don't know at what point you're defining the API_KEY. As you can see, not much changes. But you will definitely want to parse the response into the corresponding fields. For that to be easy, use the VBA-JSON library from github, look it up. Add it to your Access file. With that you'll be able to write Response("make"), and if you had a field called make in your table, you could add it to the INSERT INTO instruction above

[Tigereyes78]

Thank you for the response, the RegistrationNumber is entered on a Vehicles data entry form (a subform on customers form). Customers table is linked to vehicles table on a one-to-many relationship as customers can have more than 1 vehicle. When a new vehicle Registration Number is added I want to have a button that goes to collect that vehicle data.
The API is hard coded in, I just replaced it with XXX for the purposes of posting here
I am very new to the API data request task so am really unsure how best to execute this. I am just building the database now so can change tables and field names as needed. I used to do database development but it was years ago so am very rusty and need a little hand holding

The code above was adapted from an old colleagues code to get the DVLA data into excel and test the API request was working. I now need to get it into my Access database so if there is a better way please advise

Thank you

[Tigereyes78]

If it helps, this is the response I get into my Excel spreadsheet, all in one cell. (using my own reg)

{"registrationNumber":"LK66ONU","taxStatus":"Taxed ","taxDueDate":"2024-04-01","motStatus":"Valid","make":"NISSAN","yearOfMan ufacture":2016,"engineCapacity":1461,"co2Emissions ":99,"fuelType":"DIESEL","markedForExport":false," colour":"GREY","typeApproval":"M1","dateOfLastV5CI ssued":"2022-04-09","motExpiryDate":"2024-01-24","wheelplan":"2 AXLE RIGID BODY","monthOfFirstRegistration":"2016-10"}

[Welshgasman]

That all looks like comma delimited or colon delimited.
So I would use the Split() function and then populate a record with every other data item (2, 4, 6 etc) as 1,3 & 5 etc are the fieldnames.

[Edgar]

I don't know what you're going to store from that json, make a table in your database and post here a sample so I can tell you how to wire it up. We could guess all week for this, better to work from a sample file.

[Tigereyes78]

I don't know what you're going to store from that json, make a table in your database and post here a sample so I can tell you how to wire it up. We could guess all week for this, better to work from a sample file.

This is the table I have. I am creating a form for it which will be a subform on the customer form. The registrationNumber will be entered but then I need the rest to populate from the API somehow :/

[Edgar]

Take a look at the attached file, let me know how it goes

[Tigereyes78]

Take a look at the attached file, let me know how it goes

You are a superstar! Thank you so much Saved me another migraine day

[Welshgasman]

Take a look at the attached file, let me know how it goes

@Edgar,
Any chance of a 2007 (12) compatible version please? I would like to see it working.

TIA

[Tigereyes78]

@Edgar,
Any chance of a 2007 (12) compatible version please? I would like to see it working.

TIA

Do you have the API key from DVLA? this has to be requested as it is personal to you. The "XXX" in the code needs to be replaced with your key. I would have expected the file shared by Edgar above would be backwards compatible

[Welshgasman]

No, and I am not paying for one.
I can use your data as the string result and walk through the code, just to see it working.

[Tigereyes78]

No, and I am not paying for one.
I can use your data as the string result and walk through the code, just to see it working.

You dont have to pay for it! It wont work without the API key to request the data from DVLA.
The API key is free, you just have to request it from DVLA so its personal to you!

[Welshgasman]

You dont have to pay for it! It wont work without the API key to request the data from DVLA.
The API key is free, you just have to request it from DVLA so its personal to you!

OK, thanks.
I was just going to set strResponse to your data and then walk through the code.
Would you have a link to request a key please?

[Tigereyes78]

OK, thanks.
I was just going to set strResponse to your data and then walk through the code.
Would you have a link to request a key please?

https://developer-portal.driver-vehi...-ves-api-guide