[ADO.NET] 如何 使用 OLE DB 讀取 Access
1.連線字串參數如下
1-1.檔案位置："Data Source="
1-2.提供者名稱："Provider="
1-3.帳號："User Id="
1-4.密碼："Password="
//連線字串 string cs = "Data Source=" + DataSource + ";" +
"Provider=" + ProviderName + "User Id=" + UserId + "Password=" + Password;
2.若Access檔案沒有安全性，也就是不用密碼即可開啟，參數"User Id=" 與 "Password=" 可以空著
3.建立連線的步驟跟Excel與textFile沒有什麼兩樣，讀寫資料庫就是如此而已，唯一不同的是連線字串，不用花太多時間，前面那篇EXCEL已經夠我瞎忙了，趕快結案往下一篇前進。
連線字串參考
http://www.connectionstrings.com/access
3.完整範例如下：
C#
using System;
using System.Data;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
namespace CS_OLEDB_Access
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
//定義OLE======================================================
//1.檔案位置
private const string FileName = "Northwind.mdb";
//2.提供者名稱
private const string ProviderName = "Microsoft.Jet.OLEDB.4.0;";
//3.帳號
private const string UserId = ";";
//4.密碼
private const string Password = ";";
//=============================================================
private void Form1_Load(object sender, EventArgs e)
{
string DataSource=Directory.GetCurrentDirectory() + "\\" + FileName;
if (!File.Exists(DataSource))
{
MessageBox.Show("檔案不存在");
return;
}
//連線字串
string cs =
"Data Source=" + DataSource + ";" +
"Provider=" + ProviderName +
"User Id=" + UserId +
"Password=" + Password;
using (OleDbConnection cn = new OleDbConnection(cs))
{
string qs = "SELECT * FROM Employees;";
if (cn.State==ConnectionState.Closed)
{
cn.Open();
using (OleDbCommand cmd = new OleDbCommand(qs,cn))
{
using (OleDbDataReader dr = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(dr);
this.dataGridView1.DataSource = dt;
}
}
}
}
}
}
}
VB
Imports System.Data.OleDb
Imports System.IO
Public Class Form1
'定義OLE======================================================
'1.檔案位置
Private Const FileName As String = "Northwind.mdb"
'2.提供者名稱
Private Const ProviderName As String = "Microsoft.Jet.OLEDB.4.0;"
'3.帳號
Private Const UserId As String = ";"
'4.密碼
Private Const Password As String = ";"
'=============================================================
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim DataSource As String = Directory.GetCurrentDirectory() + "\" & FileName
If Not File.Exists(DataSource) Then
MessageBox.Show("檔案不存在")
Return
End If
'連線字串
Dim cs As String = "Data Source=" & DataSource & ";" & "Provider=" & ProviderName & "User Id=" & UserId & "Password=" & Password
Using cn As New OleDbConnection(cs)
Dim qs As String = "SELECT * FROM Employees;"
If cn.State = ConnectionState.Closed Then
cn.Open()
Using cmd As New OleDbCommand(qs, cn)
Using dr As OleDbDataReader = cmd.ExecuteReader()
Dim dt As New DataTable()
dt.Load(dr)
Me.DataGridView1.DataSource = dt
End Using
End Using
End If
End Using
End Sub
End Class
4.範例下載：
VB_OLEDB_Access.rar
若有謬誤，煩請告知，新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET
