Content
# 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
```python
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:
```sql
-- 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
MCP Store