各数据库的几种连接方式
时 间:2018-09-14 08:40:40
作 者:缪炜 ID:24010 城市:江阴
摘 要:各数据库的几种连接方式
正 文:
一、用DAO代码连接数据库
'在使用DAO对象前应选定Visual Basic菜单下的[工程]中的引用了菜单中的[Microsoft DAO 3.6 Object Library]选项,或其它版本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, "dbase III;") '不需要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;Data Source=" & App.Path & "/chncmadb1.mdb;Jet OLEDB:DataBase PASSWORD=123456" '连接没有密码的Access数据库 Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "/chncmadb.mdb;Persist Security Info=False" 'Adodc1.RecordSource = "[耕地资源管理单元属性数据表2004]" Adodc1.RecordSource = "select * from [耕地资源管理单元属性数据表2004]" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.Refresh End Sub
2.'ADO控件与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 Properties=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 &”;;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.RecordSource = "[DBF1]" Adodc1.RecordSource = "select * from DBF1" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.Refresh End Sub
3.'ADO控件与Excel表连接
Private Sub Command3_Click() '下面一句测试未能通过 'Adodc1.ConnectionString = "Data Provider=MSDASQL.1;driver=Microsoft 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=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 = "select * from [EXcel.xls]" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.Refresh End Sub
4.'ADO控件与Oracle数据库连接
Private Sub Command4_Click() 'Adodc1.ConnectionString = "Provider=MSDAORA.1;Password=chncmadb;User ID=chncmadb;Data Source=towebserver;Persist Security Info=True" Adodc1.ConnectionString="Provider=OraOLEDB.Oracle.1;Password=chncmadb;Persist Security Info=True;User ID=chncmadb;Data Source=towebserver" 'Adodc1.RecordSource = "T320481TR012004" '表名不能加方括号 Adodc1.RecordSource = "select * from T320481TR012004" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.Refresh End Sub
5.'ADO控件与SQLserver数据库连接
'未测试 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 = "T320481TR012004" Adodc1.RecordSource = "select * from T320481TR012004" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.Refresh End Sub
三、用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 Sub4.'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
Access软件网QQ交流群 (群号:54525238) Access源码网店
常见问答:
技术分类:
源码示例
- 【源码QQ群号19834647...(12.17)
- 【Access选项卡示例】Ac...(09.09)
- 【Access源码示例】按输入...(09.02)
- 【Access日期区间段查询】...(08.29)
- 【Access日期区间段查询】...(08.27)
- Access怎样才能实现日期时...(08.21)
- 【Access定时打开查询】A...(08.19)
- Access生成固定数量的记录...(08.13)
- Access怎样才能实现日期时...(08.12)
- Access利用导航窗体控件对...(08.03)
学习心得
最新文章
- Access自定义函数命名为中文名...(09.20)
- Access表中的字段名、字段标题...(09.19)
- Access快速开发平台--更改“...(09.18)
- 【中秋及国庆优惠】Access培训...(09.15)
- Access如何将日期型的数值转换...(09.14)
- 英文输入法输入数据中存在单引号引起...(09.11)
- 【Access选项卡示例】Acce...(09.09)
- 让Access光标停留在指定的控件...(09.07)
- 关于Access查询条件里使用通配...(09.06)
- Access报表偷懒制作法--Ac...(09.05)