通过MS-Excel单元更新MS - 访问字段 [英] Updating MS - Access fields through MS-Excel cells

问题描述

考虑到我有一个Excel工作簿和一个Access表不一定具有相似的结构（即它们可能没有相同数量的列）。

当我打开工作簿Excel表中的行由Access表中的行填充（从Access表复制到Excel工作表使用宏指定的特定单元格范围）。

然后我修改Excel表单中的某些单元格。

我还在Excel工作表中有一个名为保存的按钮。当按下时，这将执行一个宏。

我的问题：当点击保存按钮时，如何更新Access表以反映Excel工作表中的更改？

解决方案

您可以使用ADO和一些代码。

有一些笔记。

让我们说你收到一些这样的数据：

  Sub GetMDB（）
 Dim cn As Object 
 Dim rs As Object 
 
 strFile =C：\Docs\DBFrom.mdb
 strCon =Provider = Microsoft.Jet.OLEDB.4.0; Data Source =& strFile& ; 
 
设置cn = CreateObject（ADODB.Connection）
设置rs = CreateObject（ADODB.Recordset）
 
 cn.Open strCon 
 
 strSQL =SELECT * FROM Table1
 rs.Open strSQL，cn 
 
 With Worksheets（7）
 For i = 0 To rs.Fields.Count  - 1 
 .Cells（1，i + 1）= rs.Fields（i）.Name 
下一个
 
 rs.MoveFirst 
 .Cells（2，1） .CopyFromRecordset rs 
结束
 End Sub 
  

您可以使用ADO喜欢这样：

  Sub UpdateMDB（）
 Dim cn As Object 
 Dim rs As Object 
 
''它可能会更好地使用正确的名称，但这是
''方便笔记
 strFile =工作簿（1）.FullName 
 
''注意HDR =是，所以你可以使用集合
''的第一行中的名称来引用列
 strCon =Provider = Microsoft.Jet.OLEDB.4.0;数据源=& strFile _ 
& ;扩展属性=Excel 8.0; HDR =是; IMEX = 1; 
 
设置cn = CreateObject（ADODB.Connection）
设置rs = CreateObject（ADODB.Recordset）
 
 cn.Open strCon 
 
''选择不同的单元
 strSQL =SELECT * FROM [Sheet7 $] s_ 
& INNER JOIN [; Database = c：\Docs\DBFrom.mdb;]。Table1 t_ 
& ON s.id = t.id_ 
& WHERE s.Field1<> t.Field1
 
 rs.Open strSQL，cn，1,3'adOpenKeyset，adLockOptimistic 
 
''只是看到
''如果不是rs.EOF然后MsgBox rs.GetString 
 
''逐个编辑（慢）
 rs.MoveFirst 
尽管不是rs.EOF 
 rs.Fields（t.Field1）= rs.Fields（s.Field1）
 rs.Update 
 rs.MoveNext 
循环
 
 批量更新（更快）
 strSQL =UPDATE [; Database = c：\Docs\DBFrom.mdb;]。Table1 t_ 
& INNER JOIN [Sheet7 $] s_ 
& ON s.id = t.id_ 
& SET t.Field1 = s.Field1_ 
& WHERE s.Field1< t.Field1
 
 cn.Execute strSQL 
 
 End Sub 
  

Consider that I have an Excel workbook and an Access table not necessarily having a similar structure (i.e. they may not have same number of columns).

When I open the workbook the rows in the Excel sheet get populated by the rows in Access table (copied from the Access table into the Excel sheet's particular range of cells specified using macros).

Then I modify certain cells in the Excel sheet.

I also have a button called "Save" in the Excel sheet. When pressed, this will execute a macro.

My question: how can I update the Access table to reflect the changes in the Excel sheet when the "Save" button is clicked?

解决方案

You can use ADO and some code.

Here are some notes.

Let us say you get some data like so:

Sub GetMDB()
Dim cn As Object
Dim rs As Object

strFile = "C:\Docs\DBFrom.mdb"
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * FROM Table1"
rs.Open strSQL, cn

With Worksheets(7)
    For i = 0 To rs.Fields.Count - 1
        .Cells(1, i + 1) = rs.Fields(i).Name
    Next

    rs.MoveFirst
    .Cells(2, 1).CopyFromRecordset rs
End With
End Sub

You could update the data using ADO like so:

Sub UpdateMDB()
Dim cn As Object
Dim rs As Object

''It wuld probably be better to use the proper name, but this is
''convenient for notes
strFile = Workbooks(1).FullName

''Note HDR=Yes, so you can use the names in the first row of the set
''to refer to columns
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

''Selecting the cell that are different
strSQL = "SELECT * FROM [Sheet7$] s " _
    & "INNER JOIN [;Database=c:\Docs\DBFrom.mdb;].Table1 t " _
    & "ON s.id=t.id " _
    & "WHERE s.Field1<>t.Field1"

rs.Open strSQL, cn, 1, 3 ''adOpenKeyset, adLockOptimistic

''Just to see
''If Not rs.EOF Then MsgBox rs.GetString

''Editing one by one (slow)
rs.MoveFirst
Do While Not rs.EOF
    rs.Fields("t.Field1") = rs.Fields("s.Field1")
    rs.Update
    rs.MoveNext
Loop

''Batch update (faster)
strSQL = "UPDATE [;Database=c:\Docs\DBFrom.mdb;].Table1 t " _
    & "INNER JOIN [Sheet7$] s " _
    & "ON s.id=t.id " _
    & "SET t.Field1=s.Field1 " _
    & "WHERE s.Field1<>t.Field1 "

cn.Execute strSQL

End Sub

这篇关于通过MS-Excel单元更新MS - 访问字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！