vba 更新 MS - 通过 MS-Excel 单元格访问字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2764560/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating MS - Access fields through MS-Excel cells
提问by SpikETidE
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).
考虑到我有一个 Excel 工作簿和一个 Access 表，它们不一定具有相似的结构（即它们的列数可能不同）。
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).
当我打开工作簿时，Excel 工作表中的行被 Access 表中的行填充（从 Access 表复制到使用宏指定的 Excel 工作表的特定单元格范围）。
Then I modify certain cells in the Excel sheet.
然后我修改 Excel 工作表中的某些单元格。
I also have a button called "Save" in the Excel sheet. When pressed, this will execute a macro.
我在 Excel 工作表中还有一个名为“保存”的按钮。当按下时，这将执行一个宏。
My question: how can I update the Access table to reflect the changes in the Excel sheet when the "Save" button is clicked?
我的问题：当单击“保存”按钮时，如何更新 Access 表以反映 Excel 工作表中的更改？
回答by Fionnuala
You can use ADO and some code.
您可以使用 ADO 和一些代码。
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:
您可以像这样使用 ADO 更新数据：
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
