ADO代码连接数据库

2011-10-10 14:19

二、用ADO代码连接数据库

'在使用ADO对象前应选定Visual Basic菜单下的[工程]中的引用了菜单中的[Microsoft.ActiveX Data Object 2.5 Library]选项，或其它版本

1.'ADO代码与Access2000数据库连接

Private Sub Command1_Click()

    Dim AdoCnn As ADODB.Connection

    Dim AdoRs As ADODB.Recordset

    Set AdoCnn = New ADODB.Connection

    Set AdoRs = 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:DataBase PASSWORD=123456"

AdoRs.Open "select * from [耕地资源管理单元属性数据表2004]", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText

    Set DataGrid1.DataSource = AdoRs

    Set AdoRs = Nothing

    Set AdoCnn = Nothing

End Sub

 

2.'ADO代码与DBF表连接

Private Sub Command2_Click()

    Dim AdoCnn As ADODB.Connection

    Dim AdoRs As ADODB.Recordset

    Set AdoCnn = New ADODB.Connection

    Set AdoRs = New ADODB.Recordset

    AdoCnn.CursorLocation = adUseClient

    '.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串

    AdoCnn.Open "Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceDB=" & App.Path & ";SourceType=DBF;Locale Identifier=2052"

AdoRs.Open "select * from [DBF1]", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText

    Set DataGrid1.DataSource = AdoRs

    Set AdoRs = Nothing

    Set AdoCnn = Nothing

End Sub

3.'ADO代码与Excel表连接

Private Sub Command3_Click()

    Dim AdoCnn As ADODB.Connection

    Dim AdoRs As ADODB.Recordset

    Set AdoCnn = New ADODB.Connection

    Set AdoRs = New ADODB.Recordset

    AdoCnn.CursorLocation = adUseClient

    '.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串

    AdoCnn.Open"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\EXcel.xls;Extended Properties='Excel 8.0;HDR=Yes'"

AdoRs.Open "select * from [EXcel.xls]", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText

    Set DataGrid1.DataSource = AdoRs

    Set AdoRs = Nothing

    Set AdoCnn = Nothing

End Sub

 

4.'ADO代码与Oracle数据库连接

Private Sub Command4_Click()

    Dim AdoCnn As ADODB.Connection

    Dim AdoRs As ADODB.Recordset

    Set AdoCnn = New ADODB.Connection

    Set AdoRs = New ADODB.Recordset

    AdoCnn.CursorLocation = adUseClient

    '.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串

    AdoCnn.Open "Provider=OraOLEDB.Oracle.1;Password=chncmadb;Persist Security Info=True;User ID=chncmadb;Data Source=towebserver"

AdoRs.Open "select * from T320481TR012004", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText

    Set DataGrid1.DataSource = AdoRs

    Set AdoRs = Nothing

    Set AdoCnn = Nothing

End Sub

 

5.'ADO代码与SQLserver数据库连接

'未测试

Private Sub Command5_Click()

    Dim AdoCnn As ADODB.Connection

    Dim AdoRs As ADODB.Recordset

    Set AdoCnn = New ADODB.Connection

    Set AdoRs = New ADODB.Recordset

    AdoCnn.CursorLocation = adUseClient

    '.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串

    AdoCnn.Open "Provider=SQLOLEDB.1;Password=111;Persist Security Info=True;User ID=111;Initial Catalog=111;Data Source=111"

AdoRs.Open "select * from T320481TR012004", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText

    Set DataGrid1.DataSource = AdoRs

    Set AdoRs = Nothing

    Set AdoCnn = Nothing

End Sub