<%
Dim conn, connStr, dbPath, rs
' 1. 資料庫路徑 (建議放在網站根目錄外的 data 資料夾)
dbPath = Server.MapPath("../data/mydatabase.mdb")
' 2. Jet OLEDB 4.0 連接字串 (標準 MDB)
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
' 如為 ACCDB (Access 2007+),改用:
' connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";Persist Security Info=False;"
' 3. 建立並開啟連接
Set conn = Server.CreateObject("ADODB.Connection")
On Error Resume Next
conn.Open connStr
If Err.Number <> 0 Then
Err.Clear
Set conn = Nothing
Response.Write ""
Response.Write "資料庫連接失敗!
"
Response.Write "請檢查:
"
Response.Write "1. 路徑是否正確: " & dbPath & "
"
Response.Write "2. Jet/ACE Provider 是否安裝
"
Response.Write "3. 資料夾/檔案權限 (IUSR/IIS_IUSRS 需讀寫)
"
Response.Write ""
Response.End
End If
On Error GoTo 0
' 4. 建立 Recordset 物件供後續使用
Set rs = Server.CreateObject("ADODB.Recordset")
' --- 以下為各頁面共用的 conn、rs 物件 ---
%>
| 場景 | 連接字串 |
|---|---|
| 標準 MDB (Jet 4.0) | Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path\db.mdb; |
| ACCDB (ACE 12.0/16.0) | Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path\db.accdb;Persist Security Info=False; |
| 加密 MDB (有密碼) | Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path\db.mdb;Jet OLEDB:Database Password=yourpass; |
| 唯讀模式 | Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path\db.mdb;Mode=Read; |
| 共享模式 (多用戶) | Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path\db.mdb;Mode=Share Deny None; |
' 模式 1: conn.Execute (唯讀、單筆、效能最佳)
Set rs = conn.Execute("SELECT * FROM news WHERE is_pub=1 ORDER BY id DESC")
' 模式 2: rs.Open (需游標操作、分頁、書籤)
rs.Open "SELECT * FROM products", conn, 1, 1 ' adOpenKeyset, adLockReadOnly
' 模式 3: Command 參數化 (寫入、防注入、預編譯)
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM users WHERE username=? AND status=1"
cmd.Parameters.Append cmd.CreateParameter("@u", 200, 1, 50, username)
Set rs = cmd.Execute
' 嚴格順序:後開先關,物件設為 Nothing
If rs.State = 1 Then rs.Close ' adStateOpen = 1
Set rs = Nothing
If conn.State = 1 Then conn.Close
Set conn = Nothing
此範本已包含完整錯誤處理、路徑解析、Provider 選擇指引,可直接作為專案 `conn.asp` 通用引用檔。