Excel VBA SQL 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2567150/
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
Excel VBA SQL Data
提问by user307655
I have a small excel program.
我有一个小的excel程序。
I would like to be able to use this program to update a SQL table.
我希望能够使用该程序来更新 SQL 表。
What would be the function to say update line 2 in SQL table Test in Database ABC
在数据库 ABC 中的 SQL 表测试中更新第 2 行的功能是什么
Thanks
谢谢
回答by PhilPursglove
First of all you need to add a reference to the ActiveX Data Objects library, which contains the set of objects that allow you to do database access - in the Excel Visual Basic editor, go to Tools|References... In the dialog box, scroll down until you find Microsoft ActiveX Data Objects 2.8 Library. Check the box next to the library name.
VBA References dialog with ADO library checked http://philippursglove.com/stackoverflow/adoreference.png
首先，您需要添加对 ActiveX 数据对象库的引用，其中包含允许您访问数据库的对象集 - 在 Excel Visual Basic 编辑器中，转到工具|引用... 在对话框中，向下滚动，直到找到 Microsoft ActiveX Data Objects 2.8 Library。选中库名称旁边的框。
带有 ADO 库的 VBA 引用对话框已检查 http://philippursglove.com/stackoverflow/adoreference.png
Your code to update the database should then look something like this (using the SQL from JohnK813's answer):
您更新数据库的代码应该如下所示（使用来自 JohnK813 的回答中的 SQL）：
'Declare some variables
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
'Create a new Connection object
Set cnn = New ADODB.Connection
'Set the connection string
cnn.ConnectionString = myDatabaseConnectionString 'See http://connectionstrings.com if you need help on building this string for your database!
'Create a new Command object
Set cmd = New ADODB.Command
'Associate the command with the connection
cmd.ActiveConnection = cnn
'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText
'Create the SQL
strSQL = "UPDATE Test SET YourField = NeValue WHERE IDField = 2"
'Pass the SQL to the Command object
cmd.CommandText = strSQL
'Open the Connection to the database
cnn.Open
'Execute the bit of SQL to update the database
cmd.Execute
'Close the connection again
cnn.Close
'Remove the objects
Set cmd = Nothing
Set cnn = Nothing
回答by JohnK813
I see you have other questions open that deal with actually connection to the SQL Server, so I won't add any more to that discussion.
我看到您还有其他问题未解决，涉及与 SQL Server 的实际连接，因此我不会在该讨论中添加更多内容。
Relational database tables don't think of things as being in a certain order, so you can't really say that a certain record is "record 2" or "line 2" just because you added it to the table second. Unless of course you use a field to create an ID number that increments with each new record added.
关系数据库表不认为事物是按特定顺序排列的，因此您不能仅仅因为您第二次将其添加到表中就说某条记录是“记录 2”或“第 2 行”。当然，除非您使用一个字段来创建一个 ID 号，该 ID 号会随着每个新记录的添加而增加。
Then you can access that record by saying
然后你可以通过说访问该记录
UPDATE Test SET YourField=NewValue WHERE IDfield=2
UPDATE Test SET YourField=NewValue WHERE IDfield=2
Here's more information on the UPDATE command, if you need it.
如果需要，这里有关于 UPDATE 命令的更多信息。
