使用说明

The paid version has been released link

Tested fastmcp==2.11.3(Recommended) it working 100%

Some versions may not work perfectly.

##new tools

MS Access Knowledge Graph & Token-Reduction MCP Server

A high-performance, 100% local Model Context Protocol (MCP) server for Microsoft Access (.accdb / .mdb).

Inspired by Graphify, this server builds a persistent Entity Relationship Knowledge Graph of your entire Access database. It solves the two biggest challenges when using AI with large databases:

Massive Token Reduction (98.5%+ savings): Instead of dumping tens of thousands of schema tokens into the AI prompt, targeted graph traversal extracts only the relevant subgraphs, foreign keys, and constraints (~400 tokens).
Interactive HTML Database Visualizer: Generates a standalone, zero-dependency 2D Canvas force-directed graph viewer (index.html) to visually explore all tables, relationships, queries, and forms.

Key Highlights

⚡ Headless & Blazing Fast: Powered by native in-process DAO.DBEngine.120 extraction. Full extraction of a complex database like Northwind (161 nodes, 104 edges) takes under 1 second without launching MSACCESS.EXE or triggering GUI startup dialogs.
📉 Ultra-Low Credit / Token Cost: Real-world queries run in ~420 tokens (around 0.23 credits in Kiro / Claude) instead of 35,000 tokens.
🎯 Accurate Access SQL: Automatically generates multi-hop INNER JOIN clauses with Access-compliant nested parentheses:
```
FROM ((([Orders] INNER JOIN [OrderDetails] ON ...) INNER JOIN [Products] ON ...) INNER JOIN [Suppliers] ON ...)
```
🌐 Zero External Dependencies: Built with pure Python standard libraries, pyodbc, and win32com. No external graph packages, no internet connection required.

Token Consumption: How It Saves 98.5%+ Tokens

When an AI agent (Claude Desktop, Kiro IDE, Cursor) interacts with a large database, traditional methods dump the entire schema into the prompt:

[Traditional Approach]
User Prompt ──> AI reads 30+ tables & 500+ columns ──> 35,000 tokens consumed! ($$$)

With this Knowledge Graph Engine:

The database is pre-indexed once into a compact node-link graph (graphify-out/graph.json).
When the user asks a question, the AI invokes an MCP graph tool.
The Python server performs the Breadth-First Search (BFS) or subgraph extraction locally in 0.005 seconds.
The server returns only the exact 5 lines of answer (~100 tokens) to the AI:

[Knowledge Graph Engine]
User Prompt ──> AI calls MCP Tool ──> Python runs BFS on graph.json (0.005s) ──> Returns ~100 tokens! (0.23 credits)

Token Benchmark Comparison

Operation	Without Graph Tools	With Graph Tools	Savings
Find table relationship / JOIN	~25,000 tokens	~420 tokens	98.3%
Inspect target tables for insert	~35,000 tokens	~540 tokens	98.5%
Credit usage in Kiro IDE	10 – 25 credits	0.23 credits	98.8%

New Graph MCP Tools Reference

1. `export_graphify_graph`

Exports the complete database dependency graph to Graphify-standard node-link JSON format and builds the interactive HTML visualizer.

Parameters:
- db_name: Path or filename of the Access database (e.g., K:\mcp\Northwind.accdb).
- output_path: Destination path for graph.json (defaults to graphify-out/graph.json in the database directory).
Returns: Summary metrics (total nodes, edges, tables, foreign keys, queries, forms, and top hub "god nodes").

2. `query_database_context`

Primary token-reduction tool. Performs keyword relevance scoring and BFS graph traversal to return an ultra-compact schema cheat sheet for the AI.

Parameters:
- db_name: Database name or path.
- task_description: Description of what you want to do (e.g., "Add order line item for product").
- relevant_tables: Optional comma-separated table names to focus on (e.g., "Orders,OrderDetails").
- depth: Relationship hop distance (default 1 for direct foreign keys).
- token_budget: Maximum token budget for the returned schema (default 1500).
Returns: Dense Markdown table of only relevant tables, exact types, primary keys, AutoNumber warnings, and foreign key rules.

