與各數(shù)據(jù)庫的幾種連接方式

1、、用DAO控件連接數(shù)據(jù)庫1.與Access2000數(shù)據(jù)庫連接Private Sub Command1_Click()'也可直接在控件屬性中設(shè)置以下各項(xiàng)但在控件屬性中不能寫入密碼'只有在數(shù)據(jù)數(shù)沒有密碼的情況下可以省略Data1.Refresh Data1.Connect = "Access 2000;"Data1.DatabaseName = App. Path + "chncmadb.mdb"'數(shù)據(jù)庫沒有密碼此句可省Data1.Connect = "pwd=123456"'Data1.RecordSou

2、rce = "耕地資源管理單元屬性數(shù)據(jù)表2004"Data1.RecordSource = "select * from 耕地資源管理單元屬性數(shù)據(jù)表2004"Data1.Refreshmove后才能正確顯示記錄個(gè)數(shù)End Sub2.與沒有密碼的DBF文件數(shù)據(jù)庫連接Private Sub Command2_Click()Data1.Connect = "dBASE III;"Data1.DatabaseName = App. Path' Data1.RecordSource ="DBF" Data1.Reco

3、rdSource = "select * from dbf"Data1.Refreshmove后才能正確顯示記錄個(gè)數(shù)End Sub3.與沒有密碼的Excel文件數(shù)據(jù)庫連接Private Sub Command3_Click()Data1.Connect = "Excel 8.0;"Data1.DatabaseName = App.Path & "EXcel.xls"Data1.RecordSource = "select * from EXcel.xls"Data1.Refreshmove后才能正確顯示記錄

4、個(gè)End Sub二、用DAO代碼連接數(shù)據(jù)庫'在使用DAO對(duì)象前應(yīng)選定Visual Basic菜單下的工程中的引用了菜單中的Microsoft DAO 3.6 Object Library選項(xiàng)，或其它版本1.DAO代碼與Access數(shù)據(jù)庫連接Private Sub Command1_Click()Dim Db As DatabaseDim Rs As Recordset'以共享、讀寫方式打開'如果無密碼最后一個(gè)參數(shù)可以不要SetDb= OpenDatabase(App.Path & "chncmadb.mdb", False, False, &

5、quot;pwd=123456") '不需要move來更新記錄個(gè)數(shù) 'Set Rs = Db.OpenRecordset("耕地資源管理單元屬性數(shù)據(jù)表2004") '需要move來更新記錄個(gè)數(shù)Set Rs = Db.OpenRecordset("select * from 耕地資源管理單元屬性數(shù)據(jù)表2004") If Rs.RecordCount > 0 Then Rs.MoveLast Rs.MoveFirstEnd IfEnd Sub2.DAO代碼與沒有密碼的DBF文件數(shù)據(jù)庫連接Private Sub Comm

6、and2_Click()Dim Db As DatabaseDim Rs As Recordset'以共享、讀寫方式打開Set Db = OpenDatabase(App.Path, False, False, "dbase III;") '不需要move來更新記錄個(gè)數(shù)'Set Rs = Db.OpenRecordset("DBF") 需要move來更新記錄個(gè)數(shù) Set Rs = Db.OpenRecordset("select * from DBF") If Rs.RecordCount > 0 The

7、n Rs.MoveLast Rs.MoveFirstEnd IfEnd sub3. 'DAO代碼與沒有密碼的Excel文件數(shù)據(jù)庫連接Private Sub Command3_Click()Dim Db As DatabaseDim Rs As Recordset'以共享、讀寫方式打開'如果無密碼最后一個(gè)參數(shù)可以不要Set Db = OpenDatabase(App.Path & "EXcel.xls", False, False, "Excel 8.0;")'不需要move來更新記錄個(gè)數(shù) ' Set Rs

8、= Db.OpenRecordset("EXcel.xls") '表格中的工作目錄sheet '需要move來更新記錄個(gè)數(shù)Set Rs = Db.OpenRecordset("select * from EXcel.xls") '表格中的工作目錄sheet' If Rs.RecordCount > 0 Then Rs.MoveLast Rs.MoveFirstEnd IfEnd Sub三、用ADO控件連接數(shù)據(jù)庫'也可直接在控件屬性中設(shè)置以下各項(xiàng)1.ADO控件與Access2000數(shù)據(jù)庫連接Private Su

