各数据库的几种连接方式-will.miao
Access软件网QQ交流学习群(群号码198465573),欢迎您的加入!
首页 >技术文章> Access数据库-模块/函数/VBA


各数据库的几种连接方式

发表时间:2018/9/14 8:40:40 评论(3) 浏览(18244)  评论 | 加入收藏 | 复制
   
摘 要:各数据库的几种连接方式
正 文:

一、用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 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


Access软件网交流QQ群(群号:198465573)
 
 相关文章
【Access示例】判断当前电脑是否已连接到网络  【缪炜  2014/2/23】
【VB.net小品】盲人摸象---解决Oracle数据库连接连接问...  【煮江品茶  2014/5/7】
VBA连接SQL sever 2008及 access 2007以...  【月皓星隐  2016/5/7】
多个连接connection使用ADO写入再读取数据延迟的一种解决...  【老季  2017/12/22】
ODC连接文件  【萤火虫  2018/6/13】
强制断开sql server数据库已有连接的方法   【宏鹏  2018/8/22】
常见问答
技术分类
相关资源
文章搜索
关于作者

will.miao

文章分类

文章存档

友情链接