3. `find_relationship_path`

Finds the shortest relationship path between any two tables and generates the exact MS Access SQL INNER JOIN clause with nested parentheses.

Parameters:
- db_name: Database name or path (or path to pre-built graph.json).
- source_table: Starting table name (e.g., "Products").
- target_table: Target table name (e.g., "ProductVendors").
Returns: Hop count, connecting tables, foreign key field pairs, and pre-formatted Access SQL JOIN.

4. `get_table_neighborhood`

Inspects a single table and its immediate network of parent/child tables and connected queries.

Parameters:
- db_name: Database name or path.
- table_name: Table name to inspect.
- depth: Hop distance (default 1).

5. `generate_interactive_graph_html`

Generates or refreshes the standalone interactive HTML visualization file.

Parameters:
- db_name: Database name or path.
- output_file: Path to save HTML file (default: database_graph.html).

Sample Prompts (Ready to Copy & Paste)

📌 1. Export Graph & Generate Interactive Visualizer

Connect to MS Access database K:\mcp\Northwind.accdb. Export the full dependency knowledge graph to graphify-out/graph.json and generate the interactive index.html visualizer. Show me a summary of total tables, relations, and top hub tables.

📌 2. Find Relationship & SQL JOIN (Consumes only ~400 tokens!)

Find the exact relationship path between Products and ProductVendors in K:\mcp\Northwind.accdb. Return the shortest path of tables and the recommended MS Access SQL INNER JOIN clause with proper nested parentheses.

📌 3. Target Subgraph Before Adding / Editing Records

I need to insert a customer order payment into K:\mcp\Northwind.accdb. Before writing any SQL or code, query the database knowledge graph for task 'Add customer order payment' focusing on tables Orders, Invoices, Customers. Do not load the full database schema.

📌 4. Multi-Hop Relationship Discovery

Find the shortest relationship path connecting Customers and Suppliers in K:\mcp\Northwind.accdb. Show the connecting tables and generate the complete MS Access multi-table INNER JOIN.

📌 5. Create or Edit an Access Form

I want to create an order details form in K:\mcp\Northwind.accdb. First query the knowledge graph for tables Orders and OrderDetails to get the linking fields. Then generate a main form template based on Orders linking to Form.OrderDetailsSubform on field OrderID. Finally, create the form named frmOrderManagement.

Exploring the Interactive HTML Visualizer

After running export_graphify_graph, open graphify-out/index.html in Chrome, Edge, or Firefox:

2D Canvas Force Simulation: Drag tables around, zoom in/out with mouse wheel, pan across the canvas.
Color-Coded Objects:
- 🟢 Green: Tables
- 🔵 Blue: Queries / Views
- 🟣 Purple: Forms
- 🟠 Amber: Reports
- 🔴 Pink: VBA Modules
Live Search & Autocomplete: Type any table or field name in the search bar; the camera smoothly pans and focuses on the node.
Slide-Out Schema Drawer: Click any table node to slide open its full column definitions, data types, Primary Key / Foreign Key / AutoNumber badges, and clickable relationship links.

MCP Server ms_Access 🚀

A powerful Model Context Protocol (MCP) server that provides seamless integration with Microsoft Access databases. This server enables you to create, manage, and query Access databases through MCP-compatible applications like Kiro IDE.

Prerequisites

Windows Operating System (required for Access integration)
Python 3.13+
Microsoft Access Database Engine It is recommended to use the 2016 version.(required - see installation guide below)
uv package manager (recommended)

⚠️ Important: Bit Architecture Compatibility

Python , Microsoft Access,Microsoft Access Database Engine must have the same bit architecture (32-bit or 64-bit). Microsoft Access Database Engine 2016 Redistributable

Choose:

AccessDatabaseEngine.exe → for 32-bit systems or 32-bit Office

