# 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