Microsoft Jet SQL 進階教學：Access 2000 多使用者環境應用

前言

本文為三部曲最終篇，建立在基礎與中階文章概念之上，專注於多使用者環境中最常見的 SQL 語法與技巧。讀完前兩篇並掌握本文內容，您將具備全方位運用 Jet SQL 的能力。

進階 SQL 定義與多使用者模型

Jet 4.0 引入了許多接近 SQL Server 的功能，支援：

• 資料庫鎖定模式：頁面鎖定 → 列級鎖定（Record-level locking），大幅降低衝突。
• 交易處理：支援 BEGIN TRANSACTION、COMMIT、ROLLBACK，確保 ACID 特性。
• 並發控制：樂觀鎖定（預設）與悲觀鎖定（LOCK TABLE、SELECT ... WITH OWNERACCESS OPTION）。

Jet vs. MSDE (SQL Server Desktop Engine)

選擇建議：單機/小型工作群組用 Jet；需擴展性、企業級安全性或未來升級 SQL Server 則選 MSDE。

SQL 程式碼撰寫慣例

• 關鍵字大寫（SELECT, FROM, WHERE），物件名稱使用 PascalCase。
• 複雜查詢採用 CTE（Common Table Expression）或衍生表提升可讀性。
• 避免 SELECT *，明確列出所需欄位。
• 參數化查詢防止 SQL 注入，提升計畫重用。

進階資料定義語言 (DDL)

檢視表 (VIEWS)

Jet 4.0 支援 CREATE VIEW，可封裝複雜連接、篩選邏輯：

CREATE VIEW vw_OrderSummary AS
SELECT o.OrderID, c.CompanyName, o.OrderDate, 
       SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS TotalAmount
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
GROUP BY o.OrderID, c.CompanyName, o.OrderDate;

預存程序 (PROCEDURES)

Jet 4.0 引入 CREATE PROCEDURE，支援輸入/輸出參數與傳回值：

CREATE PROCEDURE sp_GetOrdersByDate
    @StartDate DATETIME,
    @EndDate DATETIME,
    @TotalCount LONG OUTPUT
AS
BEGIN
    SELECT @TotalCount = COUNT(*) FROM Orders 
    WHERE OrderDate BETWEEN @StartDate AND @EndDate;
    
    SELECT OrderID, CustomerID, OrderDate, ShippedDate
    FROM Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
    ORDER BY OrderDate DESC;
END;

安全性管理

Jet 使用「工作群組資訊檔」(System.mdw) 管理使用者/群組與權限：

• 建立使用者/群組：CREATE USER、CREATE GROUP（需 ADOX 或 DAO 程式碼）。
• 授權：GRANT SELECT, INSERT ON TableName TO UserOrGroup。
• 收回權限：REVOKE DELETE ON TableName FROM UserOrGroup。
• 資料庫密碼：ALTER DATABASE PASSWORD 'newpwd' 'oldpwd'。

最佳實務：採用「最小權限原則」，應用程式連線使用專用帳號，僅授予必要物件權限；避免使用 Admin 帳號執行日常操作。

多使用者效能調校技巧

1. 索引策略：外鍵欄位必建索引；高選擇性欄位建立非叢集索引。
2. 查詢最佳化：使用 SHOWPLAN 分析執行計畫；避免隱含轉換導致索引失效。
3. 交易簡短化：縮短交易範圍，減少鎖定持有時間。
4. 分頁查詢：採用「游標法」或 TOP N ... WHERE PK > @LastPK 避免大量資料排序。

結語

掌握進階 Jet SQL，能讓 Access 應用在多使用者環境下穩定、高效運作。若專案規模持續成長，建議規劃遷移至 SQL Server/MSDE，以獲得更完整的企業級功能與擴展性。

其他資源

• 下載完整範例檔：AcAdvSQL.exe
• Microsoft Jet 4.0 程式設計參考 (MSDN)