vb连接access excel步骤

最新推荐文章于 2025-03-17 16:15:19 发布

iteye_8171

最新推荐文章于 2025-03-17 16:15:19 发布

阅读量182

点赞数

文章标签： VB Excel VB.NET Access Microsoft

本文介绍使用VB进行数据库操作的方法，包括创建工程、引入控件及库文件，并通过示例代码展示了如何连接Access和Excel数据库，实现数据的读取与展示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第一步

新建vb工程新建数据库

第二步

引入数据库环境：ms activiteXdata OBJECTS 2.8 LIBRARY

引入显示控件

常用的有三个

datagrid：可以直接从表格修改数据库数据但是不能设置每行的颜色推荐2st

flexgrid：可以显示数据库数据只读的

hflexgrid：支持ado 可以设置每行显示数据的颜色推荐1st

第三步：编写代码将数据库数据筛选显示到表格控件里面

----------------------------------------------------------------------

下面给出我写的vb连接access 和excel的源代码

----------------------------------------------------------------------

Dim ConStr As String Dim cn As adodb.Connection Dim rs As adodb.Recordset Dim statestring As String Dim addrs As String Private Sub Form_Load() 'On Error GoTo ERR Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\tmsdatas.mdb;Persist Security Info=False" 'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "\\172.17.180.60\net\tmsdata.mdb;Persist Security Info=False" '------------------change cn.Open ConStr cn.CursorLocation = adUseClient Select Case cn.State Case adStateClose statestring = "adStateClosed" Case adStateOpen statestring = "adStateOpen" End Select '显示连接的状态 If statestring = "adStateClosed" Then MsgBox "数据库连接失败！", , statestring Text1.Text = "连接失败" Else Text1.Text = "连接成功" End If '----------open the table------------------ rs.Open "Select * from buy", cn, 3, 3 Set MSHFlexGrid1.DataSource = rs Call SetRowColor(MSHFlexGrid1) Exit Sub 'ERR: 'MsgBox "LOAD ERROR", , "TIPS" End Sub ----------------------------------------------------------------------连接excel数据--------------------------------- Dim cnn As adodb.Connection Set cnn = New adodb.Connection Dim XLS_FILE As String Set cn1 = CreateObject("ADODB.Connection") Set rs1 = CreateObject("ADODB.Recordset") cn1.Open "provider=Microsoft.Jet.OLEDB.4.0;" & "data source=" & App.Path & "\buy.xls;" & "Extended Properties=Excel 8.0;" rs1.Open "select STATE from [sheet1$] where STATE=0", cn1, 3, 3 If rs1.RecordCount = 0 Then '*** no new data*** MsgBox "没有新数据" Text1.Text = "没有新数据" Else '*** have new data*** '*** add new data*** Text1.Text = "检测到新数据" Text1.Text = rs1.RecordCount Set MSHFlexGrid2.DataSource = rs1