Complete Guide to Microsoft Access AI Integration

Contents: 

1. Installing Ollama

Read More

Access and AI Integration Part 3

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:

1. AI Integration in Microsoft Access Part 1
2. AI Integration in Microsoft Access Part 2

Right-click the image to open Large-Image in a new Tab

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

1. Always use JsonEscape() for dynamic text
2. Avoid manually concatenating quotes

3. 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



Right-click the image to open Large-Image in a new Tab

• 
  

1. 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.

2. 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.

3. 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.

4. Two Command Buttons have been added below the Prompt and Response fields to allow quick copying of the TextBox contents to the Clipboard.

5. 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.



Right-click the image to open Large-Image in a new Tab

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).

1. 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.
   

2. 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.
   
   

3. 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.
   
   

4. 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.
   
   

5. 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.
   
   

6. 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.
   
     

7. Output Control Add-on (Very Important)

   Append this to any prompt:

   Rules:
   
   - Output only code
   
   - No explanation
   
   - No comments
   
   

8. Recommended Parameter Settings

           
           {
   
     "temperature": 0.2,
   
     "max_tokens": 150
   
   }
   
   

How to Use This Library

1. Pick a template

2. Replace placeholders

3. Send to your local AI

4. Copy - Test - Refine.

Note: The quality of AI output depends more on the prompt structure than the model itself.




Frequently Asked Questions.

1. Can Microsoft Access connect to AI models?

   Yes. Microsoft Access can connect to local AI models through HTTP APIs such as Ollama.

2. What is the best AI model for VBA Code generation?

   Qwen2.5-Coder and similar coding models generally produce better VBA and SQL output.

3. Can I generate Microsoft Access SQL queries?

   Yes. AI models can translate natural language instructions into Access-compatible SQL.

Demo Databases Download.

AI3_IntegrationV3.zip



Related Articles

1. AI Integration in Microsoft Access Part 1
2. AI Integration in Access VBA and SQL Part 2
3. Wrapper Class Module Creation Wizard-31
4. wrapper-class-template-wizard-v2

Read More

AI Integration in Access VBA and SQL Part 2

Generate Microsoft Access SQL and VBA with Local AI Models (Part 2).

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 visited the first part of this series, AI Integration in Microsoft Access for VBA Coding Part 1, then please visit the Page and 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:

AIPrompt Table

Field Name	Field Type
Prompt	LongText
Result	LongText

A Simple Form in Design View is given below for reference:

frmAIPrompt in Design View

Right-click to open Large Image in New Tab/Window.

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 to open Large Image in New Tab/Window.

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

1. 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.
   

2. 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.
   

3. 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
   
   
   DO NOT:
   - Explain the code
   - Add Instruction text
   - Add conclusion text
   - Use markdown
   - code fences
   
   
   

   Form Event Procedure

   Task: Write VBA code for a Form Button Click event
   
   Requirement:
   
   On click:
   
   - Open query "qrySalesReport"
   
   - Display results
   
   DO NOT:
   - Explain the code
   - Add Instruction text
   - Add conclusion text
   - Use markdown
   - code fences
   
   
   
   

   Error Handling Template

   Task: Write a VBA procedure with error handling
   
   
   Requirement:
   
   Open a recordset
   
   Handle runtime errors properly
   
   Show error message
   
   
   DO NOT:
   - Explain the code
   - Add Instruction text
   - Add conclusion text
   - Use markdown
   - code fences
   
   
   
   

   Reusable Class Module

   Task: Create a VBA class module
   
   Requirement:
   
   Class Name: clsCustomer
   
   Properties:
   
   - CustomerID
   
   - CustomerName
   
   
   Include Property Let/Get procedures
   
   
   
   DO NOT:
   - Explain the code
   - Add Instruction text
   - Add conclusion text
   - Use markdown
   - code fences.
   
   

4. 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.
   
   

5. 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.
   
   

6. 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.
   
   

7. Output Control Add-on (Very Important)

   Append this to any prompt:

   Rules:
   
   - Output only code
   
   - No explanation
   
   - No comments
   

8. Recommended Parameter Settings.

   {
   
     "temperature": 0.2,
   
     "max_tokens": 150
   
   }
   

How to Use This Library

1. Pick a template
2. Replace placeholders
3. Send to your local AI
4. 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:

Demo Databases Download.

AI LOCAL.zip

1. AI Integration in Microsoft Access Part 1
2. Wrapper Class Module Creation Wizard-31
3. wrapper-class-template-wizard-v2

Read More

AI Integration in Microsoft Access for VBA Coding Part 1

Local AI for Microsoft Access SQL and VBA Coding - Part 1.

The idea of using Artificial Intelligence within Microsoft Access may sound complex at first, but recent advancements have made it surprisingly accessible—even on a standard Windows 10/11 PC. With lightweight local AI runtime, such as 'Ollama' and efficient coding-focused models like Qwen2.5-Coder, Access developers can now experiment with AI-assisted SQL and VBA generation directly from within Microsoft Access, without relying on cloud services.

Right-click to open Large Image in New Tab/Window.

This first step is intentionally simple: install Ollama, download a compact model such as qwen2.5-coder:3b, and run a few test prompts. For example, you can ask the model to generate an SQL query to retrieve top customers or to write a basic VBA function to open a recordset. Within seconds, the AI responds with structured output that you can study, refine, and adapt into your own applications. This hands-on approach helps demystify AI and demonstrates its immediate practical value.

From a hardware perspective, one of the key advantages of smaller models like Qwen2.5-Coder (3B) is that they are optimized for modest systems. A modern dual-core or quad-core processor (Intel i5/Ryzen 5 or equivalent) with at least 8 GB RAM can run such models comfortably for basic tasks, although 16 GB RAM is recommended for smoother performance and multitasking. Since the model runs locally, you also benefit from improved data privacy and independence from internet connectivity.

At this stage, the objective is not to build a full integration, but to gain familiarity and confidence. By interacting with the model through simple prompts in the Command Prompt, developers begin to understand how AI interprets instructions, generates SQL syntax, and structures VBA procedures. This foundational experience is essential before moving on to deeper integration within Access using VBA and API calls.

In the sections that follow, we will walk through the installation steps, demonstrate sample prompts, and explore how even a basic setup can assist in writing queries and VBA code. Once you see how quickly AI can respond to your development needs, you may begin to view it not as a replacement but as a reliable assistant in your everyday Access workflow.

AI Environment for Microsoft Access Developers.

