Private, Local AI for Microsoft Access Data

How a Local Setup Works

• Model runner

  Ollama, LM Studio or a similar tool runs on a PC or server and loads a model into memory.

• Local API

  The runner listens on an address such as http://localhost:11434 or http://ai-server:11434 on your network.

• Access VBA

  Your database posts a prompt to that address and receives the answer, just like a cloud call.

• Firewall

  The AI machine does not need internet access once the model is downloaded.

Hardware: What Is Realistic

Setting Up Ollama on Windows

• Install Ollama for Windows on the machine that will run the model.

• Open a command prompt and pull a model, for example: ollama pull llama3.1:8b (choose a current model suited to your hardware).

• Test it: ollama run llama3.1:8b and type a question.

• For network use, set the OLLAMA_HOST environment variable to 0.0.0.0 so other PCs can reach it, and restrict access with Windows Firewall to your Access users' machines.

Calling the Local Model From Access VBA

Option Compare Database
Option Explicit

Private Const LOCAL_AI_URL As String = "http://ai-server:11434/v1/chat/completions"
Private Const LOCAL_MODEL As String = "llama3.1:8b"

Public Function AskLocalAI(ByVal instruction As String, ByVal inputText As String) As String
    Dim http As Object, body As String
    body = "{""model"":""" & LOCAL_MODEL & """,""temperature"":0," & _
           """messages"":[{""role"":""system"",""content"":""" & JsonEsc(instruction) & """}," & _
           "{""role"":""user"",""content"":""" & JsonEsc(inputText) & """}]}"

    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    http.setTimeouts 5000, 5000, 60000, 300000      ' local models can be slower
    http.Open "POST", LOCAL_AI_URL, False
    http.setRequestHeader "Content-Type", "application/json"
    http.send body

    If http.Status <> 200 Then
        Err.Raise vbObjectError + http.Status, "AskLocalAI", Left$(http.responseText, 200)
    End If
    AskLocalAI = ReadContent(http.responseText)
End Function

Getting Good Results From Smaller Models

• Narrow the task

  One job per call: classify, or extract, or summarise. Not all three.

• Constrain the answer

  Give a fixed list of categories or an exact output format and validate it in a query afterwards.

• Shorten the input

  Send the relevant field, not the whole record. Smaller models degrade on long inputs.

• Use temperature 0

  Consistency matters more than creativity for business data.

• Give examples

  Two or three worked examples in the instruction raise accuracy noticeably on small models.

Local vs Cloud: Choosing Per Task

Typical Private Use Cases

• Clinics: summarising visit notes or classifying referral letters in an Access patient tracker.

• Law firms: tagging matter notes and extracting dates from correspondence.

• HR: categorising exit interview comments without sending them out.

• Finance: classifying transaction descriptions for entities with strict data agreements.

• Labs: extracting values from internal result sheets.

What Local AI Does Not Solve