Introduction.
Modernizing Microsoft Access applications with AI is not about replacing Access—it’s about extending it into a far more intelligent, responsive, and future-ready system. With the right integration approach, Access can evolve from a traditional desktop database into a smart, AI-assisted application platform.
If you have not yet visited the first part of this Series: AI Integration in Microsoft Access for VBA Coding Part 1then please visit the Page, then continue.
Intelligent Query & SQL Generation
AI models can translate natural language into SQL queries, dramatically reducing the need for manual query design.
A user types: “Show me dairy products sorted by name.”
AI converts it into a valid Access SQL statement instantly
This is especially powerful for non-technical users and rapid prototyping. Using local LLMs (via tools like Ollama), you can embed this capability directly into Access forms.
AI-Assisted VBA Coding
Writing and maintaining VBA code becomes faster and more reliable with AI assistance.
Generate boilerplate code for forms, reports, and modules
Debug and refactor existing VBA procedures
Auto-document your code for better maintainability
Models such as Mistral or Qwen2.5-Coder can run locally and assist developers directly within Access.
Smart Data Entry & Validation
AI can enhance forms with intelligent suggestions and validation:
Auto-complete fields based on historical data
Detect anomalies (e.g., incorrect pricing, duplicate entries)
Suggest corrections in real time
This reduces human error and improves data quality significantly.
Natural Language Interface (NLI)
Instead of navigating complex forms and menus, users can interact with Access using plain language.
- “Add a new customer from Kochi with the pending invoice.”
- “List the top 10 customers by sales this month.”
- This can be implemented using prompt-driven interfaces embedded in Access forms.
Automated Reporting & Insights
AI can transform raw data into meaningful summaries:
- Generate narrative reports (“Sales increased by 12% this quarter…”)
- Identify trends and outliers automatically
- Suggest actionable insights
This moves Access from a data storage tool to a decision-support system.
I assume that Ollama, Mistral, and qwen2.5-coder:3b are already installed on your Windows 10 or Windows 11 system. However, relying on Command Prompt–based trial runs is neither practical nor efficient for developing production-quality VBA code or generating SQL statements.
Instead, we can design a user-friendly interface in Microsoft Access to execute AI prompts seamlessly. This approach will enable faster and more consistent generation of Access VBA code and SQL queries, significantly improving overall development efficiency.
A Simple Table with only two Fields.
Table Name: AIPrompt.
Table Structure:
| Field Name | Field Type |
|---|---|
| Prompt | LongText |
| Result | LongText |
A Simple Form in Design View is given below for reference:
frmAIPrompt in Design View
The AIPrompt table is linked to the form frmAIPrompt. The TextBox on the left has both its Control Source and Name properties set to the Prompt field of the table, while the TextBox on the right uses the Result field for its Control Source and Name properties.
A command button is placed above the TextBoxes. In addition, a label with the caption “Working, please wait…” is positioned above the Prompt TextBox. This label remains hidden during normal use. When a prompt is executed by clicking the command button, the label becomes visible and stays on screen until the AI response is written to the Result TextBox.
The Result TextBox is locked to prevent accidental edits, but it remains enabled so users can still select and copy its contents as needed.
In a previous session, we downloaded and installed three different programs on our computers. We will now provide a brief introduction to each of them.
1. Ollama
Ollama is a lightweight runtime and management tool that lets you run large language models (LLMs) locally on your computer without relying on cloud-based AI services. It acts as a bridge between raw model files (such as Mistral or Qwen2.5-Coder) and user applications, making it easy to download, install, run, and interact with these models through simple commands or APIs.
In the context of the Local AI models, Ollama performs several key functions.
First, it manages model lifecycle operations - you can pull (download), list, update, and remove models using straightforward commands like Ollama pull mistral.
Second, it provides a local inference engine, meaning it executes the model of your machine (CPU/GPU) and generates responses in real-time.
Third, it offers a standardized API interface allowing developers to integrate local LLM capabilities into applications such as Microsoft Access, web apps, or automation tools.
Fourth, Ollama handles model configuration and optimization, including parameters like temperature and context size, simplifying what would otherwise be complex setup tasks.
Finally, it ensures privacy and offline capability, since all data processing happens locally, an important advantage for sensitive or enterprise data. In short, Ollama transforms complex LLM deployment into a simple, developer-friendly workflow for running AI models locally.
2. Mistral.
The Mistral Large Language Model (LLM) is a family of advanced generative AI models developed by Mistral AI. Designed to understand and generate human language with high efficiency and strong reasoning ability. Built on transformer-based neural network architecture and can perform a wide range of tasks such as text generation, translation, summarization, and code writing by predicting patterns in language data.
Additionally, many Mistral models are released with open weight or open-source licenses, making them highly attractive for developers and enterprises seeking customizable, locally deployable AI solutions.
3. Qwen2.5-Coder:3B
The Qwen2.5-coder:3B model is a compact, code-specialized large language model developed by Alibaba Cloud as part of the broader Qwen2.5 series, designed specifically for programming-related tasks such as code generation, compilation, debugging, and reasoning. It supports multiple programming languages and demonstrates solid performance in real-world development scenarios.
In addition to coding, it retains general language and mathematical reasoning abilities, and its availability in different sizes within the Qwen2.5-Coder family allows developers to choose models based on their computational constraints and application needs.
frmAIPrompt in Form View
Right-click the form image above to open a larger version in a new tab for clearer viewing. The left-side TextBox displays the AI prompt used to generate an Access wrapper class module for a command button control. The required details are entered on separate lines within the prompt.
For simplicity, a basic prompt—such as one for an Access SQL statement or a VBA function—could have been used. However, this more detailed example demonstrates how to structure a multi-step prompt within a TextBox control. Use Ctrl + Enter to insert line breaks and organize the prompt into logical sections on separate lines.
When complete, click the 'AskAI' Command Button to execute the Prompt.
Let us inspect the Command Button Click Event Procedure listed below:
Option Compare Database
Option Explicit
Private Sub cmdClose_Click()
If MsgBox("Close the Form?", vbYesNo + vbInformation, "cmdClose()") = vbYes Then
DoCmd.Close acForm, Me.Name
End If
End Sub
Private Sub cmdRun_Click()
Dim sPrompt As String
Dim sResult As String
Dim sModel As String
Dim sel_Model As Integer
Const m_Model = "mistral"
Const q_Model = "qwen2.5-coder:3b"
sel_Model = Me!Models
If sel_Model = 1 Then
sModel = q_Model
Else
sModel = m_Model
End If
Me.Label5.Visible = True
Me.Repaint
sPrompt = Me!Prompt
' Convert VB Speial characters to
'Java Script Object Notation compatible characters
' Escapes special characters for JSON
sPrompt = Replace(sPrompt, "\", "\\") ' Backslash
sPrompt = Replace(sPrompt, vbCrLf, "\n") ' Line break (Windows)
sPrompt = Replace(sPrompt, vbCr, "\n") 'Cariage Return
sPrompt = Replace(sPrompt, vbLf, "\n") 'Line Feed
sResult = AskLocalAI(sPrompt, sModel)
Me!Result = ""
Me!Result = sResult
Me.Label5.Visible = False
Me.Repaint
Me.Result.SetFocus
End Sub
At the start of the procedure, the selected LLM model is initialized in the sModel variable. The AI prompt, stored in a Long Text field, may include special characters such as line breaks (Ctrl+Enter), double quotes, backslashes, and non-printable characters like carriage return and line feed (vbCrLf). Before submitting the prompt to the chosen AI model, these characters must be properly encoded into a valid JSON string—the format required by the AI for processing.
Note: The response from the AI model is returned in JSON format. The required result must be extracted from the JSON string, specifically from the content between the response and end tags. After reformatting the AI prompt, the AskLocalAI() function is invoked, with the prompt and model name passed as parameters. Once the response is returned by the AskLocalAI() function, it replaces any existing content and is inserted into the Result TextBox on the form.
The AI workflow itself is straightforward: clearly describe the task you want the AI to perform in the Prompt TextBox, ideally in a step-by-step manner. Providing detailed instructions yields more accurate and useful results than a brief, one-line request such as: “Create an SQL query to display Employees’ Last Name, First Name, and City from the Employees table.”
Note: Avoid running the same Task with minor changes repeatedly.
You should continuously refine your ability to write effective AI prompts tailored to your specific task. Be as detailed and explicit as possible when describing your requirements, since clearer instructions lead to more accurate and useful outputs.
When requesting VBA or SQL solutions, explicitly specify that you need Microsoft Access VBA or Microsoft Access SQL in your prompt. This ensures the generated code uses the correct syntax and conventions for Microsoft Access.
If you request only VBA code without specifying the platform, it is typically assumed to be for Microsoft Excel. As a result, the code provided may not be compatible with Microsoft Access.
The AskLocalAI() Function
Option Compare Database
Option Explicit
Function AskLocalAI(aiPrompt As String, ModelName As String) As String
Dim http As Object
Dim JSON As String
Dim Response As String
Dim safePrompt As String
Dim txtResponse As String
Set http = CreateObject("MSXML2.XMLHTTP")
'Escape Double Quptes in Prompt (important)
safePrompt = Replace(aiPrompt, """", "\""") 'double quote to JSON escape Code \"
JSON = "{""model"":""" & ModelName & """,""prompt"":""" & safePrompt & """,""stream"":false}"
http.Open "POST", "http://localhost:11434/api/generate", False
http.setRequestHeader "Content-Type", "application/json"
http.Send JSON
Response = http.responseText
txtResponse = ExtractText(Response)
'Replace escape Code with VBA codes
txtResponse = Replace(txtResponse, "\n", vbNewLine)
txtResponse = Replace(txtResponse, "\u0026", "&")
txtResponse = Replace(txtResponse, "\u003c", "<")
txtResponse = Replace(txtResponse, "\u003e", ">")
txtResponse = Replace(txtResponse, "\", vbNull)
AskLocalAI = txtResponse
End Function
The statement: Set http = CreateObject("MSXML2.XMLHTTP") creates an Instance of the MSXML2.XMLHTTP COM Object.
This object is primarily used to send HTTP requests to servers and receive responses (such as HTML, XML, or JSON data) without a web browser, making it a cornerstone API interaction and automated web tasks in VBA.
The Prompt that was passed to AskLocalAI() is further checked for Double quotes (") in the Prompt; if found, then changes it to (\") JSON Escape code.
Note: JSON escape codes are backslash-prefixed sequences used to represent characters within a JSON string that would otherwise break the data's structure or are impossible to type directly. Because JSON strings must be enclosed in double quotes, any literal double quote inside the string must be escaped so the JSON Parser does not mistake it for the end of the string
Next, the Input information, the Large Language Model Name, and the user's Task Prompt values are validated, formatted, and saved in the JSON String Variable, ready to pass to the Local Cloud Service.
The next two steps, the Local Host Cloud is open for API Service and initialized to accept the JSON input.
The statement: http.Send JSON, transmit the JSON input to the user-selected LLM Model for action.
We will get the response from the AI Model through the statement: Response = http.responseText
The Response variable may contain a significant amount of extraneous information, such as timestamps indicating when the request was received, action logs, and other system-generated metadata. This is typically followed by the actual response to the requested information, which often ends with the word “Done,” and may then be followed by additional system log details.
Note: We will explore JSON Syntax and Structure in detail next week.
The graphical representation of the Response looks like the following:
Log--------- Response >>>>>>>>>>>>>>>>>Done------------Log
We are interested only in retrieving the information between the words: Response and Done, from within the JSON Response String.
There is a small Function to do that job. The VBA Code is given below.
Private Function ExtractText(ByVal jsonText As String) As String
Dim startPos As Long
Dim endPos As Long
startPos = InStr(jsonText, """response"":""") + 12
endPos = InStr(startPos, jsonText, """,""done""")
If endPos > startPos Then
ExtractText = Mid(jsonText, startPos, endPos - startPos)
Else
ExtractText = jsonText
End If
End Function
The first statement in the ExtractText() function searches for the tag "response":. If found, it calculates the character position immediately after this tag by adding the length of "response": plus one (i.e., 12) and saves it in the startPos variable. This value marks the starting point of the AI-generated output.
The next statement searches for the "done" tag, which indicates the end of the relevant output, within the JSON string, and stores its position in the endPos variable. With both the starting and ending positions identified, the function can extract the prompt result from the JSON string.
The following statement validates these positions. If they are valid, it extracts the response text (from startPos to endPos) and returns the result. Otherwise, it returns the entire JSON string—including all extraneous details—to the calling function AskLocalAI().
When the prompt response is extracted, it may still contain JSON-encoded characters (such as "\u0026" for & and "\u003c" for <) and similar codes. A series of additional statements is included to replace these escape sequences with their corresponding VBA-compatible characters.
Finally, the processed response is returned to the cmdAskAI() command button click event procedure, where the result is used to populate the Result TextBox, replacing any existing content.
The 'Working, Please wait...' label disappears. If the process takes too much time your System may hang for some time. Don't panic, give it time, and it will return with the result.
Prompt Engineering Toolkit for Access Developers
Reusable AI Prompts for SQL and VBA
Universal System Prompt (Use Everywhere)
You are a Microsoft Access expert. Generate only valid Microsoft Access SQL or VBA code. Do not include explanations, comments, or alternative options. Follow proper syntax and best practices.
SQL Query Templates
Basic SELECT Query
You are a Microsoft Access expert. Generate only valid Microsoft Access SQL or VBA code. Do not include explanations, comments, or alternative options. Follow proper syntax and best practices. Task: Create a Microsoft Access SQL SELECT query Table: [TableName] Fields: [Field1], [Field2], [Field3] Filter: [Condition] Sort: [Field ASC/DESC] Return only SQL.
JOIN Query
You are a Microsoft Access expert. Generate only valid Microsoft Access SQL or VBA code. Do not include explanations, comments, or alternative options. Follow proper syntax and best practices. Task: Create a Microsoft Access SQL query with JOIN Tables: - Customers (CustomerID, Name) - Orders (OrderID, CustomerID, Amount) Requirement: Join Customers and Orders Show Customer Name and Total Order Amount Return only SQL.
TOP N Query
Task: Create a Microsoft Access SQL query Table: Orders Fields: CustomerID, Amount Requirement: Return top 5 customers by total Amount Return only SQL.
PARAMETER Query
Task: Create a parameter query in Microsoft Access Table: Orders Fields: OrderID, OrderDate, Amount Requirement: Prompt user for Start Date and End Date Return only SQL.
VBA Code Templates
Simple Function
Task: Write a VBA function in Microsoft Access Requirement: Open a DAO Recordset for table Customers Loop through records Print Customer Name to Immediate Window Return only VBA code.
Form Event Procedure
Task: Write VBA code for a Form Button Click event Requirement: On click: - Open query "qrySalesReport" - Display results Return only VBA code.
Error Handling Template
Task: Write a VBA procedure with error handling Requirement: Open a recordset Handle runtime errors properly Show error message Return only VBA code.
Reusable Class Module
Task: Create a VBA class module Requirement: Class Name: clsCustomer Properties: - CustomerID - CustomerName Include Property Let/Get procedures Return only VBA code.
Debugging Templates
Fix SQL Query
Task: Correct the following Microsoft Access SQL query SQL: [Paste SQL here] Return corrected SQL only.
Fix VBA Code
Task: Debug the following VBA code Code: [Paste Code here] Fix errors and return corrected code only.
Explanation Templates (Learning Mode)
Explain SQL
Explain the following Microsoft Access SQL query step by step: [Paste SQL] Keep explanation simple and structured.
Explain VBA
Explain the following VBA code: [Paste Code] Describe what each section does.
Database-Aware Smart Template (Advanced)
Task: Generate SQL based on database structure Tables: Customers(CustomerID, Name, City) Orders(OrderID, CustomerID, Amount) Requirement: Find top 3 customers by total order amount Return only Microsoft Access SQL.
Output Control Add-on (Very Important)
Append this to any prompt:
Rules: - Output only code - No explanation - No comments
Recommended Parameter Settings.
{ "temperature": 0.2, "max_tokens": 150 }
How to Use This Library
- Pick a template
- Replace placeholders
- Send to your local AI
- Copy → Test → Refine
Key Insight for Readers
The quality of AI output depends more on the prompt structure than the model itself.
Will continue Next Week.
Downloads:
AI LOCAL.zip
No comments:
Post a Comment
Comments subject to moderation before publishing.