We help IT Professionals succeed at work.

Get VIN decode data to put in Access fields

gaynes asked
Last Modified: 2013-11-18
I am looking for some way to get VIN decode data placed into fields in my form. I want the user to be able to put in the VIN number and have it look up on the internet via software or ActiveX or something, then have the data flow into the fields for year, make, model, color.

Ideas? Thanks!
Watch Question

Top Expert 2016



That dumps the whole page in. Is there a more elegant way to pull the data in? Also, I am not sure how to land it to get the Vin entered in the right spot on the web page.

There seems to be many provider of Web Services to decode VIN.  I am not in the automative industry, so I can't compare them.  But from an application point of view, using a web service that return the description in an XML document is simple.

have a look at this site that I found using Google:

Again, there are other similar sites.


I saw that as well. How would i get the XML data to populate my form from this quickly? That is why I was hoping for an activeX or some easy way to pull this data.
Top Expert 2016

Top Expert 2016

also, you may not be able to find one that is tailored fit for your requirement.
the examples suggested are mere examples to give you an idea how to do the things you want done. In short, you need to do some tweaking....


I realize that. I am playing with them. Since the automotive industry is so huge, I am surprised there isn't something already out there I can buy or use to interface. There are lots of decode VIN free sites.

When you subscribe to those services, then will tell you the structure of the XML document.

This is a portion of an example from the site above:

<vin_number value="3D7MX48C16G266205">
<common_model>Ram Pickup 3500</common_model>

It may seems gibberish, but with the XML library from MS (now include in Windows), you can parse this information and query it directly as easily as the value of a recordset (well very close anyway).

It does involved some code, but it is more reliable than scan the text of a web page which layout could change at any time.

It is possible that the companies who provide the web services could have VB or VBA examples.


I got the XML to come in, now how do I put it in my form fields? Can you point me to some examples?

I will based my example on the above XML.  You will have to adapt it to the actual format you received.  For this code to work, you will need a reference to Microsoft XML library (use the latest version you have)

Lets say that wReplyXML is string variable that contains the XML you reveived from the web site and me.txtCarMake is a textbox on your form, then:

dim oDOM as new As MSXML2.DOMDocument30
dim oNode As MSXML2.IXMLDOMNode

odom.loadXML wResult
If oDOM.parseError <> 0 Then
    msgbox  "Could not interpret result: " & oDOM.parseError.reason
   Set oNode = oDOM.selectSingleNode("vin_number\common_info\common_basic_data\common_make")
   If oNode Is Nothing Then
      msgbox "Node is not found: " & "vin_number\common_info\common_basic_data\common_make"
      me.txtCarMake.value  = oNode.nodeTypedValue
   End If

   ' ....
end if


This is the code I am starting from. It displays, then I can't get the xml data to work how you said. I did add the Microsoft XML object. It doesn't like your dim odom statement.

Sub TestIt()
Dim xmlDoc As New DOMDocument
Dim currNode As IXMLDOMNode
Dim rootNode As IXMLDOMNode
Dim oNodeList As IXMLDOMNodeList
Dim NewNode As IXMLDOMNode
Dim strFullXML, xmlstring As String
Dim v
v = "2hnyd18265h504875"
Dim strVINqueryURL As String
strVINqueryURL = "http://www.vinquery.com/ws_POQCXTYNO1D/NX2PD7QK.aspx?accessCode=85035b5e-44ce-4d1a-bfd5-1de0cd77d022&vin=" & v & "&reportType=2"
xmlDoc.async = False

With xmlDoc
    If Not xmlDoc.Load(strVINqueryURL) Then
        MsgBox "Line: " & .parseError.Line & vbCrLf & "Char: " & .parseError.linepos & vbCrLf & _
                "Text: ...'" & Mid(.parseError.srcText, 57, 20) & "...'" & vbCrLf & "Reason: " & .parseError.Reason
        Exit Sub
    End If
End With
Set rootNode = xmlDoc.documentElement
Set currNode = rootNode.firstChild
MsgBox currNode.XML
End Sub

I also tried:
Public Function ImportXML(x)
  Application.Import x, acStructureAndData
End Function

putting this on a button with importxml(currNode.XML) to try to have it build a table. It says "object required". I am so frustrated! Thanks.