Setting up a local AI environment for Microsoft Access development is a powerful idea—especially for automating SQL generation, VBA code assistance, and database analysis without relying on cloud services.

Here’s a clear, practical step-by-step guide tailored to the development workflow.

1. Choose a Local AI Engine

We need a locally running LLM (Large Language Model).

Recommended options:

• Ollama (Best for simplicity)

• LM Studio (GUI-based)

• GPT4All

Best choice for developers: Ollama (lightweight + API support)

2. Install Ollama (Core Engine)

Steps:

• Download OllamaSetup.exe for Windows from: https://ollama.com

• Install OllamaSetup.exe

• Verify installation:

Open Windows Command Prompt and Type:

Download and Install the Large Language Model: Mistral, like ChatGPT. The 'pull' command downloads the model from source.

C:\Users\HP>ollama pull mistral  
C:\Users\HP>ollama run mistral

Download and Install the SQL and Code-generating AI model: qwen2.5-coder:3b with 3 billion parameters. The model is downloaded and installed with the following command. 
C:\Users\HP>ollama pull qwen2.5-coder:3b
C:\Users\HP>ollama run qwen2.5-coder:3b

'Display the Installed Model's information.

C:\Users\HP>ollama --version
ollama version is 0.20.7

C:\Users\HP>ollama list
NAME                ID              SIZE      MODIFIED
qwen2.5-coder:3b    f72c60cabf62    1.9 GB    7 weeks ago - if installed
mistral:latest      6577803aa9a0    4.4 GB    7 weeks ago - if installed

C:\Users\HP>

The VBA code-generating AI is installed on your machine. You need an Intel i5 or i7 Machine for better performance.

>>>      The Coder AI 'prompt' input line appears. Let us try it out in the Command Prompt.

Type /? or /help for help

>>> /?  

Available Commands:

  /set            Set session variables

  /show           Show model information

  /load    Load a session or model

  /save    Save your current session

  /clear          Clear session context

  /bye            Exit

  /?, /help       Help for a command

  /? shortcuts    Help for keyboard shortcuts

Use """ to begin a multi-line message.


>>> /show

Available Commands:

  /show info         Show details for this model

  /show license      Show model license

  /show modelfile    Show Modelfile for this model

  /show parameters   Show parameters for this model

  /show system       Show system message

  /show template     Show prompt template


>>> /show info

  Model

    architecture        qwen2

    parameters          3.1B

    context length      32768

    embedding length    2048

    quantization        Q4_K_M



  Capabilities

    completion

    tools

    insert

  System

    You are Qwen, created by Alibaba Cloud. You are a helpful assistant.



  License

    Qwen RESEARCH LICENSE AGREEMENT

    Qwen RESEARCH LICENSE AGREEMENT Release Date: September 19, 2024

    ...



>>> Hello?

Hello! How can I assist you today?

'SQL SELECT Query creation AI PROMPT:

>>> """Create SELECT SQL

... Table: Employees

... Fields: FirstName, LastName, City, Jobtitle

... Filter: Jobtitle = "Sales Representative"

