MS Access MCP Explorer

MS Access MCP Explorer

A tool for exploring and querying Microsoft Access databases using MCP (Model-Command-Procedure).

Installation

Install using UV:

uv pip install omni-ms-access-mcp

Or using pip:

pip install omni-ms-access-mcp

Usage

Command Line

Start the MCP server with the path to your Access database:

ms-access-mcp --db-path "C:\path\to\your\database.accdb"

Python API

from omni_ms_access_mcp.server import AccessMCP

# Initialize the MCP server
mcp_server = AccessMCP(db_path="C:/path/to/your/database.accdb")

# Run the server
mcp_server.run()

Available Tools

1. list_databases - List all available databases

   • No parameters required
   • Shows all registered databases with descriptions

2. get_schema_tool - Get database schema information

   • Required parameter: database - Database name to examine
   • Example: get_schema_tool(database="sales_db")

3. query_data - Execute SQL queries across multiple databases

   • Required parameter: sql - SQL query using [database].[table] syntax
   • Example: query_data(sql="SELECT TOP 10 * FROM [sales_db].[Customers]")

4. test_cross_db_connectivity - Test database connections and cross-DB functionality

   • No parameters required
   • Helps diagnose connection issues

5. query_builder_help - Get examples for building Access SQL queries

   • Required parameter: query_type - Type of query help needed
   • Options: simple_select, cross_database_join, union, aggregation, date_filtering, boolean_filtering
   • Example: query_builder_help(query_type="cross_database_join")

6. validate_query_syntax - Validate Access SQL syntax before execution

   • Required parameter: sql - Query to validate
   • Checks for common Access SQL mistakes

7. query_limitations - Learn about Access limitations and workarounds

   • Required parameter: topic - Limitation topic
   • Options: joins, performance, data_types, functions, best_practices, all
   • Example: query_limitations(topic="joins")

Cross-Database Query Support

This tool supports queries across multiple Access databases using a special syntax:

-- Always use [database_name].[table_name] format
SELECT [c].[CustomerName], [o].[OrderTotal]
FROM [sales_db].[Customers] AS [c], [orders_db].[Orders] AS [o]
WHERE [c].[CustomerID] = [o].[CustomerID]
AND [o].[OrderDate] > #2024-01-01#

Important Access SQL Rules

• Dates: Use #YYYY-MM-DD# format (not quotes)
• Booleans: Use 1/0 instead of True/False
• String concatenation: Use & instead of +
• Limit results: Use TOP N instead of LIMIT
• Conditionals: Use IIF() instead of CASE WHEN

Known Limitations

Cross-Database JOINs

Direct JOINs between databases often fail. Use these workarounds:

• WHERE clause filtering: FROM [db1].[t1], [db2].[t2] WHERE [t1].[id]=[t2].[id]
• UNION operations to combine results
• Query databases separately and combine in application code

Performance

• Database files limited to 2GB
• Queries slow down significantly with >100k records
• Always use TOP N or WHERE clauses to limit results

Data Types

• Boolean fields may use 1/0 or -1/0
• Text fields limited to 255 characters (use Memo for larger)
• Use conversion functions: CInt(), CDbl(), CStr(), CDate()

Best Practices

1. Always use [database].[table] syntax for all tables
2. Test with small datasets first
3. Use validate_query_syntax before executing complex queries
4. Run query_limitations tool to understand constraints
5. Keep related data in the same database when possible

Troubleshooting

If you encounter errors:

1. Run test_cross_db_connectivity to check all databases are accessible
2. Use get_schema_tool to verify table and column names
3. Check query_builder_help for correct syntax examples
4. Review query_limitations for known issues and workarounds

Development

Setup Development Environment

uv venv
.venv\Scripts\activate.bat
uv pip install -e .

Building the Package

publish.bat

Publishing to Package Index

uv pip install -U twine
uv python -m twine upload dist/*

Requirements

• Microsoft Access Driver must be installed on your system
• Python 3.7 or higher