access数据库实例(简单实例分享)

access数据库实例

同事要用一个excel文件导出每天邮件的信息，邮件基本信息都存在了access数据表中了，要实现这个功用有好几种方式，例如写一个qty文件，而我直接用了excel自带的VBA功用，写了一个宏，依据输入的日期直接调用，想要哪一天的就导哪一天的！不能白瞎了我这个程序猿的称谓啊，
SubDaoChuExl()
DimconnAsNewADODB.Connection
DimrsAsNewADODB.Recordset
DimrowxhAsInteger,vhxAsInteger,rowzsAsInteger,connStrAsString,sqlStrAsString
OnErrorResumeNext
connStr=”Provider=Microsoft.Jet.OLEDB.4.0;DataSource=”&ThisWorkbook.Path&”\maildb.mdb;”
‘MsgBoxconnStr
DimwsAsWorksheet
Setws=Sheets(“Sheet1″)
conn.OpenconnStr
vhx=1
sqlStr=”SELECTFax_Name,Fax_ModiTimeFROMFax_ReciveInfowhereFax_Update='”&ws.Cells(1,6).Value&”‘;”
‘MsgBoxsqlStr
rs.OpensqlStr,conn,adOpenStatic,adLockBatchOptimistic
ws.Range(“A2:K65535”).ClearContents
DoWhileNotrs.EOF
Withws
.Range(“C2″).CopyFromRecordsetrs,65535
EndWith
rs.MoveNext
Loop
rowzs=rs.RecordCount+1
Forrowxh=2Torowzs
ws.Cells(rowxh,1)=vhx
ws.Cells(rowxh,4).NumberFormatLocal=”yyyy-m-dhh:mm:ss”
vhx=vhx+1
Next
rs.Close
Setrs=Nothing
EndSub

access数据库实例简单实例分享

usingSystem;
usingSystem.Data;
usingSystem.Configuration;
usingSystem.Linq;
usingSystem.Web;
usingSystem.Web.Security;
usingSystem.Web.UI;
usingSystem.Web.UI.HtmlControls;
usingSystem.Web.UI.WebControls;
usingSystem.Web.UI.WebControls.WebParts;
usingSystem.Xml.Linq;
usingSystem.Data.OleDb;
///<summary>
///AccessDB的摘要说明
///</summary>
publicclassAccessDB
{
publicAccessDB()
{
//TODO:在此处添加构造函数逻辑
}
///<summary>
///读取Access数据库
///</summary>
privatevoidReadAccessData()
{
//1、建立连接
stringsAccessConnection=@”Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\DocumentsandSettings\Administrator.WWW-410ADC78208\桌面\应用程序.mdb”;
OleDbConnectionodcConnection=newOleDbConnection(sAccessConnection);
//2、打开连接
odcConnection.Open();
//建立SQL查询
OleDbCommandodCommand=odcConnection.CreateCommand();
//3、输入查询语句
odCommand.CommandText=”SELECTtable.编号,table.[123],table.[456]FROM[table];”;
//建立读取
OleDbDataReaderodrReader=odCommand.ExecuteReader();
//查询并显示数据
while(odrReader.Read())
{
ListViewItemitem=newListViewItem();
item.SubItems.Add(odrReader[0].ToString());
item.SubItems.Add(odrReader[1].ToString());
item.SubItems.Add(odrReader[2].ToString());
this.listView1.Items.Add(item);
}
//关闭连接
odrReader.Close();
odcConnection.Close();
}
///<summary>
///add
///</summary>
///<paramname=”sender”></param>
///<paramname=”e”></param>
privatevoidbutton1_Click(objectsender,EventArgse)
{
//1、建立连接
stringsAccessConnection=@”Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\DocumentsandSettings\Administrator.WWW-410ADC78208\桌面\应用程序.mdb”;
OleDbConnectionodcConnection=newOleDbConnection(sAccessConnection);
//2、打开连接
odcConnection.Open();
//建立SQL查询
OleDbCommandodCommand=odcConnection.CreateCommand();
//3、输入查询语句
odCommand.CommandText=”INSERTINTO[table]([123],[456])VALUES(’88’,’88’)”;
//执行
odCommand.ExecuteNonQuery();
//关闭连接
odcConnection.Close();
this.listView1.Items.Clear();
ReadAccessData();
}
///<summary>
///delete
///</summary>
///<paramname=”sender”></param>
///<paramname=”e”></param>
privatevoidbutton3_Click(objectsender,EventArgse)
{
//1、建立连接
stringsAccessConnection=@”Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\DocumentsandSettings\Administrator.WWW-410ADC78208\桌面\应用程序.mdb”;
OleDbConnectionodcConnection=newOleDbConnection(sAccessConnection);
//2、打开连接
odcConnection.Open();
//建立SQL查询
OleDbCommandodCommand=odcConnection.CreateCommand();
//3、输入查询语句
stringsql=”DELETEtable.编号FROM[table]WHERE(((table.编号)=”+this.listView1.SelectedItems[0].SubItems[1].Text+”))”;
odCommand.CommandText=sql;
//执行
odCommand.ExecuteNonQuery();
//关闭连接
odcConnection.Close();
this.listView1.Items.Clear();
ReadAccessData();
}
///<summary>
///update
///</summary>
///<paramname=”sender”></param>
///<paramname=”e”></param>
privatevoidbutton2_Click(objectsender,EventArgse)
{
//1、建立连接
stringsAccessConnection=@”Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\DocumentsandSettings\Administrator.WWW-410ADC78208\桌面\应用程序.mdb”;
OleDbConnectionodcConnection=newOleDbConnection(sAccessConnection);
//2、打开连接
odcConnection.Open();
//建立SQL查询
OleDbCommandodCommand=odcConnection.CreateCommand();
//3、输入查询语句
stringsql=”UPDATE[table]SET123=’11’,456=’11’WHERE(((table.编号)=”+this.listView1.SelectedItems[0].SubItems[1].Text+”))”;
odCommand.CommandText=sql;
//执行
odCommand.ExecuteNonQuery();
//关闭连接
odcConnection.Close();
this.listView1.Items.Clear();
ReadAccessData();
}
}