vb连接access excel步骤 excel 导入access

概述第一步 新建vb工程 新建数据库   第二步 引入数据库环境： ms activiteX data  OBJECTS 2.8 LIBRARY ms excel 11.0 引入显示控件: 常用的有三个 datagrid：可以直接从表格修改数据库数据 但是不能设置每行的颜色  推荐2st flexgrid：可以显示数据库数据 只读的 hflexgrid：支持ado 可以设置每行显示数据的颜色  推荐1

第一步

新建vb工程 新建数据库

第二步

引入数据库环境：

ms activiteXdata OBJECTS 2.8 liBRARY

ms excel 11.0

引入显示控件:

常用的有三个

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

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

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

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

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

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

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

Dim ConStr As StringDim cn As adodb.ConnectionDim rs As adodb.RecordsetDim statestring As StringDim addrs As StringPrivate Sub Form_Load()'On Error GoTo ERRSet 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" '------------------changecn.Open ConStrcn.CursorLocation = adUseClIEnt Select Case cn.State Case adStateClose statestring = "adStateClosed" Case adStateOpen statestring = "adStateOpen" End Select '显示连接的状态 If statestring = "adStateClosed" ThenMsgBox "数据库连接失败！",statestringText1.Text = "连接失败"ElseText1.Text = "连接成功"End If'----------open the table------------------rs.Open "Select * from buy",cn,3,3Set MSHFlexGrID1.DataSource = rsCall SetRowcolor(MSHFlexGrID1)Exit Sub'ERR:'MsgBox "LOAD ERROR","TIPS"End Sub----------------------------------------------------------------------连接excel数据---------------------------------Dim cnn As adodb.ConnectionSet cnn = New adodb.ConnectionDim XLS_file As StringSet 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,3If rs1.RecordCount = 0 Then'*** no new data***MsgBox "没有新数据"Text1.Text = "没有新数据"Else'*** have new data***'*** add new data***Text1.Text = "检测到新数据"Text1.Text = rs1.RecordCountSet MSHFlexGrID2.DataSource = rs1

全部源代碼

'-------define hong------------Dim ConStr As StringDim cn As ADODB.ConnectionDim rs As ADODB.RecordsetDim cn1 As ADODB.ConnectionDim rs1 As ADODB.RecordsetPrivate statestring As StringPrivate Sub Command1_Click()Dim cnn As ADODB.ConnectionSet cnn = New ADODB.ConnectionDim Source As StringCommonDialog1.Filter = "All files (*.*)|*.xls"CommonDialog1.FilterIndex = 2CommonDialog1.ShowOpenText3.Text = CommonDialog1.filename Source = CommonDialog1.filenameWith 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$] ".CloseEnd WithMsgBox "ok"End SubPrivate Sub Command2_Click()On Error GoTo ErrHandlerCommonDialog1.Filter = "All files (*.*)|*.xls"CommonDialog1.FilterIndex = 2CommonDialog1.ShowOpenText3.Text = CommonDialog1.filenameExit SubErrHandler:End SubPrivate 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"'------------------changecn.Open ConStrcn.CursorLocation = adUseClIEnt  Select Case cn.State    Case adStateClose         statestring = "adStateClosed"    Case adStateOpen         statestring = "adStateOpen"  End Select  If statestring = "adStateClosed" ThenMsgBox "CONNECT ERROR!",statestringElseEnd If'----------open the table------------------rs.Open "SELECT * FROM toolsok order by 識別碼 desc",2,3Set DataGrID1.DataSource = rsText4.Text = rs.dDataGrID1.RefreshCall show_excelEnd SubPrivate Function show_excel()Set cn1 = New ADODB.ConnectionSet rs1 = CreateObject("ADODB.Recordset")Dim XLS_file As StringSet 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$]",3Text2.Text = rs1.RecordCountSet MSHFlexGrID1.DataSource = rs1MSHFlexGrID1.RefreshEnd 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

总结

以上是内存溢出为你收集整理的vb连接access excel步骤 excel 导入access全部内容，希望文章能够帮你解决vb连接access excel步骤 excel 导入access所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。