求VB.NET2010连接MDB数据库后，添加、修改和删除等写法示例

引用 Microsoft ActiveX Data Objects 2.8 Library

或者加入microsoft ado data control

定义数据库连接

Dim Cnn As New ADODB.Connection

定义 *** 作记录

Dim Res As New ADODB.Recordset

开始使用

If Res.State = 1 Then Res.Close

Res.CursorLocation = adUseClient

Res.Open "SELECT * From 发票表", Cnn, adOpenDynamic, adLockReadOnly

删除用res.Delete

添加用res.AddNew

res.Fields("序号") = Trim(Res.Fields("序号"))

...

res.Update

更新用Res.Update "序号", 111

列：

'写入产品信息

Sub WriteFP()

'On Error Resume Next

Dim SyBaseRes As New ADODB.Recordset

PB.value = 0

Res.Open "SELECT Count(*) From ICSaleEntry INNER JOIN t_Item ON ICSaleEntry.FItemID = t_Item.FItemID INNER JOIN t_MeasureUnit ON ICSaleEntry.FUnitID = t_MeasureUnit.FMeasureUnitID", SQLServer, adOpenDynamic, adLockReadOnly

Text2.Text = "当前共 " &Val(Res.Fields(0)) &"条数据"

PB.Max = Val(Res.Fields(0))

If Res.State = 1 Then Res.Close

Res.Open "SELECT ICSaleEntry.FDetailID AS 序号,ICSaleEntry.FInterID AS 物料编号, t_Item.FName AS 产品名称,ICSaleEntry.FAuxPrice AS 单价, ICSaleEntry.FAuxQty AS 数量,ICSaleEntry.FAmount AS 原币, ICSaleEntry.FStdAmount AS 本币,t_MeasureUnit.FName AS 单位 FROM ICSaleEntry INNER JOIN t_Item ON ICSaleEntry.FItemID = t_Item.FItemID INNER JOIN t_MeasureUnit ON ICSaleEntry.FUnitID = t_MeasureUnit.FMeasureUnitID", SQLServer, adOpenDynamic, adLockReadOnly

Do While Not Res.EOF

If SyBaseRes.State = 1 Then SyBaseRes.Close

SyBaseRes.Open "select * from 物料表 where 序号='" &Res.Fields("序号") &"'", Cnn, adOpenDynamic, adLockOptimistic

If SyBaseRes.EOF Then

Text2.Text = Text2.Text + vbCrLf + "正在添加: " &Trim(Res.Fields("序号"))

SyBaseRes.AddNew

SyBaseRes.Fields("序号") = Trim(Res.Fields("序号"))

SyBaseRes.Fields("产品编号") = Trim(Res.Fields("物料编号"))

SyBaseRes.Fields("产品名称") = Trim(Res.Fields("产品名称"))

SyBaseRes.Fields("单价") = Trim(Res.Fields("单价"))

SyBaseRes.Fields("数量") = Trim(Res.Fields("数量"))

SyBaseRes.Fields("原币") = Trim(Res.Fields("原币"))

SyBaseRes.Fields("本币") = Trim(Res.Fields("本币"))

SyBaseRes.Fields("单位") = Trim(Res.Fields("单位"))

SyBaseRes.Fields("数据库名") = Trim(Text1.Text)

SyBaseRes.Update

Else

Text2.Text = Text2.Text + vbCrLf + "正在更新: " &Trim(Res.Fields("序号"))

SyBaseRes.Update "序号", Trim(Res.Fields("序号"))

SyBaseRes.Update "产品编号", Trim(Res.Fields("物料编号"))

SyBaseRes.Update "产品名称", Trim(Res.Fields("产品名称"))

SyBaseRes.Update "单价", Trim(Res.Fields("单价"))

SyBaseRes.Update "数量", Trim(Res.Fields("数量"))

SyBaseRes.Update "原币", Trim(Res.Fields("原币"))

SyBaseRes.Update "本币", Trim(Res.Fields("本币"))

SyBaseRes.Update "单位", Trim(Res.Fields("单位"))

SyBaseRes.Update "数据库名", Trim(Text1.Text)

End If

PB.value = PB.value + 1

Res.MoveNext

DoEvents

Loop

If SyBaseRes.State = 1 Then SyBaseRes.Close

Set SyBaseRes = Nothing

End Sub

网络希望不要用ACCESS用ASA（就是sybase那个太强了）

public void CreateMDB()

{

//为了方便测试，数据库名字采用比较随机的名字，以防止添加不成功时还需要重新启动IIS来删除数据库。

string time = DateTime.Now.Year.ToString()

if (DateTime.Now.Month <10)//判断当前月是否小于10

{

time += "0" + DateTime.Now.Month

}

else

{

time += DateTime.Now.Month

}

time += DateTime.Now.Day

string apppath = System.Web.HttpContext.Current.Request.ApplicationPath

string dbName = System.Web.HttpContext.Current.Server.MapPath("\\" + apppath + "\\NewMDB\\CGMDB" + time + ".mdb")

ADOX.CatalogClass cat = new ADOX.CatalogClass()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0Data Source=" + dbName + "")

//ADODB.Connection cn = new ADODB.Connection()

//cn.Open("Provider=Microsoft.Jet.OLEDB.4.0Data Source=" + dbName + "", null, null, -1)

// cat.ActiveConnection = cn

ADOX.TableClass tbl = new ADOX.TableClass()

tbl.ParentCatalog = cat

tbl.Name = "WTMDBTable"

//增加一个文本字段

ADOX.ColumnClass col2 = new ADOX.ColumnClass()

col2.ParentCatalog = cat

col2.Name = "CheckSignNo"

col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false

tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarChar, 50)

