爱华网
－、用DAO控件连接数据库
1.与Access2000数据库连接
Private Sub Command1_Click()
'也可直接在控件属性中设置以下各项但在控件属性中不能写入密码
'只有在数据数没有密码的情况下可以省略
Data1.Refresh
Data1.Connect = "Access 2000;"
Data1.DatabaseName = App. Path +"chncmadb.mdb"
'数据库没有密码此句可省
Data1.Connect = ";pwd=123456"
'Data1.RecordSource ="耕地资源管理单元属性数据表2004"
Data1.RecordSource = "select * from耕地资源管理单元属性数据表2004"
Data1.Refresh
’move后才能正确显示记录个数
End Sub
2.与没有密码的DBF文件数据库连接
Private Sub Command2_Click()
Data1.Connect = "dBASE III;"
Data1.DatabaseName = App. Path
' Data1.RecordSource ="DBF"
Data1.RecordSource = "select * fromdbf"
Data1.Refresh
’move后才能正确显示记录个数
End Sub
3.与没有密码的Excel文件数据库连接
Private Sub Command3_Click()
Data1.Connect = "Excel 8.0;"
Data1.DatabaseName = App.Path &"EXcel.xls"
Data1.RecordSource = "select *from [EXcel.xls]"
Data1.Refresh
’move后才能正确显示记录个
End Sub
二、用DAO代码连接数据库
'在使用DAO对象前应选定VisualBasic菜单下的[工程]中的引用了菜单中的[Microsoft DAO 3.6 ObjectLibrary]选项，或其它版本
1.DAO代码与Access数据库连接
Private Sub Command1_Click()
Dim Db As Database
Dim Rs As Recordset
'以共享、读写方式打开'如果无密码最后一个参数可以不要
Set Db= OpenDatabase(App.Path &"chncmadb.mdb", False, False, ";pwd=123456")
'不需要move来更新记录个数
'Set Rs =Db.OpenRecordset("耕地资源管理单元属性数据表2004")
'需要move来更新记录个数
Set Rs = Db.OpenRecordset("select * from[耕地资源管理单元属性数据表2004]")
If Rs.RecordCount > 0 Then
Rs.MoveLast
Rs.MoveFirst
End If
End Sub
2.DAO代码与没有密码的DBF文件数据库连接
Private Sub Command2_Click()
Dim Db As Database
Dim Rs As Recordset
'以共享、读写方式打开
Set Db = OpenDatabase(App.Path, False, False,"dbaseIII;")
'不需要move来更新记录个数
'Set Rs = Db.OpenRecordset("DBF")
’需要move来更新记录个数
Set Rs = Db.OpenRecordset("select * from[DBF]")
If Rs.RecordCount > 0 Then
Rs.MoveLast
Rs.MoveFirst
End If
End sub
3.'DAO代码与没有密码的Excel文件数据库连接
Private Sub Command3_Click()
Dim Db As Database
Dim Rs As Recordset
'以共享、读写方式打开'如果无密码最后一个参数可以不要
Set Db = OpenDatabase(App.Path &"EXcel.xls", False, False, "Excel 8.0;")
'不需要move来更新记录个数
' Set Rs = Db.OpenRecordset("EXcel.xls")'表格中的工作目录sheet
'需要move来更新记录个数
Set Rs = Db.OpenRecordset("select * from[EXcel.xls]") '表格中的工作目录sheet'
If Rs.RecordCount > 0 Then
Rs.MoveLast
Rs.MoveFirst
End If
End Sub
三、用ADO控件连接数据库
'也可直接在控件属性中设置以下各项
1.ADO控件与Access2000数据库连接
Private Sub Command1_Click()
'连接有密码的Access数据库
'Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" & App.Path & "chncmadb1.mdb;Jet OLEDB:DataBasePASSWORD=123456"
'连接没有密码的Access数据库
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" & App.Path & "chncmadb.mdb;Persist SecurityInfo=False"
'Adodc1.RecordSource ="[耕地资源管理单元属性数据表2004]"
Adodc1.RecordSource = "select * from[耕地资源管理单元属性数据表2004]"
Adodc1.Refresh
SetDataGrid1.DataSource = Adodc1
DataGrid1.Refresh
End Sub
2.'ADO控件与DBF表连接
Private Sub Command2_Click()
'Adodc1.ConnectionString = "Provider=MSDASQL.1;Persist SecurityInfo=False;Data Source=dBASE Files;DBQ=" & App.Path &";SourceType=DBF;"
'Adodc1.ConnectionString = "Provider=MSDASQL.1;Persist SecurityInfo=False;Extended Properties=DSN=Visual FoxProTables;UID=;SourceDB=”& app.path&”;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
'Adodc1.ConnectionString = "Provider=MSDASQL.1;Persist SecurityInfo=False;Extended Properties=DSN=dBASE Files;DBQ=”& app.path&”;;DefaultDir=”& app.path&”;DriverId=533;MaxBufferSize=2048;PageTimeout=5;"
'能使表名长度不受限制
Adodc1.ConnectionString = "Provider=MSDASQL.1;Driver=MicrosoftVisual Foxpro Driver;SourceDB=" & App.Path &";SourceType=DBF;Locale Identifier=2052"
'Adodc1.RecordSource = "[DBF1]"
Adodc1.RecordSource = "select * from DBF1"
Adodc1.Refresh
SetDataGrid1.DataSource = Adodc1
DataGrid1.Refresh
End Sub
3.'ADO控件与Excel表连接
Private Sub Command3_Click()
'下面一句测试未能通过
'Adodc1.ConnectionString = "DataProvider=MSDASQL.1;driver=Microsoft Excel Driver *.xls);DBQ=" &App.Path & "EXcel.xls"
'Adodc1.ConnectionString="Provider=MSDASQL.1;Persist SecurityInfo=False;Extended Properties=DSN=Excel Files;DBQ=" & App.Path& "EXcel.xls;DefaultDir=”&app.path&”;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;PersistSecurity Info=False;Data Source=" & App.Path &"EXcel.xls;Extended Properties='Excel 8.0;HDR=Yes'"
'Adodc1.RecordSource = "[EXcel.xls]"
Adodc1.RecordSource = "select * from [EXcel.xls]"
Adodc1.Refresh
SetDataGrid1.DataSource = Adodc1
DataGrid1.Refresh
End Sub
4.'ADO控件与Oracle数据库连接
Private Sub Command4_Click()
'Adodc1.ConnectionString ="Provider=MSDAORA.1;Password=chncmadb;User ID=chncmadb;DataSource=towebserver;Persist Security Info=True"
Adodc1.ConnectionString="Provider=OraOLEDB.Oracle.1;Password=chncmadb;PersistSecurity Info=True;User ID=chncmadb;DataSource=towebserver"
'Adodc1.RecordSource = "T320481TR012004"
'表名不能加方括号
Adodc1.RecordSource = "select * from T320481TR012004"
Adodc1.Refresh
SetDataGrid1.DataSource = Adodc1
DataGrid1.Refresh
End Sub
5.'ADO控件与SQLserver数据库连接
'未测试
Private Sub Command5_Click()
Adodc1.ConnectionString = "Provider=SQLOLEDB.1;Password=111;PersistSecurity Info=True;User ID=111;Initial Catalog=111;DataSource=111"
'Adodc1.RecordSource = "T320481TR012004"
Adodc1.RecordSource = "select * from T320481TR012004"
Adodc1.Refresh
SetDataGrid1.DataSource = Adodc1
DataGrid1.Refresh
End Sub
四、用ADO代码连接数据库
'在使用ADO对象前应选定VisualBasic菜单下的[工程]中的引用了菜单中的[Microsoft.ActiveX Data Object2.5 Library]选项，或其它版本
1.'ADO代码与Access2000数据库连接
Private Sub Command1_Click()
DimAdoCnn As ADODB.Connection
DimAdoRs As ADODB.Recordset
SetAdoCnn = New ADODB.Connection
SetAdoRs = New ADODB.Recordset
AdoCnn.CursorLocation = adUseClient
'.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串
AdoCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &App.Path & "chncmadb1.mdb;Jet OLEDB:DataBasePASSWORD=123456"
AdoRs.Open "select * from[耕地资源管理单元属性数据表2004]", AdoCnn, adOpenDynamic,adLockPessimistic, adCmdText
SetDataGrid1.DataSource = AdoRs
SetAdoRs = Nothing
SetAdoCnn = Nothing
End Sub
2.'ADO代码与DBF表连接
Private Sub Command2_Click()
DimAdoCnn As ADODB.Connection
DimAdoRs As ADODB.Recordset
SetAdoCnn = New ADODB.Connection
SetAdoRs = New ADODB.Recordset
AdoCnn.CursorLocation = adUseClient
'.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串
AdoCnn.Open "Provider=MSDASQL.1;Driver=Microsoft Visual FoxproDriver;SourceDB=" & App.Path & ";SourceType=DBF;LocaleIdentifier=2052"
AdoRs.Open "select * from [DBF1]", AdoCnn,adOpenDynamic, adLockPessimistic, adCmdText
SetDataGrid1.DataSource = AdoRs
SetAdoRs = Nothing
SetAdoCnn = Nothing
End Sub
3.'ADO代码与Excel表连接
Private Sub Command3_Click()
DimAdoCnn As ADODB.Connection
DimAdoRs As ADODB.Recordset
SetAdoCnn = New ADODB.Connection
SetAdoRs = New ADODB.Recordset
AdoCnn.CursorLocation = adUseClient
'.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串
AdoCnn.Open"Provider=Microsoft.Jet.OLEDB.4.0;Persist SecurityInfo=False;Data Source=" & App.Path & "EXcel.xls;ExtendedProperties='Excel 8.0;HDR=Yes'"
AdoRs.Open "select * from [EXcel.xls]",AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText
SetDataGrid1.DataSource = AdoRs
SetAdoRs = Nothing
SetAdoCnn = Nothing
End Sub
4.'ADO代码与Oracle数据库连接
Private Sub Command4_Click()
DimAdoCnn As ADODB.Connection
DimAdoRs As ADODB.Recordset
SetAdoCnn = New ADODB.Connection
SetAdoRs = New ADODB.Recordset
AdoCnn.CursorLocation = adUseClient
'.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串
AdoCnn.Open "Provider=OraOLEDB.Oracle.1;Password=chncmadb;PersistSecurity Info=True;User ID=chncmadb;DataSource=towebserver"
AdoRs.Open "select * from T320481TR012004",AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText
SetDataGrid1.DataSource = AdoRs
SetAdoRs = Nothing
SetAdoCnn = Nothing
End Sub
5.'ADO代码与SQLserver数据库连接
'未测试
Private Sub Command5_Click()
DimAdoCnn As ADODB.Connection
DimAdoRs As ADODB.Recordset
SetAdoCnn = New ADODB.Connection
SetAdoRs = New ADODB.Recordset
AdoCnn.CursorLocation = adUseClient
'.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串
AdoCnn.Open "Provider=SQLOLEDB.1;Password=111;Persist SecurityInfo=True;User ID=111;Initial Catalog=111;DataSource=111"
AdoRs.Open "select * from T320481TR012004",AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText
SetDataGrid1.DataSource = AdoRs
SetAdoRs = Nothing
SetAdoCnn = Nothing
End Sub
该文章转载自网络大本营：http://www.qqview.com/Dev/VB/200742412648.Html
