VB与各数据库的几种连接方式.doc

《VB与各数据库的几种连接方式.doc》由会员分享，可在线阅读，更多相关《VB与各数据库的几种连接方式.doc（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、-作者xxxx-日期xxxxVB与各数据库的几种连接方式【精品文档】、用DAO控件连接数据库1.与Access2000数据库连接Private Sub Command1_Click()也可直接在控件属性中设置以下各项但在控件属性中不能写入密码只有在数据数没有密码的情况下可以省略 Data1.Connect = Access 2000;Data1.DatabaseName = App. Path + chncmadb.mdb数据库没有密码此句可省Data1.Connect = ;pwd=123456Data1.RecordSource = 耕地资源管理单元属性数据表2004Data1.Recor

2、dSource = select * from 耕地资源管理单元属性数据表2004move后才能正确显示记录个数End Sub2.与没有密码的DBF文件数据库连接Private Sub Command2_Click()Data1.Connect = dBASE III;Data1.DatabaseName = App. Pathurce =DBF Data1.RecordSource = select * from dbfmove后才能正确显示记录个数End Sub3.与没有密码的Excel文件数据库连接Private Sub Command3_Click()Data1.Connect = E

3、xcel 8.0;Data1.DatabaseName = App.Path & EXcel.xlsData1.RecordSource = select * from EXcel.xlsmove后才能正确显示记录个End Sub二、用DAO代码连接数据库在使用DAO对象前应选定Visual Basic菜单下的工程中的引用了菜单中的Microsoft DAO 3.6 Object Library选项，或其它版本Private Sub Command1_Click()Dim Db As DatabaseDim Rs As Recordset以共享、读写方式打开如果无密码最后一个参数可以不要Set

4、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 End IfEnd SubPrivate Sub Command2_Click()Dim Db As DatabaseDim Rs As Recor

5、dset以共享、读写方式打开Set Db = OpenDatabase(App.Path, False, False, dbase III;) 不需要move来更新记录个数Set Rs = Db.OpenRecordset(DBF) 需要move来更新记录个数 Set Rs = Db.OpenRecordset(select * from DBF) If Rs.RecordCount 0 ThenEnd IfEnd sub3. DAO代码与没有密码的Excel文件数据库连接Private Sub Command3_Click()Dim Db As DatabaseDim Rs As Recor

6、dset以共享、读写方式打开如果无密码最后一个参数可以不要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 ThenEnd IfEnd Sub三、用ADO控件连接数据库也可直接在控件属

7、性中设置以下各项1.ADO控件与Access2000数据库连接Private Sub Command1_Click() 连接有密码的Access数据库 Adodc1.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= & App.Path & chncmadb1.mdb;Jet OLEDB:DataBase PASSWORD=123456 连接没有密码的Access数据库 Adodc1.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= &

8、 App.Path & chncmadb.mdb;Persist Security Info=False Adodc1.RecordSource = 耕地资源管理单元属性数据表2004 Adodc1.RecordSource = select * from 耕地资源管理单元属性数据表2004 Set DataGrid1.DataSource = Adodc1End Sub2.ADO控件与DBF表连接Private Sub Command2_Click() String = Provider=MSDASQL.1;Persist Security Info=False;Data Source=dB

9、ASE Files;DBQ= & App.Path & ;SourceType=DBF; Adodc1.ConnectionString = Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DSN=Visual FoxPro Tables;UID=;SourceDB=”& app.path &”;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes; Adodc1.ConnectionStrin

10、g = Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DSN=dBASE Files;DBQ=”& app.path &”;DefaultDir=”& app.path &”;DriverId=533;MaxBufferSize=2048;PageTimeout=5; 能使表名长度不受限制 Adodc1.ConnectionString = Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceDB= & App.Path & ;Sou

11、rceType=DBF;Locale Identifier=2052 Adodc1.RecordSource = DBF1 Adodc1.RecordSource = select * from DBF1 Set DataGrid1.DataSource = Adodc1End Sub3.ADO控件与Excel表连接Private Sub Command3_Click() 下面一句测试未能通过 ;driver=Microsoft Excel Driver *.xls);DBQ= & App.Path & EXcel.xls Adodc1.ConnectionString=Provider=MS

12、DASQL.1;Persist Security Info=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;Persist Security Info=False;Data Source= & App.Path & EXcel.xls;Exten

13、ded Properties=Excel 8.0;HDR=Yes Adodc1.RecordSource = EXcel.xls cordSource = select * from EXcel.xls Set DataGrid1.DataSource = Adodc1End Sub4.ADO控件与Oracle数据库连接Private Sub Command4_Click() Adodc1.ConnectionString = Provider=MSDAORA.1;Password=chncmadb;User ID=chncmadb;Data Source=towebserver;Persis

14、t Security Info=True=Provider=OraOLEDB.Oracle.1;Password=chncmadb;Persist Security Info=True;User ID=chncmadb;Data Source=towebserver Adodc1.RecordSource = T320481TR012004 表名不能加方括号 Adodc1.RecordSource = select * from T320481TR012004 Set DataGrid1.DataSource = Adodc1End Sub5.ADO控件与SQLserver数据库连接未测试Pr

15、ivate 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 = T320481TR012004 Adodc1.RecordSource = select * from T320481TR012004 Set DataGrid1.DataSource = Adodc1End Sub四、用ADO代码连

16、接数据库在使用ADO对象前应选定Visual Basic菜单下的工程中的引用了菜单中的Microsoft.ActiveX Data Object 2.5 Library选项，或其它版本1.ADO代码与Access2000数据库连接Private Sub Command1_Click() AdoCnn.CursorLocation = adUseClient AdoCnn.Open Provider=Microsoft.Jet.OLEDB.4.0;Data Source= & App.Path & chncmadb1.mdb;Jet OLEDB:DataBase PASSWORD=123456A

17、doRs.Open select * from 耕地资源管理单元属性数据表2004, AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub2.ADO代码与DBF表连接Private Sub Command2_Click() AdoCnn.CursorLocation = adUseClient AdoCnn.Open Provider=MSDASQL.1;Driver=Microsof

18、t Visual Foxpro Driver;SourceDB= & App.Path & ;SourceType=DBF;Locale Identifier=2052AdoRs.Open select * from DBF1, AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub3.ADO代码与Excel表连接Private Sub Command3_Click() AdoCnn.C

19、ursorLocation = adUseClient AdoCnn.OpenProvider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= & App.Path & EXcel.xls;Extended Properties=Excel 8.0;HDR=YesAdoRs.Open select * from EXcel.xls, AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set Ad

20、oRs = Nothing Set AdoCnn = NothingEnd Sub4.ADO代码与Oracle数据库连接Private Sub Command4_Click() AdoCnn.CursorLocation = adUseClient AdoCnn.Open Provider=OraOLEDB.Oracle.1;Password=chncmadb;Persist Security Info=True;User ID=chncmadb;Data Source=towebserverAdoRs.Open select * from T320481TR012004, AdoCnn, a

21、dOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub5.ADO代码与SQLserver数据库连接未测试Private Sub Command5_Click() AdoCnn.CursorLocation = adUseClient AdoCnn.Open Provider=SQLOLEDB.1;Password=111;Persist Security Info=True;User ID=111;Initial Catalog=111;Data Source=111AdoRs.Open select * from T320481TR012004, AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub【精品文档】