//增加一个文本字段

ADOX.ColumnClass col3 = new ADOX.ColumnClass()

col3.ParentCatalog = cat

col3.Name = "CarNo"

col3.Properties["Jet OLEDB:Allow Zero Length"].Value = false

tbl.Columns.Append(col3, ADOX.DataTypeEnum.adVarChar, 50)

//增加一个文本字段

ADOX.ColumnClass col4 = new ADOX.ColumnClass()

col4.ParentCatalog = cat

col4.Name = "CheckDate"

col4.Properties["Jet OLEDB:Allow Zero Length"].Value = false

tbl.Columns.Append(col4, ADOX.DataTypeEnum.adVarChar, 50)

//增加一个文本字段

ADOX.ColumnClass col5 = new ADOX.ColumnClass()

col5.ParentCatalog = cat

col5.Name = "TownClass"

col5.Properties["Jet OLEDB:Allow Zero Length"].Value = false

tbl.Columns.Append(col5, ADOX.DataTypeEnum.adVarChar, 50)

//增加一个文本字段

ADOX.ColumnClass col6 = new ADOX.ColumnClass()

col6.ParentCatalog = cat

col6.Name = "Address"

col6.Properties["Jet OLEDB:Allow Zero Length"].Value = false

tbl.Columns.Append(col6, ADOX.DataTypeEnum.adVarChar, 200)

//增加一个文本字段

ADOX.ColumnClass col7 = new ADOX.ColumnClass()

col7.ParentCatalog = cat

col7.Name = "Action"

col7.Properties["Jet OLEDB:Allow Zero Length"].Value = false

tbl.Columns.Append(col7, ADOX.DataTypeEnum.adVarChar, 50)

//增加一个文本字段

ADOX.ColumnClass col8 = new ADOX.ColumnClass()

col8.ParentCatalog = cat

col8.Name = "CarType"

col8.Properties["Jet OLEDB:Allow Zero Length"].Value = false

tbl.Columns.Append(col8, ADOX.DataTypeEnum.adVarChar, 50)

//增加一个文本字段

ADOX.ColumnClass col9 = new ADOX.ColumnClass()

col9.ParentCatalog = cat

col9.Name = "Mark"

col9.Properties["Jet OLEDB:Allow Zero Length"].Value = false

tbl.Columns.Append(col9, ADOX.DataTypeEnum.adVarChar, 50)

//设置主键

tbl.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "CheckSignNo", "", "")

cat.Tables.Append(tbl)

System.Runtime.InteropServices.Marshal.ReleaseComObject(tbl)

System.Runtime.InteropServices.Marshal.ReleaseComObject(cat)

tbl = null

cat = null

GC.WaitForPendingFinalizers()

GC.Collect()

}

以上代码可以完成创建一个MDB数据库了

新建表：首先打开或新建一个数据库，在该数据库上使用CDaoTableDef的构赵函数定义一个对象，在调用Create函数建新表，之后调用Append函数加入数据库：

void ChangeUserPassword( )

{   

   CString strSystemDB = 

     _T( "路径" )

   SetSystemDB( strSystemDB )

   // User name and password manually added

   // by using Microsoft Access

   CString strUserName = _T( "NewUser" )

   CString strOldPassword = _T( "Password" )

   CString strNewPassword = _T( "NewPassword" )

  SetDefaultUser( strUserName, strOldPassword )

  ChangePassword( strUserName, strOldPassword, 

                   strNewPassword )

   }

//设置默认用户和密码

void SetDefaultUser(CString & strUserName, CString & strPassword)

{

  COleVariant varUserName( strUserName, VT_BSTRT )

  COleVariant varPassword( strPassword, VT_BSTRT )

  DAODBEngine* pDBEngine = AfxDaoGetEngine( )

  ASSERT( pDBEngine != NULL )

  // 设置默认用户

  DAO_CHECK( pDBEngine->put_DefaultUser( varUserName.bstrVal ) )

  // 设置默认密码

  DAO_CHECK( pDBEngine->put_DefaultPassword( varPassword.bstrVal ) )

}

///更改密码

void SetDBPassword( LPCTSTR pDB, LPCTSTR pszOldPassword, LPCTSTR pszNewPassword )

{

   CDaoDatabase db

   CString strConnect( _T( "pwd=" ) )

   // 数据库必须打开

   // 设置密码

   db.Open( pDB, TRUE, FALSE, 

            strConnect + pszOldPassword )

   COleVariant NewPassword( pszNewPassword, VT_BSTRT ),

               OldPassword( pszOldPassword, VT_BSTRT )

   DAO_CHECK( db.m_pDAODatabase->NewPassword( V_BSTR( &OldPassword ),

              V_BSTR( &NewPassword ) ) )

   db.Close()

}

---