AccessDatabaseEngine_X64.exe → for 64-bit Office

🧪 Summary: Feature 2010 Engine 2016 Engine Compatibility Office 2010–2013 Office 2010–2021 New Excel/Access support ❌ Limited ✅ Full Future-proof ❌ No ✅ Yes Stability ✅ Yes ✅ Yes Bitness must match Office ✅ Yes ✅ Yes

Check Your Python Architecture

Open your terminal (CMD or PowerShell) and run:

python -c "import platform; print(platform.architecture())"

This will show either:

('64bit', 'WindowsPE') - You have 64-bit Python
('32bit', 'WindowsPE') - You have 32-bit Python

Check Your Office/Excel Architecture

Open Excel
Click File tab
Choose Account
Click About Excel

You'll see something like:

Microsoft® Excel® 2016 MSO (Version 2506 Build 16.0.18925.20076) 32-bit

Microsoft® Excel® 2019 MSO (Version 2506 Build 16.0.18925.20076) 64-bit

The last part shows whether you have 32-bit or 64-bit Office.

Installing Microsoft Access Database Engine

You must install the Access Database Engine that matches your Python architecture:

For 64-bit Python:

Download: Microsoft Access Database Engine 2016 Redistributable (64-bit)
File: AccessDatabaseEngine_X64.exe

For 32-bit Python:

Download: Microsoft Access Database Engine 2016 Redistributable (32-bit)
File: AccessDatabaseEngine.exe

Installation Notes:

We will run the server inside a virtual environment.

Option 1: Using uv (Recommended)

First, install uv if you haven't already:

# Install uv using pip
pip install uv

