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

－、用DAO控件连接数据库1与Access2000数据库连接Private Sub Command1_Click()'也可直接在控件属性中设置以下各项但在控件属性中不能写入密码'只有在数据数没有密码的情况下可以省略Data1RefreshData1Connect = "Access 2000;"Data1DatabaseName = App Path + "/chncmadbmdb"'数据库没有密码此句可省Data1Connect = ";pwd=123456"'Data1RecordSource = "耕地资源管理单元属性数据表2004" Data1RecordSource = "select from耕地资源管理单元属性数据表2004"Data1Refresh’move后才能正确显示记录个数End Sub2与没有密码的DBF文件数据库连接Private Sub Command2_Click()Data1Connect = "dBASE III;"Data1DatabaseName = App Path' Data1RecordSource ="DBF"Data1RecordSource = "select from dbf"Data1Refresh’move后才能正确显示记录个数End Sub3与没有密码的Excel文件数据库连接Private Sub Command3_Click()Data1Connect = "Excel 80;"Data1DatabaseName = AppPath & "/EXcelxls"Data1RecordSource = "select from [EXcelxls]"Data1Refresh’move后才能正确显示记录个End Sub 二、用DAO代码连接数据库'在使用DAO对象前应选定Visual Basic菜单下的[工程]中的引用了菜单中的[Microsoft DAO 36 Object Library]选项，或其它版本1DAO代码与Access数据库连接Private Sub Command1_Click()Dim Db As DatabaseDim Rs As Recordset'以共享、读写方式打开'如果无密码最后一个参数可以不要Set Db= OpenDatabase(AppPath & "/chncmadbmdb", False, False, ";pwd=123456")'不需要move来更新记录个数 'Set Rs = DbOpenRecordset("耕地资源管理单元属性数据表2004") '需要move来更新记录个数Set Rs = DbOpenRecordset("select from [耕地资源管理单元属性数据表2004]")If RsRecordCount > 0 Then RsMoveLast RsMoveFirstEnd IfEnd Sub 2DAO代码与没有密码的DBF文件数据库连接Private Sub Command2_Click()Dim Db As DatabaseDim Rs As Recordset'以共享、读写方式打开Set Db = OpenDatabase(AppPath, False, False, "dbase III;") '不需要move来更新记录个数'Set Rs = DbOpenRecordset("DBF")’需要move来更新记录个数Set Rs = DbOpenRecordset("select from [DBF]") If RsRecordCount > 0 Then RsMoveLast RsMoveFirstEnd IfEnd sub 3 'DAO代码与没有密码的Excel文件数据库连接Private Sub Command3_Click()Dim Db As DatabaseDim Rs As Recordset'以共享、读写方式打开'如果无密码最后一个参数可以不要Set Db = OpenDatabase(AppPath & "/EXcelxls", False, False, "Excel 80;")'不需要move来更新记录个数 ' Set Rs = DbOpenRecordset("EXcelxls") '表格中的工作目录sheet '需要move来更新记录个数Set Rs = DbOpenRecordset("select from [EXcelxls]") '表格中的工作目录sheet'If RsRecordCount > 0 Then RsMoveLast RsMoveFirstEnd IfEnd Sub 三、用ADO控件连接数据库'也可直接在控件属性中设置以下各项1ADO控件与Access2000数据库连接Private Sub Command1_Click() '连接有密码的Access数据库 'Adodc1ConnectionString = "Provider=MicrosoftJetOLEDB40;Data Source=" & AppPath & "/chncmadb1mdb;Jet OLEDB:DataBase PASSWORD=123456" '连接没有密码的Access数据库 Adodc1ConnectionString = "Provider=MicrosoftJetOLEDB40;Data Source=" & AppPath & "/chncmadbmdb;Persist Security Info=False" 'Adodc1RecordSource = "[耕地资源管理单元属性数据表2004]" Adodc1RecordSource = "select from [耕地资源管理单元属性数据表2004]" Adodc1Refresh Set DataGrid1DataSource = Adodc1 DataGrid1RefreshEnd Sub 2'ADO控件与DBF表连接Private Sub Command2_Click() 'Adodc1ConnectionString = "Provider=MSDASQL1;Persist Security Info=False;Data Source=dBASE Files;DBQ=" & AppPath & ";SourceType=DBF;" 'Adodc1ConnectionString = "Provider=MSDASQL1;Persist Security Info=False;Extended Properties=DSN=Visual FoxPro Tables;UID=;SourceDB=”& apppath &”;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" 'Adodc1ConnectionString = "Provider=MSDASQL1;Persist Security Info=False;Extended Properties=DSN=dBASE Files;DBQ=”& apppath &”;;DefaultDir=”& apppath &”;DriverId=533;MaxBufferSize=2048;PageTimeout=5;" '能使表名长度不受限制 Adodc1ConnectionString = "Provider=MSDASQL1;Driver=Microsoft Visual Foxpro Driver;SourceDB=" & AppPath & ";SourceType=DBF;Locale Identifier=2052" 'Adodc1RecordSource = "[DBF1]" Adodc1RecordSource = "select from DBF1" Adodc1Refresh Set DataGrid1DataSource = Adodc1 DataGrid1RefreshEnd Sub 3'ADO控件与Excel表连接Private Sub Command3_Click() '下面一句测试未能通过 'Adodc1ConnectionString = "Data Provider=MSDASQL1;driver=Microsoft Excel Driver xls);DBQ=" & AppPath & "/EXcelxls" 'Adodc1ConnectionString="Provider=MSDASQL1;Persist Security Info=False;Extended Properties=DSN=Excel Files;DBQ=" & AppPath & "/EXcelxls;DefaultDir=”&apppath &”;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" Adodc1ConnectionString = "Provider=MicrosoftJetOLEDB40;Persist Security Info=False;Data Source=" & AppPath & "/EXcelxls;Extended Properties='Excel 80;HDR=Yes'" 'Adodc1RecordSource = "[EXcelxls]" Adodc1RecordSource = "select from [EXcelxls]" Adodc1Refresh Set DataGrid1DataSource = Adodc1 DataGrid1RefreshEnd Sub 4'ADO控件与Oracle数据库连接Private Sub Command4_Click() 'Adodc1ConnectionString = "Provider=MSDAORA1;Password=chncmadb;User ID=chncmadb;Data Source=towebserver;Persist Security Info=True"Adodc1ConnectionString="Provider=OraOLEDBOracle1;Password=chncmadb;Persist Security Info=True;User ID=chncmadb;Data Source=towebserver" 'Adodc1RecordSource = "T320481TR012004" '表名不能加方括号 Adodc1RecordSource = "select from T320481TR012004" Adodc1Refresh Set DataGrid1DataSource = Adodc1 DataGrid1RefreshEnd Sub 5'ADO控件与SQLserver数据库连接'未测试Private Sub Command5_Click() Adodc1ConnectionString = "Provider=SQLOLEDB1;Password=111;Persist Security Info=True;User ID=111;Initial Catalog=111;Data Source=111" 'Adodc1RecordSource = "T320481TR012004" Adodc1RecordSource = "select from T320481TR012004" Adodc1Refresh Set DataGrid1DataSource = Adodc1 DataGrid1RefreshEnd Sub 四、用ADO代码连接数据库'在使用ADO对象前应选定Visual Basic菜单下的[工程]中的引用了菜单中的[MicrosoftActiveX Data Object 25 Library]选项，或其它版本1'ADO代码与Access2000数据库连接Private Sub Command1_Click() Dim AdoCnn As ADODBConnection Dim AdoRs As ADODBRecordset Set AdoCnn = New ADODBConnection Set AdoRs = New ADODBRecordset AdoCnnCursorLocation = adUseClient 'open后面的字符串可以参考ADO控件连接ConnectionString后面的的字符串 AdoCnnOpen "Provider=MicrosoftJetOLEDB40;Data Source=" & AppPath & "/chncmadb1mdb;Jet OLEDB:DataBase PASSWORD=123456"AdoRsOpen "select from [耕地资源管理单元属性数据表2004]", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub 2'ADO代码与DBF表连接Private Sub Command2_Click() Dim AdoCnn As ADODBConnection Dim AdoRs As ADODBRecordset Set AdoCnn = New ADODBConnection Set AdoRs = New ADODBRecordset AdoCnnCursorLocation = adUseClient 'open后面的字符串可以参考ADO控件连接ConnectionString后面的的字符串 AdoCnnOpen "Provider=MSDASQL1;Driver=Microsoft Visual Foxpro Driver;SourceDB=" & AppPath & ";SourceType=DBF;Locale Identifier=2052"AdoRsOpen "select from [DBF1]", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub3'ADO代码与Excel表连接Private Sub Command3_Click() Dim AdoCnn As ADODBConnection Dim AdoRs As ADODBRecordset Set AdoCnn = New ADODBConnection Set AdoRs = New ADODBRecordset AdoCnnCursorLocation = adUseClient 'open后面的字符串可以参考ADO控件连接ConnectionString后面的的字符串 AdoCnnOpen"Provider=MicrosoftJetOLEDB40;Persist Security Info=False;Data Source=" & AppPath & "/EXcelxls;Extended Properties='Excel 80;HDR=Yes'"AdoRsOpen "select from [EXcelxls]", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub 4'ADO代码与Oracle数据库连接Private Sub Command4_Click() Dim AdoCnn As ADODBConnection Dim AdoRs As ADODBRecordset Set AdoCnn = New ADODBConnection Set AdoRs = New ADODBRecordset AdoCnnCursorLocation = adUseClient 'open后面的字符串可以参考ADO控件连接ConnectionString后面的的字符串 AdoCnnOpen "Provider=OraOLEDBOracle1;Password=chncmadb;Persist Security Info=True;User ID=chncmadb;Data Source=towebserver"AdoRsOpen "select from T320481TR012004", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub 5'ADO代码与SQLserver数据库连接'未测试Private Sub Command5_Click() Dim AdoCnn As ADODBConnection Dim AdoRs As ADODBRecordset Set AdoCnn = New ADODBConnection Set AdoRs = New ADODBRecordset AdoCnnCursorLocation = adUseClient 'open后面的字符串可以参考ADO控件连接ConnectionString后面的的字符串 AdoCnnOpen "Provider=SQLOLEDB1;Password=111;Persist Security Info=True;User ID=111;Initial Catalog=111;Data Source=111"AdoRsOpen "select from T320481TR012004", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub

