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.
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:
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>
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 run qwen2.5-coder:3b
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 /loadLoad 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
RAM (Most Important)
Less RAM → swapping → slow performance
16 GB is the sweet spot
SSD vs HDD
SSD = fast model loading
HDD = noticeable delay
CPU Threads
More cores = faster token generation
LLMs are CPU - parallel friendly
GPU (Optional Boost)
Ollama can use a GPU, if available
Not required for 3B models
Strong recommendation: Use SSD
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.
No comments:
Post a Comment
Comments subject to moderation before publishing.