vba 通过excel VBA更新SQL数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12680931/
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
Update SQL database through excel VBA
提问by rmon2852
First off I understand the process of having a user update a db via excel runs alot of risks, I appreciate any advise against this method but lets speculate that I have the perfect user who never makes mistakes :)
首先，我明白让用户通过 excel 更新数据库的过程会带来很多风险，我感谢任何反对这种方法的建议，但让我们推测我有一个完美的用户，从不犯错:)
So firstly I've managed to access a stored procedure via vba in excel to pull data based on a parameter in a particular cell. Now if I wanted to update these fields how would I go about doing this?
因此，首先我设法通过 excel 中的 vba 访问存储过程，以根据特定单元格中的参数提取数据。现在，如果我想更新这些字段，我将如何进行？
My first suggestion would be to create a stored procedure that updates based on all the fields in spreadsheet. If this is the right avenue to follow how would I loop through all the rows?
我的第一个建议是创建一个存储过程，该过程基于电子表格中的所有字段进行更新。如果这是遵循的正确途径，我将如何遍历所有行？
Any other suggestions people could offer would be greatly appreciated.
人们可以提供的任何其他建议将不胜感激。
回答by Fionnuala
Some notes on updating SQL Server. This [ODBC;FileDSN=z:\docs\test.dsn]can be any valid connection string.
关于更新 SQL Server 的一些注意事项。这[ODBC;FileDSN=z:\docs\test.dsn]可以是任何有效的连接字符串。
Dim cn As Object, rs As Object
Dim scn As String, sSQL As String, sFile As String
sFile = ActiveWorkbook.FullName
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open scn
sSQL = "SELECT * INTO [ODBC;FileDSN=z:\docs\test.dsn].FromXL " _
& "FROM [Sheet8$]"
cn.Execute sSQL, recs
Debug.Print recs
sSQL = "INSERT INTO [ODBC;FileDSN=z:\docs\test.dsn].FromXL " _
& "SELECT * FROM [Sheet8$]"
cn.Execute sSQL, recs
Debug.Print recs
sSQL = "UPDATE [ODBC;FileDSN=z:\docs\test.dsn].FromXL x " _
& "INNER JOIN [Sheet8$] w " _
& "ON x.ID = w.ID SET x.Field =w.field"
cn.Execute sSQL, recs
Debug.Print recs
rs.Open "SELECT * FROM [ODBC;FileDSN=z:\docs\test.dsn].FromXL", cn
Debug.Print rs.GetString
回答by jTC
You have to familiarize yourself with Microsofts COM Object. My VBA is rusty so watch out.
您必须熟悉 Microsoft 的 COM 对象。我的 VBA 生锈了，所以要小心。
str t = Range("A1:A4").Value
If you are doing a lot of dynamic processing you should make yourself a wrapper method to some of the microsoft stubs.
如果您正在进行大量的动态处理，您应该让自己成为一些 microsoft 存根的包装方法。
function getValue(str location)
return Range(location + ":" + location).Value
end function
I've done large VBA projects before and reading values from the front end is a costly operation. You should set up objects to hold these values so you never have to read the same value more than once.
我以前做过大型 VBA 项目，从前端读取值是一项代价高昂的操作。您应该设置对象来保存这些值，这样您就不必多次读取相同的值。
Lastly, you need lots and lots of validation, from everything between checking the database to make sure the user has the latest version to making sure that the spreadsheet isn't in print view (because this affects the range being able to read).
最后，您需要大量的验证，从检查数据库以确保用户拥有最新版本到确保电子表格不在打印视图中（因为这会影响能够读取的范围）。
Verifying the version is important because you are going to need to release fixes and you need to verify that those fixes are being used.
验证版本很重要，因为您将需要发布修复程序，并且需要验证这些修复程序是否正在被使用。