Set node1 = TreeView1NodesAdd(, , "bm", " 部门")
sql = "select departname,departid from departinfo"
checkopen sql,连接数据库字符串
checkMoveFirst
Do While Not checkEOF
key2 = Trim(checkFields(1))
txt2 = Trim(checkFields(0))
Set node2 = TreeView1NodesAdd(node1Key, tvwChild, "A" & key2, txt2)
If babyState = 1 Then babyClose
sql = "select emploername,employerid from employerinfo where departid like '" & Trim(checkFields(1)) & "%' and ccccode <> '" & checkFields(1) & "'"
babyOpen sql, conn, adOpenStatic, adLockReadOnly
babyMoveFirst
Do While Not babyEOF
key3 = babyFields(1)
text3 = babyFields(0)
Set node3 = TreeView1NodesAdd(node2Key, tvwChild, "B" & key3, text3, 1)
babyMoveNext
Loop
checkMoveNext
Loop
大概意思是这样的，在我的程序里头是正确的，但是你给出的这两个表没有对应关系，找不到连接的字段，你再看看，员工表有没有departid字段，这段代码你可以稍微改动一下就可以了，希望能给你提供帮助

在事件前面加上窗体成员就可以了。
例如：
FORM1代码：
Private Sub command_click()
form2caption="第二个窗体"
end sub

可以啊！
（1）你需要在VB工程中添加一个模块，然后在模块中添加两个公共变量。
Public
cn
As
New
ADODBConnection
'连接
Public
rs
As
New
ADODBRecordset
'记录集
（2）然后在你的主窗口那里连接数据库。
Set
cn
=
New
ADODBConnection
cnOpen
"Provider=MicrosoftJetOLEDB40;Data
Source="
&
AppPath
&
"\datamdb;Jet
OLEDB:Database
Password=123456;Persist
Security
Info=False;"
‘这条语句可以用VB自动生成
（3）之后你就可以其他的窗口做查询、添加、修改等工作了
如果还有什么不明白了，Hi我！

首先新建窗体Form1,Form2
如果是用命令按钮进行调用，那你要画一个COMMAND1（按钮）
然后在Form1代码中输入
Private Sub Command1_Click()
'如果在2号窗体出现后,隐去当前窗体,你还要加上
Form1hide '直接隐去当前窗体1
Form2Show '直接调用窗体2
End Sub
这样就行了,OK

---