vb连接access excel步骤

第一步

新建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