Understanding JSON, Temperature, and Max Tokens in Microsoft Access AI Integration (Part 3)
This tutorial explains how Microsoft Access developers can use JSON requests, temperature settings, max_tokens, and prompt engineering techniques when integrating local AI models such as Ollama and qwen2.5-Coder with VBA applications.
How JSON Works in Microsoft Access AI Integration.
When working with AI models through APIs—such as those provided by Ollama—data is exchanged using a structured format known as JSON. JSON (JavaScript Object Notation) is a lightweight, text-based format used to represent data as key-value pairs. It is widely adopted because it is both human-readable and easy for machines to parse efficiently. In the context of Microsoft Access and VBA, JSON serves as the communication bridge between your application and the AI engine, transmitting prompts to the model and returning generated responses in a consistent and predictable structure.
Earlier Post Links:
- How to Integrate AI into Microsoft Access VBA Using Ollama (Part 1)
- Generate Microsoft Access SQL and VBA with Local AI Models (Part 2)
Understanding JSON structure is essential for successfully integrating AI into your Microsoft Access applications. A typical JSON request includes elements such as the model name, prompt text, and optional parameters, all enclosed within curly braces and properly formatted using quotation marks. Similarly, the AI model returns its response in JSON format, which requires careful parsing to extract the relevant output. Even minor formatting issues—such as improperly escaped double quotation marks—can result in errors. Therefore, developing a solid understanding of JSON syntax and structure is a fundamental step toward building reliable AI-assisted VBA solutions.
A visual JSON structure diagram
Understanding JSON Structure (VBA - AI Request & Response)
When your Microsoft Access application communicates with a local AI model (via Ollama), it sends and receives data in JSON format.
This section shows exactly what goes out and what comes back.
1. JSON Request (Sent from VBA to AI)
{
"model": "qwen2.5-coder:3b",
"prompt": "Write a SQL query to list the top 5 customers",
"stream": false
}
Explanation.
- model - AI model name installed locally
- prompt - Your instruction (SQL / VBA / etc.)
- stream - false = return full response at once
2. Same JSON Built in VBA
JSON = "{""model"":""qwen2.5-coder:3b"",""prompt"":""" & safePrompt & """,""stream"": false}"
Visual Mapping
VBA String - JSON Output
---------------------------------------------------------
""model"" - "model"
""prompt"" - "prompt"
""stream"" - "stream"
"""" - "
3. JSON Response (Returned by AI)
{
"model": "qwen2.5-coder:3b",
"created_at": "2026-04-19T10:00:00Z",
"response": "SELECT TOP 5 CustomerID, Name FROM Customers ORDER BY Sales DESC;",
"done": true
}
4. What You Actually Need
"response"
This contains the generated SQL or VBA code.
5. Visual Flow Diagram
[Microsoft Access VBA]
|
Build JSON String
|
Send via HTTP (POST)
|
[Ollama Local API]
|
AI Model Processes Prompt
|
Returns JSON Response
|
Extract "response" Field
|
Use in Access (SQL / VBA)
Common JSON Issue (Very Important)
"prompt": "He said "Hello""
'Invalid JSON (quotes not escaped)
Correct Version
"prompt": "He said \"Hello\""
Practical Tip for Developers
Always ensure:
- Double quotes inside prompts are escaped (\")
- The JSON structure is properly formed
- VBA strings are correctly constructed
Important Points
JSON is the communication bridge between VBA and AI.
If the JSON is incorrect, the AI request will fail—no matter how good your prompt is.
VBA JSON Builder Module (Ready to Use)
Handles escaping (", \, line breaks)
Clean and reusable
Designed for AI prompt calls
1. JSON Escape Function (Core)
Public Function JsonEscape(ByVal text As String) As String
' Escapes special characters for JSON
text = Replace(text, "\", "\\") ' Backslash
text = Replace(text, """", "\""") ' Double quote
text = Replace(text, vbCrLf, "\n") ' Line break (Windows)
text = Replace(text, vbCr, "\r")
text = Replace(text, vbLf, "\n")
JsonEscape = text
End Function
2. Build AI Request JSON
Public Function BuildAIJson(ByVal prompt As String, _
Optional ByVal model As String = "qwen2.5-coder:3b", _
Optional ByVal stream As Boolean = False) As String
Dim json As String
json = "{"
json = json & """model"":""" & model & ""","
json = json & """prompt"":""" & JsonEscape(prompt) & ""","
json = json & """stream"":" & LCase(stream)
json = json & "}"
BuildAIJson = json
End Function
3. HTTP Call Function (Using the Builder)
Public Function AskLocalAI(prompt As String) As String
Dim http As Object
Dim json As String
Set http = CreateObject("MSXML2.XMLHTTP")
json = BuildAIJson(prompt)
http.Open "POST", "http://localhost:11434/api/generate", False
http.setRequestHeader "Content-Type", "application/json"
http.Send json
AskLocalAI = http.responseText
End Function
4. Extract Only AI Response (Clean Output)
Public Function ExtractAIResponse(ByVal json As String) As String
Dim startPos As Long
Dim endPos As Long
startPos = InStr(json, """response"":""")
If startPos = 0 Then Exit Function
startPos = startPos + 12
endPos = InStr(startPos, json, """,""done""")
ExtractAIResponse = Mid(json, startPos, endPos - startPos)
End Function
5. Example Usage
Sub TestAI()
Dim raw As String
Dim result As String
raw = AskLocalAI("Write a Microsoft Access SQL query to list top 5 customers")
result = ExtractAIResponse(raw)
MsgBox result
End Sub
Important Notes
- Always use JsonEscape() for dynamic text
- Avoid manually concatenating quotes
This works reliably for:
- SQL generation
- VBA code generation
- Structured prompts
Optional Enhancement
- temperature
- max_tokens
system prompt
Example:
"temperature": 0.2
1. Temperature Option
Think of Temperature as a randomness dial:
Low temperature (e.g., 0.1 – 0.3)
- More deterministic, consistent, and precise
- Best for SQL, VBA, structured output
Medium (0.4 – 0.7)
- Balanced between accuracy and variation
- Good for explanations or mixed tasks
High (0.8 – 1.0+)
- More creative, but less predictable
- May introduce errors in code
Simple Analogy
Low temperature - Like a careful accountant
High temperature - Like a creative writer
Temperature: 0.2 is Ideal for Microsoft Access development tasks such as:
- Writing SQL queries
- Generating VBA procedures
- Producing structured outputs
You want accuracy, not creativity, so:
'JSON
"temperature": 0.2
Produces: Cleaner syntax, Less unnecessary text, More repeatable results
Example Comparison
Prompt:
'Plain Text
"Write a SQL query to get top 5 customers by sales"
Understanding Temperature Parameter
Option: Temperature: 0.2
'SQL
SELECT TOP 5 CustomerID, SUM(SalesAmount) AS TotalSales
FROM Orders
GROUP BY CustomerID
ORDER BY TotalSales DESC;
Clean and Precise
Option: Temperature: 0.9
'SQL
Here is one possible query you could try:
SELECT CustomerID, SUM(SalesAmount) AS TotalSales
FROM Orders
GROUP BY CustomerID
ORDER BY TotalSales DESC
LIMIT 5;
Issues:
Uses LIMIT (not valid in Access), adds extra text
How to Add It in Your VBA JSON
Just include it in your builder:
'vba
json = json & """temperature"":0.2,"
'Full Example:
'JSON
{
"model": "qwen2.5-coder:3b",
"prompt": "...",
"temperature": 0.2,
"stream": false
}
Temperature Settings
| Task Type | Recommended Temperature |
|---|
| SQL Queries | 0.1 - 0.3 |
| VBA Code | 0.2 - 0.4 |
| Explanation | 0.5 - 0.7 |
| Creative Writing | 0.8+ |
In Short:
- Lower temperature = more reliable code
- Higher temperature = more creative but risky output
Understanding Max Tokens.
2. The max_tokens Parameter
— Controlling Response Length.
What does it?
max_tokens limits the length of the AI’s response.
A token is roughly:
- 1 word = 1–1.5 tokens
- 1 line of code = a few tokens
Why it matters in Access/VBA work
Without a limit, the AI may:
- Add explanations
- Generate overly long code
- Include unnecessary comments
With max_tokens, you keep output concise and predictable
Example
Prompt:
"Write a VBA function to open a recordset"
With max_tokens: 120
'Output:
Function OpenRS()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Customers")
End Function
With max_tokens: 300
Output may include:
- Explanation
- Comments
- Extra variations
max_tokens
| Task Type | Recommended Values |
|---|
| SQL Queries | 50 - 120 |
| Small VBA Function | 100 - 250 |
| Complex Procedure | 300 - 600 |
Rule of Thumb
Use the smallest value that still completes the task.
3. System Prompt Parameter
Prompt — Controlling Behavior
The “system” parameter plays a decisive role in shaping how the AI interprets and responds to a prompt. When you include an instruction such as “You are a Microsoft Access VBA Expert”, you are not merely adding context—you are establishing a behavioral framework for the model. This system-level instruction influences the tone, depth, and technical accuracy of the response by biasing the model toward domain-specific knowledge, coding conventions, and best practices relevant to Access and VBA. As a result, when the actual user prompt follows (e.g., “Create a function to loop through a DAO Recordset”), the AI is far more likely to produce structured, idiomatic, and efficient VBA code rather than a generic or loosely adapted solution.
In practical terms, the system prompt acts as a persistent guiding instruction that remains in effect throughout the interaction, ensuring consistency across responses. It can also enforce output constraints such as:
“return only VBA code”,
“avoid explanations”,
“include error handling”,
which is particularly valuable when integrating AI into automated workflows within Access.
Without a well-defined system prompt, responses may vary in format, include unnecessary explanations, or drift away from the intended technical focus. Therefore, carefully designing the System parameter is essential for achieving reliable, predictable, and production-quality outputs when using AI for tasks like VBA function generation.
What does it do?
The system prompt defines the AI’s role and behavior.
Think of it as:
“Instructions that guide how the AI should respond—every time.”
Why is it powerful?
Instead of repeating instructions in every prompt, you define:
- Tone (professional, concise)
- Output format (code only, no explanation)
- Context (Microsoft Access expert)
Example
Without system prompt:
Write a SQL query for the top 5 customers
Output may include explanations
With system prompt:
You are a Microsoft Access expert.
Return only valid Access SQL.
Do not include explanations.
Output becomes:
SELECT TOP 5 CustomerID, SUM(Amount) AS Total
FROM Orders
GROUP BY CustomerID
ORDER BY Total DESC;
How to Use it in JSON
{
"model": "qwen2.5-coder:3b",
"system": "You are a Microsoft Access expert. Return only SQL or VBA code without explanation.",
"prompt": "Write a SQL query to get the top 5 customers",
"temperature": 0.2,
"max_tokens": 150,
"stream": false
}
Adding to Your VBA JSON Builder
json = "{"
json = json & """model"":""qwen2.5-coder:3b"","
json = json & """system"":""You are a Microsoft Access expert. Return only code."","
json = json & """prompt"":""" & JsonEscape(prompt) & ""","
json = json & """temperature"":0.2,"
json = json & """max_tokens"":150,"
json = json & """stream"":false"
json = json & "}"
Practical Strategy - Combine all three.
- temperature - accuracy
- max_tokens - length control
- system - behavior control
Recommended Default Setup for Access development:
{
"temperature": 0.2,
"max_tokens": 150,
"system": "You are a Microsoft Access expert. Return only valid SQL or VBA code without explanation."
}
Advantages
- temperature - controls randomness
- max_tokens - controls size
- system prompt - controls behavior
Note: Together, they turn AI from “chatty assistant” into a precise development tool.
Constructing JSON string expressions and formatting Name: Value pairs in VBA requires careful practice and attention to syntax. You can experiment with each line of VBA code by assigning the expression to a variable, examining it in the Debug window, and printing the output to verify how the expression is interpreted and stored within the variable.
For Example:
model="qwen2.5-coder:3b"
x="""model"":""" & model & """
? x
"model":"qwen2.5-coder:3b"
y = chr(34) & "model" & chr(34) & ":" & chr(34) & model & chr(34)
? y
"model":"qwen2.5-coder:3b"
z="""stream"":false"
? z
"stream":false
Access Class Module Architecture.
A well-structured VBA Class Module architecture is the cornerstone of integrating AI into Microsoft Access in a maintainable and scalable way. Instead of embedding AI-related logic directly within forms or standard modules, the application should adopt a layered class-based design that encapsulates responsibilities such as prompt construction, API communication, response parsing, and conversation management. A central engine class (for example, clsLocalLLM) acts as the orchestrator, coordinating with supporting classes such as a prompt manager, JSON builder, and response parser. This separation of concerns not only keeps the user interface clean but also allows developers to extend or replace individual components—such as switching between local and cloud-based models—without impacting the rest of the application.
This architectural approach enables Access applications to evolve into intelligent systems while preserving the strengths of VBA and the native Access environment. By treating AI as a reusable service layer implemented through class modules, developers can introduce features, such as natural language querying, automated VBA code generation, and intelligent reporting across multiple forms and databases with minimal duplication. Moreover, this design aligns well with the principles of modular development and code reusability, making it easier to package the entire AI capability as an add-in for broader deployment. In essence, a disciplined class module architecture transforms AI integration from an experimental feature into a robust, production-ready enhancement for modernizing Access applications.
Access AI Interface and VBA Programs
A few enhancements have been made to the Main Form. On the top-right, the list of installed AI models is displayed in an Option Group Control. You may select the appropriate model based on your specific requirements. Choose the first option (qwen2.5-coder) for VBA and SQL-related solutions, and the second option for general-purpose queries.
A Spin Button Control has been added for selecting the AI “Temperature” parameter. The value ranges from 0.1 to 1.0, with the default setting being 0.2, which is ideal for VBA code generation. At this setting, implementation guidance and supplementary information will remain minimal and focused.
A Spin Button Control is also provided for selecting the optional “max_tokens” parameter. The available range is from 1 to 2000, with the default value set to 80.
Two Command Buttons have been added below the Prompt and Response fields to allow quick copying of the TextBox contents to the Clipboard.
The Form’s record navigation buttons have been enabled. You may use a new record for each new AI session, saving the earlier activity.
frmAIMain Module VBA Code.
Option Compare Database
Option Explicit
Const dot As String = " +"
Dim msg As String
Private ai As New clsLocalLLM
Private Sub cboSys_Click()
Me!isysprompt = cboSys.Column(0)
End Sub
Private Sub cmdAskAI_Click()
Dim resultJSON As String
Dim m_prompt As String
Dim sPrompt As String
Dim sModelID As Integer
Dim sTemperature As Double
Dim smax_tokens As Long
Dim txt_SysPrompt As String
'Models
Const q_wen = "qwen2.5-coder:3b"
Const m_ist = "mistral"
Const d_seek = "deepseek-coder:1.3b"
On Error GoTo cmdAskAI_Err
'Get prompt Parameters
sTemperature = IIf(Me!ITemperature = 0, 0.2, Me!ITemperature)
smax_tokens = IIf(Me!ITokens = 0, 120, Me!ITokens)
txt_SysPrompt = Me!cboSys.Column(1) 'column-1 System Prompt text
m_prompt = Trim(Me!Prompt)
'Assign prompt Parameters
If Len(Trim(txt_SysPrompt)) > 0 Then
ai.AI_System = txt_SysPrompt
End If
ai.Temperature = sTemperature
ai.Tokens = smax_tokens
ai.AI_System = txt_SysPrompt
Set ai.mForm = Me
sModelID = Me!iModels
Select Case sModelID
Case 1
ai.Model = q_wen
Case 2
ai.Model = m_ist
Case 3
ai.Model = d_seek
End Select
'Display Working... Please wait message
With Me
.lblMsg.visible = True
.Repaint
End With
'Convert VBA ctrl codes like vbNewLine
'to JSON code: '\n'
sPrompt = ai.RefineRawPrompt(m_prompt)
'send to AI
resultJSON = ai.GenerateResponse(sPrompt)
'Write the AI returned result in the Response Field.
With Me
!Response = "" 'erase existing contents
!Response = resultJSON 'write result to field
.lblMsg.visible = False 'Hide progress label
.Repaint
End With
cmdAskAI_Exit:
Exit Sub
cmdAskAI_Err:
MsgBox Err & " : " & Err.Description, , "cmdAskAI()"
Resume cmdAskAI_Exit
End Sub
Private Sub cmdClose_Click()
If MsgBox("Close the Form?", vbYesNo + vbQuestion, "cmdClose_Click()") = vbYes Then
DoCmd.Close acForm, Me.Name
End If
End Sub
Private Sub cmdPrompt_Click()
Me.Prompt.SetFocus
DoCmd.RunCommand acCmdCopy
End Sub
Private Sub cmdResponse_Click()
Me.Response.SetFocus
DoCmd.RunCommand acCmdCopy
End Sub
Private Sub Form_Current()
On Error GoTo Form_current_Err
With Me
!isysprompt = IIf(Nz(!isysprompt, 0) = 0, 1, !isysprompt)
!ITemperature = IIf(Nz(!ITemperature, 0) = 0, 0.2, !ITemperature)
!ITokens = IIf(Nz(!ITokens, 0) = 0, 120, !ITokens)
!iModels = IIf(Nz(!iModels, 0) = 0, 1, !iModels)
End With
Form_current_Exit:
Exit Sub
Form_current_Err:
MsgBox Err & ": " & Err.Description, , "Form_current()"
Resume Form_current_Exit
End Sub
Private Sub Form_load()
With Me
!Temperature = !ITemperature * 10
If Nz(Me!ITokens, 0) = 0 Then
Me!Tokens = 120
Else
Me!Tokens = !ITokens
End If
.TimerInterval = 0
.Recalc
End With
msg = "Working, please wait "
End Sub
Private Sub Form_Timer()
msg = msg & dot
lblMsg.Caption = msg
DoEvents
End Sub
Private Sub Temperature_Updated(Code As Integer)
'Convert to proper value range
Me!ITemperature = (Me!Temperature / 10)
Me.Refresh
End Sub
Private Sub Tokens_Updated(Code As Integer)
Me!ITokens = Me!Tokens
Me.Refresh
End Sub
Building JSON Request in VBA.
The clsLocalLLM Class Object Properties and Methods.
The above Class Module has all the AI running Methods and properties. The project Diagram is given below for quick reference.
The clsLocalLLM Class Module VBA Code is given below.
Option Compare Database
Option Explicit
'=============================
' Private Members
'=============================
Private m_Model As String
Private m_System As String
Private m_EndPoint As String
Private m_Temperature As Double
Private m_Timeout As Long
Private m_Tokens As Long
Private m_Form As Access.Form
'=============================
' Class Initialize
'=============================
Private Sub Class_Initialize()
m_Model = "qwen2.5-coder:3b"
m_System = "You are a Microsoft Access expert. Return only code."
m_EndPoint = "http://localhost:11434/api/generate"
m_Temperature = 0.2
m_Tokens = 120
m_Timeout = 60
End Sub
'=============================
' Public Properties
'=============================
Public Property Set mForm(ByRef value As Access.Form)
Set m_Form = value
End Property
Public Property Get Model() As String
Model = m_Model
End Property
Public Property Let Model(ByVal value As String)
m_Model = value
End Property
Public Property Get Endpoint() As String
Endpoint = m_EndPoint
End Property
Public Property Let Endpoint(ByVal value As String)
m_EndPoint = value
End Property
Public Property Get AI_System() As String
AI_System = m_System
End Property
Public Property Let AI_System(ByVal value As String)
m_System = value
End Property
Public Property Get Temperature() As Double
Temperature = m_Temperature
End Property
Public Property Let Temperature(ByVal value As Double)
m_Temperature = value
End Property
Public Property Get Tokens() As Long
Tokens = m_Tokens
End Property
Public Property Let Tokens(ByVal value As Long)
m_Tokens = value
End Property
Public Property Get TimeoutSeconds() As Long
TimeoutSeconds = m_Timeout
End Property
Public Property Let TimeoutSeconds(ByVal value As Long)
m_Timeout = value
End Property
'=============================
' Public Methods
'=============================
Public Function GenerateResponse(ByVal Prompt As String) As String
On Error GoTo ErrorHandler
Dim http As Object
Dim jsonBody As String
Dim rawResponse As String
Dim extResp As String
Set http = CreateObject("MSXML2.XMLHTTP")
'Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
'http.setTimeouts 5000, 5000, 5000, 60000
jsonBody = BuildjsonBody(Prompt)
http.Open "POST", m_EndPoint, True 'False
http.setRequestHeader "Content-Type", "application/json"
http.send jsonBody
m_Form.TimerInterval = 10000
Do While http.ReadyState <> 4
DoEvents
Loop
m_Form.TimerInterval = 0
rawResponse = http.responseText
extResp = ExtractResponse(rawResponse)
'Replace JSON escape Codes, if found, with VBA codes
extResp = Replace(extResp, "\n", vbNewLine)
extResp = Replace(extResp, "\u0026", "&")
extResp = Replace(extResp, "\u003c", "<")
extResp = Replace(extResp, "\u003e", ">")
extResp = Replace(extResp, "\", vbNull)
GenerateResponse = extResp
Set http = Nothing
ErrorHandler_Exit:
Exit Function
ErrorHandler:
MsgBox "Error: " & Err.Description
Set http = Nothing
Resume ErrorHandler_Exit
End Function
Public Function RefineRawPrompt(ByVal rawPrompt As String) As String
Dim rPrompt As String
On Error GoTo ErrorHandler
'Find VBA invalid CTRL characters of JSON
'and replace valid control codes.
rPrompt = rawPrompt
rPrompt = Replace(rPrompt, """", "\""")
rPrompt = Replace(rPrompt, vbCrLf, "\n")
rPrompt = Replace(rPrompt, vbCr, "\r")
rPrompt = Replace(rPrompt, vbLf, "\n")
rPrompt = Replace(rPrompt, "'", "\u0027")
rPrompt = Replace(rPrompt, "\", "\\")
rPrompt = Replace(rPrompt, "/", "\/")
RefineRawPrompt = rPrompt
ErrorHandler_Exit:
Exit Function
ErrorHandler:
MsgBox "Error: " & Err.Description
Resume ErrorHandler_Exit
End Function
'=============================
' Private Helpers
'=============================
Private Function BuildjsonBody(ByVal Prompt As String) As String
Dim safePrompt As String
On Error GoTo ErrorHandler
safePrompt = Replace(Prompt, """", "\""")
BuildjsonBody = "{""model"":""" & m_Model & """," & _
"""system"":""" & m_System & """," & _
"""prompt"":""" & safePrompt & """," & _
"""stream"":false," & _
"""options"":{" & _
"""temperature"":" & m_Temperature & "," & _
"""max_tokens"":" & m_Tokens & "}}"
ErrorHandler_Exit:
Exit Function
ErrorHandler:
MsgBox "Error: " & Err.Description
Resume ErrorHandler_Exit
End Function
Private Function ExtractResponse(ByVal jsonText As String) As String
Dim startPos As Long
Dim endPos As Long
On Error GoTo ErrorHandler
startPos = InStr(jsonText, """response"":""")
If startPos = 0 Then
ExtractResponse = jsonText
Exit Function
End If
startPos = startPos + 12
endPos = InStr(startPos, jsonText, """,""done""")
If endPos > startPos Then
ExtractResponse = Mid(jsonText, startPos, endPos - startPos)
Else
ExtractResponse = jsonText
End If
ErrorHandler_Exit:
Exit Function
ErrorHandler:
MsgBox "Error: " & Err.Description
Resume ErrorHandler_Exit
End Function
The above Program procedures were explained in detail in the second episode of this topic, viz. Access and AI Integration Part 2.
The primary VBA functions used in this application are GenerateResponse() and ExtractResponse(). The GenerateResponse() function sends the user’s prompt to the selected AI model via the Local API using HTTP.send jsonBody. The AI model processes the JSON request and returns the generated output through http.responseText, which is stored in the rawResponse variable. The ExtractResponse() function retrieves the actual result generated for the user’s prompt and stores it in the extResp variable.
Within the extResp response text, certain VBA control characters or special characters, such as <, >, &, and similar symbols, may appear as JSON escape codes. Therefore, it is advisable to validate and replace these special characters with their corresponding VBA-compatible characters.
This conversion process is performed in the final steps of the GenerateResponse() function, as shown in the following lines of code.
extResp = ExtractResponse(rawResponse)
'Replace JSON escape Codes, if found, with VBA codes
extResp = Replace(extResp, "\n", vbNewLine)
extResp = Replace(extResp, "\u0026", "&")
extResp = Replace(extResp, "\u003c", "<")
extResp = Replace(extResp, "\u003e", ">")
extResp = Replace(extResp, "\", vbNull)
GenerateResponse = extResp
Best Prompt Templates for Microsoft Access Developers.
Prompt Engineering Toolkit for Access Developers
Reusable AI Prompts for SQL and VBA
Below is a Standard Prompt Template Library designed specifically for Microsoft Access (SQL + VBA) with local AI models (e.g., Ollama using qwen2.5-coder:3b).
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
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
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.
Note: The quality of AI output depends more on the prompt structure than the model itself.
Frequently Asked Questions.
Can Microsoft Access connect to AI models?
Yes. Microsoft Access can connect to local AI models through HTTP APIs such as Ollama.
What is the best AI model for VBA Code generation?
Qwen2.5-Coder and similar coding models generally produce better VBA and SQL output.
Can I generate Microsoft Access SQL queries?
Yes. AI models can translate natural language instructions into Access-compatible SQL.
Demo Databases Download.
Related Articles
- AI Integration in Microsoft Access Part 1
- AI Integration in Access VBA and SQL Part 2
- Wrapper Class Module Creation Wizard-31
- wrapper-class-template-wizard-v2
AI3_IntegrationV3.zip