9、b Command1_Click() '連接有密碼的Access數(shù)據(jù)庫 'Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "chncmadb1.mdb;Jet OLEDB:DataBase PASSWORD=123456" '連接沒有密碼的Access數(shù)據(jù)庫 Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Da

10、ta Source=" & App.Path & "chncmadb.mdb;Persist Security Info=False" 'Adodc1.RecordSource = "耕地資源管理單元屬性數(shù)據(jù)表2004" Adodc1.RecordSource = "select * from 耕地資源管理單元屬性數(shù)據(jù)表2004" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub2.'ADO控件與

11、DBF表連接Private Sub Command2_Click() 'Adodc1.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=dBASE Files;DBQ=" & App.Path & "SourceType=DBF;" 'Adodc1.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Prop

12、erties=DSN=Visual FoxPro Tables;UID=;SourceDB=”& app.path &”;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" 'Adodc1.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DSN=dBASE Files;DBQ=”& app.path

13、 &”;DefaultDir=”& app.path &”;DriverId=533;MaxBufferSize=2048;PageTimeout=5;" '能使表名長度不受限制 Adodc1.ConnectionString = "Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceDB=" & App.Path & "SourceType=DBF;Locale Identifier=2052" 'Adodc1.R

14、ecordSource = "DBF1" Adodc1.RecordSource = "select * from DBF1" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub3.'ADO控件與Excel表連接Private Sub Command3_Click() '下面一句測試未能通過 'Adodc1.ConnectionString = "Data Provider=MSDASQL.1;driver=Microsoft

15、 Excel Driver *.xls);DBQ=" & App.Path & "EXcel.xls" 'Adodc1.ConnectionString="Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DSN=Excel Files;DBQ=" & App.Path & "EXcel.xls;DefaultDir=”&app.path &”;DriverId=790;MaxBufferSize

16、=2048;PageTimeout=5;" Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "EXcel.xls;Extended Properties='Excel 8.0;HDR=Yes'" 'Adodc1.RecordSource = "EXcel.xls" Adodc1.RecordSource =

17、 "select * from EXcel.xls" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub4.'ADO控件與Oracle數(shù)據(jù)庫連接Private Sub Command4_Click() 'Adodc1.ConnectionString = "Provider=MSDAORA.1;Password=chncmadb;User ID=chncmadb;Data Source=towebserver;Persist Security Info=

18、True"Adodc1.ConnectionString="Provider=OraOLEDB.Oracle.1;Password=chncmadb;Persist Security Info=True;User ID=chncmadb;Data Source=towebserver" 'Adodc1.RecordSource = "T320481TR012004" '表名不能加方括號(hào) Adodc1.RecordSource = "select * from T320481TR012004" Adodc1.R

19、efresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub5.'ADO控件與SQLserver數(shù)據(jù)庫連接'未測試Private Sub Command5_Click() Adodc1.ConnectionString = "Provider=SQLOLEDB.1;Password=111;Persist Security Info=True;User ID=111;Initial Catalog=111;Data Source=111" 'Adodc1.RecordSource

20、= "T320481TR012004" Adodc1.RecordSource = "select * from T320481TR012004" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub四、用ADO代碼連接數(shù)據(jù)庫'在使用ADO對(duì)象前應(yīng)選定Visual Basic菜單下的工程中的引用了菜單中的Microsoft.ActiveX Data Object 2.5 Library選項(xiàng)，或其它版本1.'ADO代碼與Access2000數(shù)據(jù)庫連

21、接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

22、Source=" & App.Path & "chncmadb1.mdb;Jet OLEDB:DataBase PASSWORD=123456"AdoRs.Open "select * from 耕地資源管理單元屬性數(shù)據(jù)表2004", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub2.'ADO代碼與DBF表

23、連接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 V

24、isual 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 = NothingEnd Sub3.'ADO代碼與Excel表

25、連接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;Persi

26、st 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 = NothingEnd Sub4.'ADO代碼與Oracle數(shù)據(jù)庫連接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 "