sub-arjun/OMNI-MS-Access-MCP
If you are the rightful owner of OMNI-MS-Access-MCP and would like to certify it and/or have it hosted online, please leave a comment on the right or send an email to henry@mcphub.com.
MS Access MCP Explorer is a tool for exploring and querying Microsoft Access databases using the Model-Command-Procedure (MCP) protocol.
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
-
list_databases - List all available databases
- No parameters required
- Shows all registered databases with descriptions
-
get_schema_tool - Get database schema information
- Required parameter:
database- Database name to examine - Example:
get_schema_tool(database="sales_db")
- Required parameter:
-
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]")
- Required parameter:
-
test_cross_db_connectivity - Test database connections and cross-DB functionality
- No parameters required
- Helps diagnose connection issues
-
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")
- Required parameter:
-
validate_query_syntax - Validate Access SQL syntax before execution
- Required parameter:
sql- Query to validate - Checks for common Access SQL mistakes
- Required parameter:
-
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")
- Required parameter:
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/0instead ofTrue/False - String concatenation: Use
&instead of+ - Limit results: Use
TOP Ninstead ofLIMIT - Conditionals: Use
IIF()instead ofCASE 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 NorWHEREclauses 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
- Always use
[database].[table]syntax for all tables - Test with small datasets first
- Use
validate_query_syntaxbefore executing complex queries - Run
query_limitationstool to understand constraints - Keep related data in the same database when possible
Troubleshooting
If you encounter errors:
- Run
test_cross_db_connectivityto check all databases are accessible - Use
get_schema_toolto verify table and column names - Check
query_builder_helpfor correct syntax examples - Review
query_limitationsfor 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