... Sort: LastName"""

Certainly! Here is the SQL query that selects employees with the job title "Sales Representative", 
filters them by city, and sorts the results by last name:


```sql

SELECT FirstName, LastName, City, Jobtitle

FROM Employees

WHERE Jobtitle = 'Sales Representative'

ORDER BY LastName;

```

This query will return a list of sales representatives from all cities, sorted alphabetically by their last names.

AI Prompt for creating a VBA Function to calculate the Volume of a tree log.

Type the following Prompt on the command line exactly as shown below. Start typing three quote characters next to each other, then the Prompt text in multiple lines, by pressing the Enter Key on each line, and type 3 quotes at the end of the AI Prompt finish line, indicating the end of the Prompt, and press Enter Key to execute.

>>> """Create an MS Access VBA Function to calculate the Volume of a Tree Log
... Input Parameter values: Circumference value in Inches
... Log Length value: in Feet"""

To create an MS Access VBA function that calculates the volume of a tree log using the provided parameters, you need to consider that the volume is calculated by assuming the cross-sectional area of the log is a circle. Here's a simple function that does this:

1. Open Microsoft Access.
2. Press `Alt + F11` to open the VBA editor.
3. In the Project Explorer, right-click on "Microsoft Access Objects" and select "Add > Module".
4. Copy and paste the following code into the module:

```vba
Function CalculateVolumeOfLog(circumference As Double, logLength As Double) As Double
    ' Define the value of pi
    Dim pi As Double
    pi = 3.141592653589793

    ' Calculate the radius from the circumference
    Dim radius As Double
    radius = circumference / (2 * pi)

    ' Convert log length from feet to inches
    Dim logLengthInInches As Double
    logLengthInInches = logLength * 12

    ' Calculate the volume of the log
    Dim volume As Double
    volume = pi * (radius ^ 2) * logLengthInInches

    ' Return the calculated volume
    CalculateVolumeOfLog = volume
End Function
```
### Explanation:
- **Step 1**: The `pi` variable is set to its approximate value.
- **Step 2**: The radius \( r \) is calculated using the circumference in inches.
- **Step 3**: The log length in feet is converted to inches by multiplying it by 12.
- **Step 4**: The volume is calculated using the formula for the volume of a cylinder, where the radius is in inches, and the log length is in inches.
>>> /Exit

C:\Users\HP>

NB: Click and drag over the required lines of content in the Command Prompt area and press Ctrl+C to copy. Paste it into a Text File or into the Global Module of  Access. Copy and paste the SQL into the Query SQL Editing Window. That is for now. We will use better methods when run from within MS Access.

The Machine hardware, Memory recommendation, and suitable Models.

Running Qwen2.5-Coder:3B locally on a Windows 10/11 PC (via Ollama) is quite feasible—even without a GPU—but the experience depends heavily on your hardware.

Let’s make it practical and tailored for smooth day-to-day use.

Minimum vs Recommended Hardware

Minimum (Will run, but slower)

• CPU: Dual-core (Intel i3 / Ryzen 3)

• RAM: 8 GB

• Storage: ~6–8 GB free (model + cache)

• GPU: Not required

Expect:

• Slower responses (3–10 seconds per reply)

• Occasional lag with longer prompts

Recommended (Smooth experience)

• CPU: 4–8 cores (Intel i5 / Ryzen 5 or better)

• RAM: 16 GB

• Storage: SSD with 10+ GB free

• GPU: Optional (integrated GPU is fine)

Expect:

• Fast responses (1–3 seconds typical)

• Comfortable coding + debugging workflow

Optimal (Best performance)

• CPU: 8+ cores (Intel i7 / Ryzen 7)

• RAM: 32 GB

• Storage: NVMe SSD

• GPU (optional but powerful):

• NVIDIA with 6–8 GB VRAM (e.g., RTX 3060)

Expect:

• Near-instant responses

• Handles long prompts + large codebases easily

Model Size & Memory Usage

• Qwen2.5-Coder:3B (quantized via Ollama):

• Disk size: ~2–4 GB

• RAM usage during run: ~4–6 GB

So your system must have extra RAM beyond this for smooth multitasking.

Key Performance Factors

1. RAM (Most Important)

   • Less RAM → swapping → slow performance

   • 16 GB is the sweet spot

2. SSD vs HDD

   • SSD = fast model loading

   • HDD = noticeable delay

Strong recommendation: Use SSD

3. CPU Threads

   • More cores = faster token generation

   • LLMs are CPU - parallel friendly

4. GPU (Optional Boost)

   • Ollama can use a GPU, if available

   • Not required for 3B models

Realistic Performance (What You’ll Feel)

Hardware Experience

8GB RAM + HDD Slow, sometimes frustrating

8GB RAM + SSD Usable, but limited

16GB RAM + SSD - Smooth & practical

32GB + GPU - Excellent

For MS Access + Coding Use Case

• Generate SQL queries

• Work with VBA

• Write structured logic in Class Modules.

You’ll benefit from:

• 16 GB RAM (minimum target)

• SSD (mandatory)

• Mid-range CPU (i5/Ryzen 5+)

Final Recommendation

If you’re planning a stable setup:

Best value configuration:

• 16 GB RAM

• SSD

• Intel i5 / Ryzen 5

This will run Qwen2.5-Coder:3b smoothly without frustration

NB: If the 3b model hangs the Access System in the middle of a Code generating run, due to hardware limitations, be patient, it completes the work in the background and comes out. Consider installing the 1b Model: qwen2.5-coder:1b (the lightweight 1 billion parameters Model).

Will Continue >>> Next Week.

1. Wrapper Class Module Creation Wizard-31
2. wrapper-class-template-wizard-v2

Read More

Group-Wise Ranking Using Class Module

Group-Wise Ranking in Microsoft Access Using a Class Module

A Reusable Object-Oriented Approach.

In earlier articles, we explored how to prepare a Rank List in Microsoft Access using procedural VBA. While that method works reliably, modern Access application design benefits greatly from encapsulating business logic inside Class Modules.

Unlike database engines such as Microsoft SQL Server, which provide built-in ranking functions like RANK() and ROW_NUMBER(), Microsoft Access does not natively support group-wise ranking in SQL.

This article demonstrates how to implement ranking logic using a dedicated class module, making the solution:

• Reusable

• Maintainable

• Extensible

• Compatible with local and linked tables

Why Use a Class Module?

Moving ranking logic into a class provides:

✔ Encapsulation of ranking behavior.

✔ Reusability across forms, reports, and automation tasks.

✔ Cleaner separation of concerns.

✔ Easier debugging and future enhancement.

This design is especially useful in structured Access applications that follow modular architecture principles.

Design Overview

The class will:

1. Accept a source table or query.

2. Accept grouping and sorting fields.

3. Accept ranking style (Dense / Competition / Sequential).

4. Execute ranking.

5. Update a designated Rank field.

Step 1: Create the Class Module.

Create a new Class Module and name it:

clsRankList

Step 2: Insert the Following Code.

Option Compare Database

Option Explicit


Private mSource As String

Private mGroupField As String

Private mValueField As String

Private mRankField As String

Private mRankingType As String


Public Property Let SourceTable(ByVal vValue As String)

    mSource = vValue

End Property


Public Property Let GroupField(ByVal vValue As String)

    mGroupField = vValue

End Property


Public Property Let ValueField(ByVal vValue As String)

    mValueField = vValue

End Property


Public Property Let RankField(ByVal vValue As String)

    mRankField = vValue

End Property


Public Property Let RankingType(ByVal vValue As String)

    mRankingType = UCase(vValue)

End Property


Public Sub ExecuteRanking()

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim strSQL As String


    Dim CurrentGroup As Variant

    Dim PreviousValue As Variant

    Dim RankNo As Long

    Dim SkipCount As Long


    If mRankingType = "" Then mRankingType = "DENSE"

    Set db = CurrentDb


    strSQL = "SELECT * FROM [" & mSource & "] " & _

             "ORDER BY [" & mGroupField & "], [" & mValueField & "] DESC;"


    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    If rs.EOF Then Exit Sub

    rs.MoveFirst

    CurrentGroup = Null

    PreviousValue = Null

    RankNo = 0

    SkipCount = 0

    Do While Not rs.EOF        

        If Nz(rs(mGroupField)) <> Nz(CurrentGroup) Then        

            CurrentGroup = rs(mGroupField)

            RankNo = 1

            SkipCount = 1

            PreviousValue = rs(mValueField)           

        Else            

            Select Case mRankingType

                Case "DENSE"

                    If rs(mValueField) <> PreviousValue Then

                        RankNo = RankNo + 1

                    End If            

                Case "COMPETITION"

                    If rs(mValueField) <> PreviousValue Then

                        RankNo = SkipCount

                    End If               

                Case "SEQUENTIAL"

                    RankNo = RankNo + 1                 

            End Select            

            SkipCount = SkipCount + 1

            PreviousValue = rs(mValueField)

        End If        

        rs.Edit

        rs(mRankField) = RankNo

        rs.Update       

        rs.MoveNext        

    Loop

    rs.Close

    Set rs = Nothing
    Set db = Nothing   

End Sub

Step 3: Using the Class

From a standard module, form, or automation routine:

Dim objRank As New clsRankList

With objRank

    .SourceTable = "tblStudentMarks"

    .GroupField = "Class"

    .ValueField = "TotalMarks"

    .RankField = "Rank"

    .RankingType = "COMPETITION"

    .ExecuteRanking

End With

Set objRank = Nothing

Supported Ranking Types

RankingType Behavior

DENSE 1, 2, 2, 3

COMPETITION 1, 2, 2, 4

SEQUENTIAL 1, 2, 3, 4

If no ranking type is specified, DENSE is used by default.

Advantages of This Approach.

1. Clean Object-Oriented Design

   Encapsulates ranking behavior into a single reusable object.

2. Compatible with Linked Tables

   Uses dbOpenDynaset, so it works with ODBC sources.

3. Extensible

   Future enhancements may include:

   • Multiple group levels

   • Dynamic ORDER BY clauses

     Returning ranked recordsets instead of updating tables

     Error handling classes

4. Integration-Friendly

   Works well with form wrappers and structured automation routines.

Performance Notes

• Efficient for moderate datasets.

• Ensure indexes exist on GroupField and ValueField.

• For very large datasets, consider server-side ranking.

Final Thoughts

Encapsulating ranking logic inside a class module elevates the solution from a procedural workaround to a structured, reusable component.

For developers who build modular Microsoft Access applications, this approach promotes:

• Clean architecture

• Reusability

• Maintainability

• Professional-grade design

--oOo--

1. Auto-numbering in Query Column
2. Product Group Sequence with Auto-Numbers.
3. Auto-Number with Date and Sequence Numbers-1.
4. Auto-Number with Date and Sequence Numbers-2.
5. Auto-Number in Query Column Version-2.

Read More

Wrapper Class Template Wizard V2

 Streamlining Form Module Code in Standalone Class Module.

Class Module Template Creation Wizard V2.

The earlier version of the Class Module Wizard was a testing platform for creating Wrapper Class Module templates. Although it served its purpose, the procedure adopted there is somewhat semi-automatic, and I am not satisfied with that either. 

This improved Version 2.0 of the Wizard can create several Class Module Templates for different Object Types in your form. This Version creates approximately 10 frequently used Object Wrapper Class Module Templates based on your selection at a time.

The Screenshot of the Class Template Wizard is given below:

Right-click to open Large Image in New Tab/Window.

The ListBox's Source Data is from the 'ListItems' Table. The Source Table Image is given below.

Right-click to open Large Image in New Tab/Window.

The Table has ten Records with six columns of data, but only the first three columns are shown on the ListBox.

(1) The RecordID - for easier retrieval of Wizard Function Parameters.
(2) The Field/Control list File Name for creating on Disk - The files will be created in the Current Project Folder.
(3) The Class Module Template Name -  suffixed with the word '_Template' is preferred, not mandatory.

The following three Columns of data are used by the Wizard.

(4) The Wizard Function that creates the Class Template - Applicable for all Types of Objects.
(5) The Object TypeName - self-explanatory.
(6) Object Short Name - you may change it to a more descriptive Name if required.

How to Run the WrapperClassWizard from within a Database?

Place the WrapperClass_Wizard.accdb database into a Trusted Folder. 

Open your Database and open the VBE Window.

Select References... from the Tools menu, find the Wizard Database, attach, and select to add it to the selected List of Library Files.

Create a SELECT Query in your Project with the name ListItemsQ using the following SQL:

SELECT ListItems.*
FROM ListItems IN 'D:\DEMO\Code2\WrapperClass_Wizard.accdb';

Change the Folder Path to the location of the WrapperClass_Wizard.accdb correctly.

You can create class module templates with the main object declarations (e.g., TextBox and Form, or any other Control and Form) with their property procedures for up to 10 of the most frequently used controls on the form.

Additionally, you can add a sample Click Event Subroutine Code foundation with all the Data Field Names (eg, TextBox and ComboBox Names) collected from the Form. To achieve this, insert a few lines of code (shown in red) in the Class_Init() Subroutine listing below, to collect each Control Type in separate Groups from the Form and save them into their designated text files on disk when the Form is in open state. 

Note: If you plan to implement this Data Field Name saving procedure for two or more forms, the contents of this text file on Disk will be overwritten with the last open Form's Field Names. 

For creating the TextBox or ComboBox sample Click Event Subroutine for a particular Form, open the Form with its Class_Init() Subroutine Code Lines in red, for a few seconds, and close it before running the Class Template creation Wizard.

I recommend this procedure to collect the Data field names on the Form using Code, because all of them are inserted from the data source Table/Query.  Memorizing all their names correctly for easier event subroutine coding is impractical. 

The code lines (marked in Red) are inserted into the Class_Init() subroutine to write out the Category-wise Field Names into their respective Files on Disk. All TextBox Field Names in one Text File, ComboBox Field Names in another, and so on. Below that is the Class_Init() Subroutine in the Class_ObjInit Class Module of the Employees Form, implemented with this procedure.

Option Compare Database
Option Explicit

Private txt As Data_TxtBox
Private Cbo As Data_CboBox
Private cmd As Data_CmdButton

Private Coll As New Collection
Private Frm As Form

'--------------------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'With Reusable Code
'--------------------------------------------------------------------
'The 'WrapperClassWizard.accdb' attached to this Database
'Check: Tools--> References...
'--------------------------------------------------------------------
'1. The 'Class_ObjInit' Class saves TxtBox/Field Names, Command Button,
'ComboBox Names, and others from the Open Form into TextBoxFields.txt,
'CmdButtonList.txt & ComboBoxList.txt files in the same Database Folder.
'
'2. The 'WrapperClassWizard.accdb' must be attached to the
'Current Project to open up the Wizard Form by running
'the Function: OpenClassWizard() from within a Command button Click.
'
'3. The Wizard Reads the Field Names from tbe above Text Files.
'
'4. The Wizard will use the Field/Control Names(if any) for the sample
'Event Subroutine in the Template. If the file is empty then it will
'create a sample Evet Subroutine without the Control names.
'
'5. This saves Coding time otherwise spent on creating a
'New Wrapper Class Module from scratch and picking the
'Field Names and other Control Names correctly
'From the Form for Event Subroutines.
'--------------------------------------------------------------------
'Author:  a.p.r. pillai
'Date  :  20/06/2024
'Remarks: with Ready-made Data Entry Events Handler
'       : in the Wrapper TextBox Class Module
'       : Suitable for Columnar, Tabular or DataSheet Form
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'--------------------------------------------------------------------

Public Property Get o_Frm() As Form
    Set o_Frm = Frm
End Property

Public Property Set o_Frm(ByRef vFrm As Form)
    Set Frm = vFrm

    Class_Init
End Property

Private Sub Class_Init()
Dim ProjectPath As String
Dim txtPath As String
Dim ComboPath As String
Dim FieldListFile As String
Dim ComboBoxList As String
Dim ctl As Control

On Error GoTo ClassInit_Err

Const EP = "[Event Procedure]"

'Save TextBox & CombBox Names into separate Text Files
'for creating Event Subroutines in the Templates
ProjectPath = CurrentProject.Path & "\"

FieldListFile = DLookup("FieldListFile", "ListItemsQ", "ID = " & wizTextBox)
ComboBoxList = DLookup("FieldListFile", "ListItemsQ", "ID = " & wizComboBox)

txtPath = ProjectPath & FieldListFile
ComboPath = ProjectPath & ComboBoxList

Open txtPath For Output As #1 'TextBoxFields.txt for writing
Open ComboPath For Output As #2 'ComboBoxList.txt

'Instantiate the 'Data_txtBox' Class for each TextBox
'on the Form for streamlined Event Procedures Coding
For Each ctl In Frm.Controls
    Select Case TypeName(ctl)
        Case "TextBox"
    Print #1, ctl.Name 'write Field/TextBox Name in TextBoxFields.txt File
            Set txt = New Data_TxtBox
            Set txt.m_Frm = Frm
            Set txt.m_txt = ctl
            
'//Colin Riddington Technique: Highlighting BackColor on GotFocus
                txt.m_txt.BackColor = 62207 'yellow
                txt.m_txt.BackStyle = 0
    
                txt.m_txt.BeforeUpdate = EP
                txt.m_txt.OnDirty = EP
                
                Coll.Add txt 'Add Data_TxtBox Class Instance to Collection
                Set txt = Nothing 'Reset txt object
      
      Case "CommandButton"
           Set cmd = New Data_CmdButton
            Set cmd.Obj_Form = Frm
            Set cmd.cmd_Button = ctl
                
                cmd.cmd_Button.OnClick = EP
                
                Coll.Add cmd
                Set cmd = Nothing
                
      Case "ComboBox"
    Print #2, ctl.Name 'write ComboBox Names in ComboBoxList.txt File
            Set Cbo = New Data_CboBox
            Set Cbo.m_Frm = Frm
            Set Cbo.m_Cbo = ctl
    
'//Colin Riddington Technique: Highlighting BackColor on GotFocus
                Cbo.m_Cbo.BackColor = 62207
                Cbo.m_Cbo.BackStyle = 0
                
                Cbo.m_Cbo.BeforeUpdate = EP
                Cbo.m_Cbo.OnDirty = EP
                
                Coll.Add Cbo
                Set Cbo = Nothing
                
        Case "ListBox"
            
    End Select
Next
'Close all files
Close #1
Close #2

ClassInit_Exit:
Exit Sub

ClassInit_Err:
MsgBox Err & ": " & Err.Description, , "Class_Init()"
Resume ClassInit_Exit
End Sub

Private Sub Class_Terminate()
While Coll.Count > 0
    Coll.Remove 1
Wend
Set Coll = Nothing

End Sub

The red lines are inserted in the required locations to collect the Data Field and ComboBox Names and record them in separate Text Files in the Project Folder as Input to the Class Module creation Wizard. Similarly, you can insert related Code lines for other Types of Controls on the Form.

The Wizard will look for these files on the disk and check their contents. If control names are found, they will be used for building a sample Click Event Subroutine after the main object declarations and property procedure lines of code.

If the above lines are embedded in the Class_Init() subroutine, we should open and keep the Employees Form for a few seconds and close it before running the wizard options. This will create the controls list in their respective text files, using the predefined file names taken from the wizard table ListItems, through the Select Query ListItemsQ.

The TextBoxFields.txt File contents created from the Employee Form Field Names are listed below for reference:

ID
Company
Last Name
First Name
E-mail Address
Job Title
Address
City
State/Province
ZIP/Postal Code
Country/Region

The Sample Click Event Subroutine Structure Code.

The sample Click Event Subroutine Code structure, created using the Field Names from the TextBoxFields.txt File, is given below.

Private Sub txtBox_Click()
   Select Case txtBox.Name
     Case "ID"
        ' Code
 
     Case "Company"
        ' Code
 
     Case "Last Name"
        ' Code
 
     Case "First Name"
        ' Code
 
     Case "E-mail Address"
        ' Code
 
     Case "Job Title"
        ' Code
 
     Case "Address"
        ' Code
 
     Case "City"
        ' Code
 
     Case "State/Province"
        ' Code
 
     Case "ZIP/Postal Code"
        ' Code
 
     Case "Country/Region"
        ' Code
 
 
   End Select
End Sub

Remove the unwanted Control Names from the List if no Events are invoked from those controls.

Opening the ClassWizard Form.

Open the ClassWizard Form by calling the Function OpenClassWizard() from your Project through a Command Button Click Event Subroutine, with the following sample VBA Code lines:

Private Sub Command25_Click()
	VBE.CodePanes.Item(1).show
	OpenClassWizard VBE.ActiveVBProject.Name
End Sub

The OpenClassWizard() Function Code is given below.

Note: You should not create a Function with the same name: OpenClassWizard() in your Project, where the Wizard is attached as a Library database.

Dim ProjectName As String

'Public Function to Open the Wizard Form
'from the Host Application
Public Function OpenClassWizard(ByVal projName As String)
On Error GoTo OpenClassWizard_Err

ProjectName = projName
DoCmd.OpenForm "ClassWizard", acNormal
    
OpenClassWizard_Exit:
Exit Function

OpenClassWizard_Err:
MsgBox Err & ": " & Err.Description, , "OpenClassWizard()"
Resume OpenClassWizard_Exit
End Function

When the Wizard Form is open in your Application Window, select one or more items from the List of Control Types, like TextBox, Command Buttons, or any other Class Module you need, and click the Run Wizard Command Button. 

The ClassWizard Form Module Code:

Option Compare Database
Option Explicit

Private obj As New Wiz_ObjInit

Private Sub Form_load()
    Set obj.O_Form = Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set obj = Nothing
End Sub

The Wiz_ObjInit Intermediary Class Module VBA Code.

Option Compare Database
Option Explicit

Private Lst As Wiz_ListBox
Private oFrm As Access.Form
Private cmd As Wiz_CmdButton
Private Coll As New Collection
'----------------------------------------------------------
'Streamlining Form Module Code in Stand-alone Class Modules
'With Reusable Structured Event Subroutine Coding.
'----------------------------------------------------------
'Event Procedure Coding in Standalone Class Module is now
'made easier with Readymade Object Wrapper Class Templates
'Demo Event Subroutine is created with the Control Names.
'----------------------------------------------------------
'Program: Wrapper Class Template Wizard
'Author:  a.p.r. pillai
'Date  :  20/06/2024
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'----------------------------------------------------------

Public Property Get O_Form() As Form
   Set O_Form = oFrm
End Property
 
Public Property Set O_Form(ByRef objForm As Form)
   Set oFrm = objForm
   
   Class_Init
End Property

Private Sub Class_Init()
On Error GoTo Class_Init_Err
Dim ctl As Control
Const EP = "[Event Procedure]"
   
For Each ctl In oFrm.Controls
    Select Case TypeName(ctl)
        Case "ListBox"
            Set Lst = New Wiz_ListBox
            Set Lst.Obj_Form = oFrm
            Set Lst.Lst_Box = ctl
                Lst.Lst_Box.OnClick = EP
                
                Coll.Add Lst
            Set Lst = Nothing
            
        Case "CommandButton"
            Set cmd = New Wiz_CmdButton
            Set cmd.Obj_Form = oFrm
            Set cmd.Obj_cmdButton = ctl
                cmd.Obj_cmdButton.OnClick = EP
                
                Coll.Add cmd
            Set cmd = Nothing
            
    End Select
Next

Class_Init_Exit:
Exit Sub

Class_Init_Err:
MsgBox Err & ": " & Err.Description, , "Class_Init()"
Resume Class_Init_Exit
End Sub

Private Sub Class_Terminate()
    While Coll.Count > 0
        Coll.Remove 1
    Wend
    Set Coll = Nothing
    Set Lst = Nothing
    
End Sub

The Class_Init() Subroutine enables the Click Events of the ListBox and Command Button Controls. 

The ListBox Wrapper Class 'Wiz_ListBox' Module VBA Code.

Option Compare Database
Option Explicit
 
Private WithEvents LstBox As Access.ListBox
Private Frm As Access.Form
'----------------------------------------------------------
'Streamlining Form Module Code in Stand-alone Class Modules
'With Reusable Structured Event Subroutine Coding.
'----------------------------------------------------------
'Event Procedure Coding in Standalone Class Module is now
'made easier with Readymade Object Wrapper Class Templates
'Demo Event Subroutine is created with the Control Names.
'----------------------------------------------------------
'Program: Wrapper Class Template Wizard
'Author:  a.p.r. pillai
'Date  :  20/06/2024
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'----------------------------------------------------------

Public Property Get Obj_Form() As Form
   Set Obj_Form = Frm
End Property
 
Public Property Set Obj_Form(ByRef objForm As Form)
   Set Frm = objForm
End Property
 
Public Property Get Lst_Box() As Access.ListBox
   Set Lst_Box = LstBox
End Property
 
Public Property Set Lst_Box(ByRef objListB As Access.ListBox)
   Set LstBox = objListB
End Property

Private Sub LstBox_Click()
On Error GoTo LstBox_Click_Err
  Select Case LstBox.Name
    Case "List1"
        Dim lCount As Integer
        Dim tmpList As ListBox
        Dim j As Integer

        Set tmpList = Frm.List1
        lCount = tmpList.ListCount - 1

        For j = 0 To lCount
            If tmpList.Selected(j) Then
                Frm.cmdRun.Enabled = True
                Exit Sub
            Else
                Frm.cmdRun.Enabled = False
            End If
        Next

  End Select
  
LstBox_Click_Exit:
Exit Sub

LstBox_Click_Err:
MsgBox Err & ": " & Err.Description, , "LstBox_Click()"
Resume LstBox_Click_Exit
End Sub

The Click Event of the ListBox Control checks for the presence of any selected Items in the ListBox. If found, enables the Command Button with the Caption 'Run Wizard'; otherwise, the Command Button is disabled.

The Command Button Wrapper Class 'Wiz_CmdButton' Module Code.

Option Compare Database
Option Explicit
 
Private WithEvents cmdButton As Access.CommandButton
Private Frm As Access.Form
'----------------------------------------------------------
'Streamlining Form Module Code in Stand-alone Class Modules
'With Reusable Structured Event Subroutine Coding.
'----------------------------------------------------------
'Event Procedure Coding in Standalone Class Module is now
'made easier with Readymade Object Wrapper Class Templates
'Demo Event Subroutine is created with the Control Names.
'----------------------------------------------------------
'Program: Wrapper Class Template Wizard
'Author:  a.p.r. pillai
'Date  :  20/06/2024
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'----------------------------------------------------------

Public Property Get Obj_Form() As Access.Form
   Set Obj_Form = Frm
End Property
 
Public Property Set Obj_Form(ByRef objForm As Access.Form)
   Set Frm = objForm
End Property
 
Public Property Get Obj_cmdButton() As Access.CommandButton
   Set Obj_cmdButton = cmdButton
End Property
 
Public Property Set Obj_cmdButton(ByRef vcmdButton As Access.CommandButton)
   Set cmdButton = vcmdButton
End Property
 
Private Sub cmdButton_Click()
On Error GoTo cmdButtonClick_Err
   Select Case cmdButton.Name
     Case "cmdClose"
        DoCmd.Close acForm, "ClassWizard"
        
        
     Case "cmdRun"
        Dim modul As Module
        Dim flag As Boolean
        Dim vbcompo As vbcomponent
        Dim tmpList As ListBox
        Dim wiz As Integer
        Dim strWiz As String

        Dim FunctionName As String
        Dim FieldListFile As String
        Dim ClsTemplate As String
        Dim s_ObjTypeName As String
        Dim s_ObjName As String
        
        Dim msg As String
        Dim lCount As Integer
        Dim j As Integer, k As Integer
        Dim qot As String
        Dim objType As Long
        Dim Dt As Double
        Dim ClsSourceFile As String
        Dim ProjectName As String
        Dim Result As Boolean
        
        qot = Chr(34)
        Set tmpList = Frm.List1
        lCount = tmpList.ListCount - 1
        k = 0

        'Validation Check
        msg = ""
        For j = 0 To lCount
          If tmpList.Selected(j) Then
                'FieldList File
                FieldListFile = CurrentProject.Path & "\" & tmpList.Column(1, j)
        
                'Chek for FieldList File on Disk
                    If Len(Dir(FieldListFile)) = 0 Then
                      Open FieldListFile For Output As #1
                        Print #1, Space(5)
                      Close #1
                    End If
        
                'Class Template Name
                ClsTemplate = tmpList.Column(2, j)
                
                msg = ""
                FunctionName = tmpList.Column(3, j)
                If Len(Nz(FunctionName, "")) = 0 Then
                    FunctionName = "CreateClassTemplate"
                End If
                
                s_ObjTypeName = tmpList.Column(4, j)
                If Len(Nz(s_ObjTypeName, "")) = 0 Then 'Control Type Name column empty
                    msg = "*** Object TypeName not specified!"
                Else
                    objType = ControlTypeCheck(s_ObjTypeName) 'Is it a Valid Control TypeName
                    If objType = 9999 Then 'Name not in specified list
                        msg = "*** object Typename: " & UCase(s_ObjTypeName) & vbCr _
                        & "Not in specified List?"
                    End If
                End If
    
                s_ObjName = tmpList.Column(5, j)
                If Len(Nz(s_ObjName, "")) = 0 Then 'Column is empty
                    msg = msg & vbCr & vbCr & "User-Defined Object Name Column is Empty!"
                End If
                If Len(msg) > 0 Then 'Errors Found
                    msg = msg & vbCr & vbCr & "Errors Found in Item: " & tmpList.Column(0, j) & _
                    vbCr & "Rectify the Errors and Re-run!"
                    MsgBox msg, vbCritical + vbOKCancel, "cmdButton_Click()"
                    Exit Sub
                Else 'No Errors then creaate Template
                'Call the Wizard
Result = CreateClassTemplate(FieldListFile, ClsTemplate, s_ObjTypeName, s_ObjName)
                    If Not Result Then
                        MsgBox "Errors Encountered for '" & ClsTemplate & "'" & vbCr _
                        & "Review/Modify the Parameter value(s) and Re-try."
                    End If
                End If
    End If

Next j
        MsgBox "Class Module Templates Created successfully!"

        'Save created Template Class modules
        DoCmd.RunCommand acCmdCompileAndSaveAllModules

     Case "cmdHelp"
        DoCmd.OpenForm "Wiz_Help", acNormal
        
   End Select

cmdButtonClick_Exit:
Exit Sub

cmdButtonClick_Err:
MsgBox Err & ": " & Err.Description, , "cmdButtonClick()"
Resume cmdButtonClick_Exit
End Sub

It runs a series of checks on the Wizard parameters and validates them before calling the CreateClassTemplate() Function with its parameters.

The Wizard will retrieve the Parameters for the selected items from the ListItems Table and check for the Field List/Control Files from the selected options on the disk. If found, then it retrieves the list of controls, if any, for the sample Event Subroutine.

After validation of parameter values, it calls the Wizard Function CreateClassTemplate(), which creates the Class Module Templates for the selected options, with predefined names in your Project's VBE Window's Navigation Pane.  

The CreateClassTemplate() Wizard Function VBA Code is given below:

Public Function CreateClassTemplate(ByVal FieldListFile As String, _
ByVal ClassTemplateName As String, ByVal strObjTypeName As String, _
ByVal strObjName As String) As Boolean

'----------------------------------------------------------
'Streamlining Form Module Code in Stand-alone Class Modules
'With Reusable Structured Event Subroutine Coding.
'----------------------------------------------------------
'Event Procedure Coding in Standalone Class Module is now
'made easier with Readymade Object Wrapper Class Templates
'Demo Event Subroutine is created with the Control Names.
'----------------------------------------------------------
'Program: Wrapper Class Template Wizard
'Author:  a.p.r. pillai
'Date  :  20/06/2024
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'----------------------------------------------------------

On Error GoTo CreateClassTemplate_Err
Dim j As Long, k As Long, h As Long, CountLines As Long
Dim low As Long, high As Long
Dim FieldList() As String
Dim strItem As Variant
Dim strLines(1 To 33) As String
Dim msg As String
Dim idx As Integer
Dim spacex As String
Dim qot As String
Dim ClsPath As String
Dim vbcompo As vbcomponent

spacex = Chr(32)
qot = Chr(34)
idx = 1

strLines(idx) = "VERSION 1.0 CLASS": GoSub NextIndex
strLines(idx) = "BEGIN": GoSub NextIndex
strLines(idx) = "  MultiUse = -1": GoSub NextIndex
strLines(idx) = "End": GoSub NextIndex
strLines(idx) = "Attribute VB_Name = " & qot & ClassTemplateName & qot: GoSub NextIndex
strLines(idx) = "Attribute VB_GlobalNameSpace = False": GoSub NextIndex
strLines(idx) = "Attribute VB_Creatable = False": GoSub NextIndex
strLines(idx) = "Attribute VB_PredeclaredId = False": GoSub NextIndex
strLines(idx) = "Attribute VB_Exposed = False": GoSub NextIndex
strLines(idx) = "Option Compare Database": GoSub NextIndex
strLines(idx) = "Option Explicit": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Private WithEvents " & strObjName & " as Access." & strObjTypeName: GoSub NextIndex
strLines(idx) = "Private Frm as Access.Form": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Public Property Get Obj_Form() as Access.Form": GoSub NextIndex
strLines(idx) = "   Set Obj_Form = Frm": GoSub NextIndex
strLines(idx) = "End Property": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Public Property Set Obj_Form(ByRef objForm as Access.Form)": GoSub NextIndex
strLines(idx) = "   Set Frm = objForm": GoSub NextIndex
strLines(idx) = "End Property": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Public Property Get Obj_" & strObjName & "() as Access." & strObjTypeName: GoSub NextIndex
strLines(idx) = "   Set obj_" & strObjName & " = " & strObjName: GoSub NextIndex
strLines(idx) = "End Property": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Public Property Set Obj_" & strObjName & "(ByRef v" & strObjName & " as Access." & strObjTypeName & ")": GoSub NextIndex
strLines(idx) = "   Set " & strObjName & " = v" & strObjName: GoSub NextIndex
strLines(idx) = "End Property": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Private Sub " & strObjName & "_Click()": GoSub NextIndex
strLines(idx) = "   Select Case " & strObjName & ".Name"

'Read the Field Names into Array
Open FieldListFile For Input As #1
strItem = ""
j = 0
While Not EOF(1)
    Input #1, strItem
 If Len(Trim(Nz(strItem, " "))) > 0 Then
    j = j + 1
    ReDim Preserve FieldList(1 To j) As String
    FieldList(j) = strItem
 End If
Wend
Close #1

If j > 0 Then 'If 0 then Field List File is empty
    low = LBound(FieldList)
    high = UBound(FieldList)
End If

'Write the Array contents to file
ClsPath = CurrentProject.Path & "\TempClass.cls"
Open ClsPath For Output As #1
For k = 1 To idx
    Print #1, strLines(k)
Next

'Subroutine Lines
If j > 0 Then 'if 0 then Field List file is empty
    For h = low To high
        Print #1, "     Case " & qot & FieldList(h) & qot
        Print #1, "        ' Code"
        Print #1, spacex
    Next
End If
    Print #1, spacex
    Print #1, "   End Select"
    Print #1, "End Sub"
    Print #1, spacex
Close #1

    ' Import the class module
Set vbcompo = Application.VBE.ActiveVBProject.VBComponents.Import(ClsPath)
    
    ' Verify if the imported module is a class module
    'Compile and Save module
    If vbcompo.Type = vbext_ct_ClassModule Then
        CreateClassTemplate = True
        Kill ClsPath
    Else
        CreateClassTemplate = False
        MsgBox "Import failed: Not a class module."
    End If

CreateClassTemplate_Exit:
Exit Function

NextIndex:
idx = idx + 1
Return

CreateClassTemplate_Err:
MsgBox Err & ": " & Err.Description, , "CreateClassTemplate()"
Resume CreateClassTemplate_Exit
End Function

Check the VBE navigation pane for the newly created Template Files. They may not appear immediately in the Database navigation pane.

The Wizard's VBA Code is straightforward. The first 33 lines of Code are standard, with the change of  Object Names and Type declarations inserted at appropriate positions using the Parameters obtained from the ListItems Table.

Next, it checks for the Field Names list file (or the Control Type-related Text File Names given in the ListItems Table) in the TextBoxFieldsList.txt. If Control Names are present in the Text File, they are loaded into an Array in memory.

After that, the first 33 VBA Code lines are written into the TempClass.cls Text File. If the Field List/Control Name lines are found in the Array, it creates a sample Click Event Subroutine Code within the Select Case . . . Case . . . End Select structure for ready-to-write Event Procedure Code wherever applicable.

This sample Event Subroutine structure can be copied for other Event Procedures, like BeforeUpdate(), GotFocus(), and others.

Finally, the TempClass.cls Text File is Imported into the VBE Project's Code Pane. Look for the Class Modules with the _Template Suffix to spot them quickly.

If the Import action is unsuccessful, then display an Error Message. In that case, you should investigate and find the cause of the Error, correct it, and try again. 

You may highlight one or more required Object Template Options from the ListBox, and the Wizard creates them quickly. You can rename the Template Modules or copy the generated Code from the Template Module and paste it into a separate Class module for normal use. Modify the Object Names- part and use it with other types of control.

Click the Help Command Button to display the Help Form in your Application Window. The Help Form Image is shown below.

Right-click to open Large Image in New Tab/Window.

The listItems Record ID Enumerations are given below.

Enum ParamID
    wizTextBox = 1
    wizCommandButton = 2
    wizComboBox = 3
    wizListBox = 4
    wizLabel = 5
    wizOptionGroup = 6
    wizOptionButton = 7
    wizCheckBox = 8
    wizToggleButton = 9
    wizTabControl = 10
End Enum

The Control Type Validating Function Code Listing.

Public Function ControlTypeCheck(ByVal strctl As String) As Long
On Error GoTo ControlTypeCheck_Err
Dim ctrlType(1 To 10) As String
Dim ctrl(1 To 10) As Long
Dim j As Integer

For j = 1 To 10
 Select Case j
    Case 1: ctrlType(j) = "Label": ctrl(j) = acLabel '100
    Case 2: ctrlType(j) = "CommandButton": ctrl(j) = acCommandButton '104
    Case 3: ctrlType(j) = "OptionButton": ctrl(j) = acOptionButton '105
    Case 4: ctrlType(j) = "CheckBox": ctrl(j) = acCheckBox '106 
    Case 5: ctrlType(j) = "OptionGroup": ctrl(j) = acOptionGroup '107 
    Case 6: ctrlType(j) = "TextBox": ctrl(j) = acTextBox '109
    Case 7: ctrlType(j) = "ListBox": ctrl(j) = acListBox '110
    Case 8: ctrlType(j) = "ComboBox": ctrl(j) = acComboBox '111
    Case 9: ctrlType(j) = "ToggleButton": ctrl(j) = acToggleButton '122 
    Case 10: ctrlType(j) = "TabControl": ctrl(j) = acTabCtl '123  
End Select
Next

For j = LBound(ctrlType) To UBound(ctrlType)
    If ctrlType(j) = strctl Then
        ControlTypeCheck = ctrl(j): Exit For
    Else
       ControlTypeCheck = 9999 'Error
    End If
Next

ControlTypeCheck_Exit:
Exit Function

ControlTypeCheck_Err:
MsgBox Err & ": " & Err.Description, , "ControlTypeCheck()"
Resume ControlTypeCheck_Exit
End Function

I hope you are enjoying the new approach to coding with reusable VBA code and easier code maintenance, without the need to constantly interfere with the Form Design View. The 'Streamlining Form Module Code in the Standalone Cass Module' also enhances code portability to other projects, offering a significant advantage.

Demo Databases Download.

WrapperClass_Wizard.zip

1. Reusing Form Module VBA Code for New Projects.
2. Streamlining Form Module Code - Part Two.
3. Streamlining Form Module Code - Part Three
4. Streamlining Form Module Code - Part Four
5. Streamlining Form Module Code - Part Five
6. Streamlining Form Module Code - Part Six
7. Streamlining Form Module Code - Part Seven
8. Streamlining Form Module Code - Part Eight
9. Streamlining Form Module Code - Part Nine
10. Streamlining Form Module Code - Part Ten
11. Streamlining Form Module Code - Part Eleven
12. Streamlining Report Module Code in Class Module
13. Streamlining Module Code Report Line Hiding-13.
14. Streamlining Form Module Code Part-14.
15. Streamlining Custom Made Form Wizard-15.
16. Streamlining VBA Custom Made Report Wizard-16.
17. Streamlining VBA External Files List in Hyperlinks-17
18. Streamlining Events VBA 3D Text Wizard-18
19. Streamlining Events VBA RGB Color Wizard-19
20. Streamlining Events Numbers to Words-20
21. Access Users Group(Europe) Presentation-21
22. The Event Firing Mechanism of MS Access-22
23. One TextBox and Three Wrapper Class Instances-23
24. Streamlining Code Synchronized Floating Popup Form-24
25. Streamlining Code Compacting/Repair Database-25
26. Streamlining Code Remainder Popup Form-26
27. Streamlining Code Editing Data in Zoom-in Control-27
28. Streamlining Code Filter By Character and Sort-28
29. Table Query Records in Collection Object-29
30. Class for All Data Entry Editing Forms-30
31. Wrapper Class Module Creation Wizard-31
32. wrapper-class-template-wizard-v2

Read More