vb连接access excel步骤 excel 导入access

最新推荐文章于 2025-04-20 16:34:08 发布

物联全栈123

最新推荐文章于 2025-04-20 16:34:08 发布

阅读量7.7k

点赞数 5

分类专栏：数据库技巧玩转VB 文章标签： excel access vb properties datagrid path

本文链接：https://blog.youkuaiyun.com/liang890319/article/details/6326487

版权

玩转VB 同时被 2 个专栏收录

28 篇文章

订阅专栏

数据库技巧

27 篇文章

订阅专栏

本文详细介绍使用VB进行数据库操作的方法，包括连接Access数据库、显示数据、连接Excel并读取数据等步骤。提供了完整的示例代码，适用于初学者快速上手。

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

第一步

新建vb工程新建数据库

第二步

引入数据库环境：

ms activiteX data OBJECTS 2.8 LIBRARY

ms excel 11.0

引入显示控件:

常用的有三个

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

全部源代碼

'-------define hong------------
Dim ConStr As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim cn1 As ADODB.Connection
Dim rs1 As ADODB.Recordset

Private statestring As String

Private Sub Command1_Click()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim Source As String

CommonDialog1.Filter = "All Files (*.*)|*.xls"
CommonDialog1.FilterIndex = 2
CommonDialog1.ShowOpen
Text3.Text = CommonDialog1.FileName
 Source = CommonDialog1.FileName
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & Source & ";Extended Properties=Excel 8.0;"
.Open
.Execute "INSERT INTO [toolsok] IN '" & App.Path & "\toolsdemo.mdb' SELECT * FROM [Sheet1$] "
.Close
End With
MsgBox "ok"
End Sub

Private Sub Command2_Click()
On Error GoTo ErrHandler
CommonDialog1.Filter = "All Files (*.*)|*.xls"
CommonDialog1.FilterIndex = 2
CommonDialog1.ShowOpen
Text3.Text = CommonDialog1.FileName
Exit Sub
ErrHandler:
End Sub

Private Sub Form_Load()
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\toolsdemo.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 "CONNECT ERROR!", , statestring
Else
End If
'----------open the table------------------
rs.Open "SELECT * FROM toolsok order by 識別碼 desc", cn, 2, 3
Set DataGrid1.DataSource = rs
Text4.Text = rs.d
DataGrid1.Refresh
Call show_excel
End Sub

Private Function show_excel()
Set cn1 = New ADODB.Connection
Set rs1 = CreateObject("ADODB.Recordset")

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 & "/excel.xls;" & "Extended Properties=Excel 8.0;"
rs1.Open "select * from [sheet1$]", cn1, 3, 3

Text2.Text = rs1.RecordCount
Set MSHFlexGrid1.DataSource = rs1
MSHFlexGrid1.Refresh
End Function

VB壓縮access2003

'工程引入Microsoft Jet and Replication Objects Library
Private Sub Command1_Click()
Dim path, path1 As String
Dim FIXDB As New JRO.JetEngine
path = App.path & "\base\tmsdata.mdb"
path1 = App.path & "\base\tmsdata_tmp.mdb"
FIXDB.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path, _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path1

Kill path '刪除源文件
FileCopy path1, path '生成壓縮後的源文件
Kill path1 '刪除緩存的文件
MsgBox " OK"
End Sub