```bash
# Clone the repository
git clone https://github.com/ayamnash/MCP_server_ms_access_control.git
cd MCP_server_ms_access_control

# Create virtual environment and install dependencies
uv venv
uv pip install -e .

Option 2: Using pip

# Clone the repository
git clone https://github.com/ayamnash/MCP_server_ms_access_control.git
cd MCP_server_ms_access_control

# Create virtual environment
python -m venv .venv

# Activate virtual environment
# On Windows:
.venv\Scripts\activate

# Install dependencies
pip install -e .

Configuration

Kiro IDE Configuration claude desktop

To use this MCP server with Kiro IDE, add the following configuration to your MCP settings:

Workspace Configuration (`.kiro/settings/mcp.json`)

LIKE AS

{
  "mcpServers": {
    "msaccess": {
      "command": "F:\\mcp_server_ms_access_control1\\.venv\\Scripts\\python.exe",
      "args": [
        "F:\\mcp_server_ms_access_control1\\server.py"
      ]
    }
  }
}

Visual studio code .vscode\mcp.json

{
  "servers": {
    "msaccess": {
      "command": "F:\\mcp_server_ms_access_control1\\.venv\\Scripts\\python.exe",
      "args": [
        "F:\\mcp_server_ms_access_control1\\server.py"
      ]
    }
  }
}

Desktop Application Usage

You can also run the server as a standalone application:

# Activate your virtual environment first
.venv\Scripts\activate

# Run the server
python server.py

Prompt Samples

(1)Using msaccess MCP server to connect C:\Users\it\AppData\Local\Programs\Kiro\treeview.accdb Make sure to save the changes and close the database tool when connecting to it and ensure use this tool when encounter an error. . i creat form to display accounts as tree look at image attached 7.1.png i need create report look like as 7.png image attached using mcp to do that ?

(2)Create a Microsoft Access database named pos.accdb in this path F:\mcp_server_ms_access_control

for a Point of Sale (POS) system with the following structure:

Database Name pos.accdb

📦 Items Table:

ItemID: unique ID (AutoNumber)

ItemName: name of the item

ItemPrice: price per unit

ItemDescription: optional text

🔁 Transactions Table:

TransactionID: unique ID (AutoNumber)

ItemID: link to the Items table

TransactionType: either "Purchase" or "Sales"

Quantity: number of items

TransactionDate: date of transaction

💸 Expenses Table:

ExpenseID: unique ID (AutoNumber)

ExpenseType: type/category of expense

Amount: how much was spent

ExpenseDate: date of expense

Create and save four queries:

Sales Amount Between Two Dates

Calculate the total sales (item price × quantity) filtered by a start and end date.

Purchase Amount Between Two Dates

Calculate total purchases (item price × quantity) between two dates.

Sum of Items Sold Between Two Dates

Group by item name and calculate how many of each item was sold between two dates.

4 detail expense between two dates

Save the queries as:

qry_SalesAmount_BetweenDates

qry_PurchaseAmount_BetweenDates

qry_SumSoldItems_BetweenDates

qry_expense_details

fix Issue may Encountered & Fixed: The only issue was with the Items table creation - the initial ItemDescription field size (500 characters) was too large for Access. I fixed this by reducing it to 255 characters, which is the standard maximum for Access text fields.

All queries use parameter prompts [Start Date] and [End Date] so when you run them in Access, you'll be prompted to enter the date range. The database is ready for use!

============================================

(3)using mcp server to Create a complete Laundry Management application in Microsoft Access name laundry_managemet1.accdb in this folder path F:\mcp_server_ms_access_control1.

Requirements:

Database Structure

Create all necessary tables with proper field names, data types, and primary/foreign keys.

Include at least these entities: tables:-

Customers (CustomerID, Name, Phone, Address, etc.)

LaundryItems (ItemID, Description, PricePerUnit, etc.)

Orders (OrderID, CustomerID, OrderDate, DueDate, Status, etc.)

OrderDetails (OrderDetailID, OrderID, ItemID, Quantity, Subtotal, etc.)

Payments (PaymentID, OrderID, PaymentDate, Amount, PaymentMethod, etc.) after create tables insert data for all table to testing Queries:-

Create queries for:

Orders due today

Total sales per day/month named sales_dm

Unpaid orders

Customer order history

Forms:-

Customer management form (add, edit, delete).

Order entry form with subform for order details.

Payment entry form.

Reports:- creat report named customer_report from Customers tables creat report named sales_dm from sales_dm query

============================================

(4)using mcp server to Create a complete Microsoft Access database for managing a school named School_Management.accdb in this folder path F:\mcp_server_ms_access_control1 . The database should include all necessary tables, queries, forms, and reports, each with clear and consistent naming. The system should support student enrollment, teacher assignments, class schedules, grades, attendance, and fee tracking.

📁 Tables: Students_Table: StudentID, FirstName, LastName, DOB, Gender, Address, Phone, Email, EnrollmentDate, ClassID

Teachers_Table: TeacherID, FirstName, LastName, SubjectSpecialization, Phone, Email, HireDate

Classes_Table: ClassID, ClassName, GradeLevel, TeacherID, RoomNumber

Subjects_Table: SubjectID, SubjectName, ClassID, TeacherID

Grades_Table: GradeID, StudentID, SubjectID, Grade, Term, Year

Attendance_Table: AttendanceID, StudentID, Date, Status (Present/Absent/Late), Remarks

Fees_Table: FeeID, StudentID, AmountDue, AmountPaid, DueDate, PaymentDate, Status

🔍 Queries: Outstanding_Fees_Query: List of students with unpaid fees

Attendance_Summary_Query: Attendance summary per student

Grade_Average_Query: Average grades per class and subject

Teacher_Assignment_Query: Teachers assigned to each class

🧾 Forms: Student_Registration_Form: For entering and updating student details

Teacher_Profile_Form: For entering teacher information

Class_Scheduling_Form: For managing class details and assignments

Grade_Entry_Form: For recording student grades

Attendance_Tracking_Form: For marking and reviewing attendance

Fee_Payment_Form: For recording fee payments

📊 Reports: Student_Report_Card: Displays student grades and performance

Monthly_Attendance_Report: Attendance overview by month

Fee_Collection_Report: Summary of fee payments and dues

Class_Schedule_Report: Overview of class schedules

Teacher_Workload_Report: Summary of teacher assignments and subjects

Ensure relational integrity using primary and foreign keys. Use combo boxes, subforms, and validation rules for usability. Design the interface to be intuitive for school administrators.

📌 Features

v1 features vedeo

v2 features vedeo

v3 features vedeo v3.1 features vedeo

🎨 Form Creation Tools (v3 - NEW!) 📝 generate_form_template – Generate a text template for Access forms

🏗️ create_form_from_llm_text – Create Access forms from text definitions

🗃️ Database Structure Tools 🏗️ create_database – Create an empty Access .accdb database

🧱 create_table – Create a table with specified schema

📋 list_tables – List all tables in the database

📊 Data Management Tools ➕ insert_data – Insert rows into a table

🧮 run_query – Execute SQL queries (SELECT, UPDATE, DELETE, etc.)

🔎 Query Management Tools 💾 save_query – Save a named query inside the Access database

📄 list_saved_queries – List all saved queries in the database

📜 VBA Module Tools (v2) 📚 list_vba_modules – List all VBA modules in the Access database

📖 read_vba_module – Read the code from a specific VBA module

✍️ write_vba_module – Create or replace a VBA module with provided code

❌ delete_vba_module – Delete a VBA module from the database

🚀 run_vba_function – Execute a VBA function and return the result

✨ Form Types Supported:

Single Forms – Standalone forms for data entry and viewing
Subforms – Forms designed to be embedded in other forms (datasheet view)
Main Forms with Subforms – Master-detail forms with embedded subforms and automatic linking

🔧 Enhanced Tools (v3 Improvements):

Improved Error Handling – Better error messages and feedback for all operations
Enhanced Query Management – Fixed parameter handling in saved queries
Optimized Form Generation – Automatic GUID and NameMap generation for robust form creation
Better Field Validation – Improved data type handling and field size validation

Available Tools

The MCP server provides the following tools:

🗄️ Database Management

create_database(db_name: str) - Create a new Access database
list_tables(db_name: str) - List all tables in a database

🏗️ Table Operations

create_table(db_name: str, table_name: str, schema: str) - Create a new table
- Example schema: "ID INT PRIMARY KEY, Name TEXT(100), Age INT"

📊 Data Operations

insert_data(db_name: str, table: str, rows: list[dict]) - Insert data into tables
- Example: [{'ID': 1, 'Name': 'John', 'Age': 30}]
run_query(db_name: str, sql: str) - Execute SQL queries (SELECT, UPDATE, DELETE, etc.)

💾 Query Management

save_query(db_name: str, query_name: str, sql: str) - Save named queries
list_saved_queries(db_name: str) - List all saved queries

📜 VBA Module Management (v2)

list_vba_modules(db_name: str) - List all VBA modules in the Access database
read_vba_module(db_name: str, module_name: str) - Read the code from a specific VBA module
write_vba_module(db_name: str, module_name: str, code: str) - Create or replace a VBA module with provided code
delete_vba_module(db_name: str, module_name: str) - Delete a VBA module from the database
run_vba_function(db_name: str, function_name: str, args: str) - Execute a VBA function and return the result

🎨 Form Creation Tools (v3 - NEW!)

generate_form_template(db_name: str, record_source: str, form_type: str, ...) - Generate a text template for Access forms
- form_type options:
  - "single" - Standard standalone form
  - "subform" - Form designed for embedding (datasheet view)
  - "main" - Form that contains a subform with automatic linking
create_form_from_llm_text(db_name: str, form_name: str, form_text: str) - Create Access forms from text definitions
- Automatically generates GUIDs and NameMaps
- Handles form validation and error correction
- Supports complex form layouts with subforms

📊 Report Creation Tools (v3.1 - NEW!) 📋 create_report_from_source – Create complete Access reports in a single step

📝 generate_report_template – Generate customizable text templates for Access reports

🏗️ create_report_from_template – Create Access reports from text definitions

✨ Report Types Supported:

Tabular Reports – Data displayed in rows and columns (default)
Columnar Reports – Data displayed in a single-column layout
Custom Reports – Fully customizable reports using templates

import win32com.client
adox = win32com.client.Dispatch("ADOX.Catalog")
conn_string = f"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={db_path};"
adox.Create(conn_string)  # This creates the .accdb file

ADOX and why it's more reliable than ODBC
ADOX (ActiveX Data Objects Extensions) is a Microsoft COM library specifically designed for database schema operations like creating databases and tables.

Why ADOX is better than ODBC for creating Access databases:

ODBC (Open Database Connectivity) is a general-purpose database interface that sometimes has registry access issues on Windows
ADOX uses Windows COM (Component Object Model) which has direct access to the Access database engine
ADOX bypasses the registry issues that cause the "Unable to open registry key" errors you were seeing
ADOX is Microsoft's recommended method for programmatically creating Access databases
Here's what happens in the code:


pyodbc 

Driver detection: The code uses pyodbc.drivers() to list available database drivers
Table creation and data operations: After ADOX creates the empty database file, pyodbc is used to:
Connect to the database
Create the  table
Insert sample data
Read data for verification


ADOX: Creates the empty .accdb file
pyodbc: Handles all the SQL operations (CREATE TABLE, INSERT, SELECT)
So the combination gives you the best of both worlds:

ADOX for reliable database file creation
pyodbc for standard SQL operations
This is why your script now works - it uses the most reliable method for each task instead of trying to do everything through ODBC alone.


## Troubleshooting

### Common Issues

1. **Access Driver Not Found**

Exception: Access ODBC driver not found

**Solution:**
- Install Microsoft Access Database Engine 2016 Redistributable
- **Critical:** Ensure the database engine matches your Python architecture (32-bit or 64-bit)
- Check available drivers: `python -c "import pyodbc; print(pyodbc.drivers())"`

2. **Architecture Mismatch Error**

[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch

**Solution:**
- Your Python and Access Database Engine have different architectures
- Check Python architecture: `python -c "import platform; print(platform.architecture())"`
- Check Office architecture: Excel → File → Account → About Excel
- Install matching Access Database Engine version

3. **Office 2016/2019 Installation Conflicts**

You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you currently have 32-bit Office products installed

**Solution:**
- Use the `/quiet` parameter: `AccessDatabaseEngine_X64.exe /quiet`
- Or uninstall existing Office, install database engine, then reinstall Office
- Consider using the same architecture for both Python and Office

4. **Permission Errors**
- Run installer as Administrator
- Check file permissions in the target directory
- Ensure the database file location is writable

5. **Python Path Issues**
- Ensure your virtual environment is activated
- Verify all dependencies are installed: `pip list`
- Check if pywin32 is properly installed: `python -c "import win32com.client"`

### Architecture Compatibility Quick Reference

| Your Setup | Python Architecture | Required Database Engine |
|------------|-------------------|-------------------------|
| 32-bit Office 2016/2019 | 32-bit Python | AccessDatabaseEngine.exe (32-bit) |
| 64-bit Office 2016/2019 | 64-bit Python | AccessDatabaseEngine_X64.exe (64-bit) |
| No Office installed | 32-bit Python | AccessDatabaseEngine.exe (32-bit) |
| No Office installed | 64-bit Python | AccessDatabaseEngine_X64.exe (64-bit) |

### System Requirements

- Windows 10/11
- Microsoft Access 2016+ or Access Database Engine
- Python 3.8 or higher
- At least 100MB free disk space

## Contributing

1. Fork the repository
2. Create a feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request

## License

This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

## Support

- 📧 Email: ayamnash@gmail.com
- 🐛 Issues: [GitHub Issues](https://github.com/ayamnash/MCP_server_ms_access_control/issues)


---

Made with ❤️ for the MCP community