PHP PDO 連接 Access 資料庫 (MDB) 封裝類別：讀取、寫入與參數綁定完整實作

適用場景

舊系統遷移、輕量級桌面資料庫、無 MySQL 環境時，使用 PHP PDO 透過 ODBC 驅動程式連接 Access (.mdb/.accdb)。

完整封裝類別

<?php
class DbHelper {
    private PDO $conn;

    public function __construct(string $mdbPath = null) {
        $path = $mdbPath ?? __DIR__ . '/../../App_Data/sd#f#45G_!.mdb';
        $realPath = realpath($path);
        if (!$realPath || !is_file($realPath)) {
            throw new RuntimeException("MDB 檔案不存在: $path");
        }

        $dsn = "odbc:Driver={Microsoft Access Driver (*.mdb)}; Dbq=$realPath;";
        try {
            $this->conn = new PDO($dsn);
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            throw new RuntimeException("Access 連線失敗: " . $e->getMessage());
        }
    }

    /**
     * 讀取查詢 (SELECT)
     * @param string $sql SQL 語句，支援命名參數 :name
     * @param array $params 參數陣列 ['name' => value]
     * @return array 結果集陣列
     */
    public function read(string $sql, array $params = []): array {
        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $val) {
            $stmt->bindValue(":$key", $val);
        }
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * 寫入操作 (INSERT/UPDATE/DELETE)
     * @return bool 執行成功與否
     */
    public function execute(string $sql, array $params = []): bool {
        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $val) {
            $stmt->bindValue(":$key", $val);
        }
        return $stmt->execute();
    }

    /**
     * 取得最後插入 ID (Access 需額外查詢)
     */
    public function lastInsertId(): string {
        $stmt = $this->conn->query("SELECT @@IDENTITY");
        return $stmt->fetchColumn();
    }

    public function beginTransaction(): bool {
        return $this->conn->beginTransaction();
    }

    public function commit(): bool {
        return $this->conn->commit();
    }

    public function rollBack(): bool {
        return $this->conn->rollBack();
    }
}

// 使用範例
$db = new DbHelper();

// 讀取
$users = $db->read("SELECT * FROM Users WHERE Status = :status AND Age > :age", [
    'status' => 1,
    'age' => 18
]);

// 寫入
$ok = $db->execute("INSERT INTO Users (Name, Email, Status) VALUES (:name, :email, 1)", [
    'name' => 'John',
    'email' => 'john@example.com'
]);

// 交易
$db->beginTransaction();
try {
    $db->execute("UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1");
    $db->execute("UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2");
    $db->commit();
} catch (Exception $e) {
    $db->rollBack();
}
?>

關鍵設定說明

系統需求與安裝

• Windows：預安裝 Access Database Engine (ACE) 或 MDAC
• Linux：安裝 unixODBC + mdbtools，DSN 驅動名稱不同
• PHP：啟用 pdo_odbc、odbc 擴展

常見問題

遷移建議

Access 適合單機、低並發、資料量小 (<100MB) 場景。Web 應用建議遷移至 SQLite (檔案型、無伺服器、支援並發 WAL 模式) 或 MySQL/PostgreSQL，PDO 程式碼幾乎無需修改即可切換。