Which version of the Microsoft XML library are you using ?

With this URL, I will try to give you a more specific example.


6.0, thanks


Change in the url string to reportType=0. They just changed my subscription. Thanks!

I built this example with version 5, but it should work fine with version 6.
A comment before I go to the example.  In your code, you had a line :

Dim strFullXML, xmlstring As String

This is a common misunderstanding of the DIM statement.  In this line, strFullXMl is declare as a variant, not a string.  When putting many variables in the same DIM statement, each needs it AS type portion, if not, they will be declare as variant.  Best practices suggests to put only one variable per Statement.

Here is my example.  I added Error processing and cleanup. I will let you look at it, you can then get back with questions:

Public Sub TestCode()

   'Debug.Print FetchVIN_XML("275")
   Debug.Print FetchVIN_XML("2hnyd18265h504875")
End Sub

Public Function FetchVIN_XML(pVin As String) As Boolean

Const BASE_URL = "http://www.vinquery.com/ws_POQCXTYNO1D/NX2PD7QK.aspx?accessCode=85035b5e-44ce-4d1a-bfd5-1de0cd77d022&vin=[%VIN_HERE%]&reportType=2"

Dim oXmlDoc As New DOMDocument
Dim oVehNode As IXMLDOMNode
Dim oRootNode As IXMLDOMNode
Dim strVIN As String
Dim strVINqueryURL As String
Dim wResult As Boolean

On Error GoTo Proc_Error

strVIN = UCase(pVin)
strVINqueryURL = Replace(BASE_URL, "[%VIN_HERE%]", strVIN)

oXmlDoc.async = False

With oXmlDoc
    If Not .Load(strVINqueryURL) Then
        MsgBox "Line: " & .parseError.Line & vbCrLf & "Char: " & .parseError.linepos & vbCrLf & _
                "Text: ...'" & Mid(.parseError.srcText, 57, 20) & "...'" & vbCrLf & "Reason: " & .parseError.reason
        wResult = False
        GoTo Proc_exit
    End If
End With

Set oRootNode = oXmlDoc.documentElement.SelectSingleNode("VIN[@Number=""" & strVIN & """]")
If oRootNode Is Nothing Then
   MsgBox "Wrong format ???"
   wResult = False
   GoTo Proc_exit
End If

If oRootNode.Attributes.getNamedItem("Status").Text <> "SUCCESS" Then
   MsgBox "error in XML query: " & oRootNode.Attributes.getNamedItem("Status").Text
   Set oVehNode = oRootNode.SelectSingleNode("Vehicle")
   'load your form controls here (replace debug.print with me.txt... = )
   ' Item names are CASE SENSITIVE
   Debug.Print GetItem(oVehNode, "Make")
   Debug.Print GetItem(oVehNode, "Model")
   Debug.Print GetItem(oVehNode, "Model Year")
   Debug.Print GetItem(oVehNode, "Manufactured in")
   Debug.Print GetItem(oVehNode, "Fuel Economy-city")
   wResult = True
End If

   On Error Resume Next
   Set oXmlDoc = Nothing
   Set oRootNode = Nothing
   Set oVehNode = Nothing
   FetchVIN_XML = wResult
   Exit Function


   MsgBox "Unhandled error: " & Err.Description
   Resume Proc_exit
End Function

Private Function GetItem(pVehNode As IXMLDOMNode, pItemName As String) As String

   Dim oItemNode As IXMLDOMNode
   Dim wValue As String
   Dim wUnit As String
   On Error GoTo Proc_err
   Set oItemNode = pVehNode.SelectSingleNode("Item[@Key=""" & pItemName & """]")
   If oItemNode Is Nothing Then
      wValue = "?"
      wValue = oItemNode.Attributes.getNamedItem("Value").Text
      wUnit = oItemNode.Attributes.getNamedItem("Unit").Text
      If Len(wUnit) > 0 Then
         wValue = wValue & " " & wUnit
      End If
   End If
   Set oItemNode = Nothing
   GetItem = wValue
   Exit Function

   Err.Raise vbError, "GetItem()", "Unhandled error in GetItem: " & Err.Description
   Resume Proc_exit

End Function
Unlock this solution and get a sample of our free trial.
(No credit card required)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.