PHP PDO 連接 Access 資料庫：完整封裝類與使用範例

背景

雖然 Access (.mdb/.accdb) 非高併發首選，但在輕量級專案、遷移維護、本地報表等場景仍有需求。PHP 透過 PDO + ODBC 驅動可實現跨平台連接。

前置條件

• Windows：系統自帶 Microsoft Access Driver (*.mdb, *.accdb)
• Linux：需安裝 unixODBC + mdbtools，配置 /etc/odbcinst.ini
• PHP 啟用 pdo_odbc 擴展

資料庫連接類實現

<?php
class AccessPDO {
    private $conn;
    private $dbPath;

    public function __construct(string $dbPath) {
        $this->dbPath = $dbPath;
        $this->connect();
    }

    private function connect(): void {
        $dsn = "odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq={$this->dbPath};";
        try {
            $this->conn = new PDO($dsn, '', '', [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);
        } catch (PDOException $e) {
            throw new Exception("Access 連接失敗: " . $e->getMessage());
        }
    }

    // 查詢單筆
    public function fetchOne(string $sql, array $params = []): ?array {
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch() ?: null;
    }

    // 查詢多筆
    public function fetchAll(string $sql, array $params = []): array {
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    // 執行增刪改
    public function execute(string $sql, array $params = []): int {
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }

    // 獲取最後插入 ID（Access 需額外查詢）
    public function lastInsertId(string $table, string $pk = 'id'): int {
        $stmt = $this->conn->query("SELECT MAX($pk) FROM $table");
        return (int)$stmt->fetchColumn();
    }
}

使用範例

<?php
require_once 'AccessPDO.php';

$db = new AccessPDO(__DIR__ . '/data/mydb.mdb');

// 查詢
$user = $db->fetchOne('SELECT * FROM users WHERE id = :id', ['id' => 1]);
var_dump($user);

// 列表
$users = $db->fetchAll('SELECT * FROM users WHERE status = :status ORDER BY created_at DESC', ['status' => 1]);
foreach ($users as $u) {
    echo $u['username'] . PHP_EOL;
}

// 新增
$affected = $db->execute(
    'INSERT INTO users (username, email, created_at) VALUES (:u, :e, NOW())',
    ['u' => 'newuser', 'e' => 'new@example.com']
);
echo "新增 $affected 筆，ID: " . $db->lastInsertId('users');

// 更新
$db->execute('UPDATE users SET last_login = NOW() WHERE id = :id', ['id' => 1]);

// 刪除
$db->execute('DELETE FROM users WHERE id = :id', ['id' => 99]);

注意事項

• Access 不支援併發寫入，多進程環境需加檔案鎖（flock()）
• .accdb (Access 2007+) 驅動名稱為 Microsoft Access Driver (*.mdb, *.accdb)，.mdb 可能需 Microsoft Access Driver (*.mdb)
• 大量資料操作建議遷移至 SQLite/MySQL
• Linux 下 ODBC 配置較繁瑣，建議使用 mdbtools 的 mdb-export 導出 CSV 再導入