Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 37 ยท Files, Databases and APIs

Databases and Web APIs from VBA

Query Access, SQL Server and other databases from VBA with ADO, use parameters safely, copy a Recordset to a sheet, and call REST APIs with WinHTTP and JSON.

Excel is often the front end to data that lives somewhere else: an Access file, a SQL Server database, a web service. VBA reaches them through ADO (ActiveX Data Objects) for databases, and through HTTP objects such as MSXML2.XMLHTTP or WinHttp.WinHttpRequest for web APIs.

This lesson shows how to connect, run queries safely, put results on a sheet, update data, and call a JSON API. The code uses late binding, so no library references are needed.

VBA cannot run on this site, so listings are read-only. The JavaScript stand-ins model parameterized queries and JSON handling, and show why parameters matter.

ADO in four steps

flowchart LR
  A["Connection"] --> B["Command (SQL + parameters)"]
  B --> C["Recordset (rows)"]
  C --> D["Worksheet"]

Open a connection, run a command, read the recordset, then close everything.

Connecting

VB
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

' Access (.accdb)
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\shop.accdb;"

' SQL Server with Windows login
' cn.Open "Provider=MSOLEDBSQL;Server=MYSERVER;Database=Shop;Trusted_Connection=yes;"

' Another Excel file as a database
' cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\book.xlsx;" & _
'         "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

Do not store passwords in code. Prefer Windows authentication or ask for credentials at run time.

Reading rows

VB
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT Name, Price FROM Products WHERE Price > 10 ORDER BY Name", cn, 0, 1   ' forward-only, read-only

If Not rs.EOF Then
    ThisWorkbook.Worksheets("Result").Range("A2").CopyFromRecordset rs   ' all rows at once
End If
rs.Close

CopyFromRecordset is the fast way to fill a sheet. To add headers, loop over rs.Fields and write rs.Fields(i).Name. To loop manually use Do While Not rs.EOF ... rs.MoveNext ... Loop.

Parameters: never build SQL by joining user text

VB
Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = cn
    .CommandText = "SELECT Name, Price FROM Products WHERE Category = ? AND Price > ?"
    .Parameters.Append .CreateParameter("cat", 200, 1, 50, "Books")   ' 200 = adVarChar, 1 = input
    .Parameters.Append .CreateParameter("min", 5, 1, , 10)            ' 5 = adDouble
End With
Set rs = cmd.Execute

If you concatenate "... WHERE Name = '" & txtName & "'", a name like O'Brien breaks the query, and an attacker can inject SQL. Parameters (the ? placeholders) solve both problems.

Changing data and transactions

VB
cn.BeginTrans
On Error GoTo Fail
cn.Execute "UPDATE Products SET Price = Price * 1.05 WHERE Category = 'Books'", recordsAffected
cn.CommitTrans
Exit Sub
Fail:
cn.RollbackTrans
MsgBox "Update undone: " & Err.Description

A transaction makes several statements succeed or fail together. Always Close the connection and set the objects to Nothing.

Calling a web API

VB
Function HttpGet(url As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP.6.0")
    http.Open "GET", url, False                 ' False = wait for the answer
    http.setRequestHeader "Accept", "application/json"
    http.send
    If http.Status = 200 Then
        HttpGet = http.responseText
    Else
        Err.Raise vbObjectError + 1, "HttpGet", "HTTP " & http.Status & ": " & http.statusText
    End If
End Function

Function HttpPostJson(url As String, body As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP.6.0")
    http.Open "POST", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.send body
    HttpPostJson = http.responseText
End Function

VBA has no built-in JSON parser. Options are a small tested library such as VBA-JSON (a JsonConverter module) that turns text into nested Dictionary and Collection objects, or a simple targeted parse for tiny responses. With such a library:

VB
Dim data As Object
Set data = JsonConverter.ParseJson(HttpGet("https://api.example.com/items"))
Dim item As Object, r As Long: r = 2
For Each item In data("items")
    ws.Cells(r, 1).Value = item("name")
    ws.Cells(r, 2).Value = item("price")
    r = r + 1
Next item

Practical points: respect the API's rate limits, send API keys in headers and store them outside the workbook, check Status before using the body, and note that XMLHTTP caches GET results, so add a unique query parameter when you need fresh data. Newer Microsoft 365 builds also offer Power Query (Get & Transform) for the same job without macros, which is worth considering for refreshable reports.

Try it: parameters versus string joining

JS Why parameters beat concatenation

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Check the status, then parse JSON into rows

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Concatenating user text into SQL instead of using parameters.
  • Leaving connections open, which locks the database file.
  • Using a client-side cursor on huge tables when forward-only would do.
  • Ignoring HTTP status codes and parsing an error page as data.
  • Hard-coding API keys or passwords in the workbook.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the fastest way to put a Recordset onto a sheet?

    CopyFromRecordset writes all rows in one call.
  2. 2. Why use parameters (?) in SQL commands?

    Parameter values are sent separately from the SQL text, so they cannot change the query's structure.
  3. 3. What should you check first after http.send?

    A non-200 status means the response body may not be the data you expect.
  4. 4. What does a transaction (BeginTrans / CommitTrans) give you?

    If something fails you call RollbackTrans and none of the statements are kept.

Summary

  • ADO flow: Connection > Command/Recordset > CopyFromRecordset > Close.
  • Use ? parameters instead of joining strings; wrap multi-statement changes in a transaction.
  • Web APIs: MSXML2.XMLHTTP with Open, headers, send, then check Status and parse the JSON with a library.
  • Keep secrets out of code, respect rate limits and always close connections.

Next: Project: Sales Report Builder