Question

EXCEL中用VBA编程连接K3数据库提取即时库存的语言怎么写 ？

谢谢啦！本人初学VBA，有很多的不懂，忘高手不吝指教！
数据库为SQL SERVER 2005

Answer 1

这样写：
如果不会，可以加我msn heroes3player@yahoo.com.cn

Dim cnn As Object
Dim rst As Object
Dim Sql As String

Set cnn = CreateObject("ADODB.connection")
Set rst = CreateObject("ADODB.recordset")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;';Data Source=" & ThisWorkbook.FullName
Sql = "select b1.物料号,b1.物料名称,b1.当前库存 from [库存$] as b1 where b1.物料号='" & Trim(Cells(Target.row, Col_GoodsCode).Value) & "'"
rst.Open Sql, cnn, 1, 1
If rst.RecordCount > 0 Then
If IsNull(rst("当前库存")) Or Trim(rst("当前库存")) = "" Then
Sql = "update [库存$] as b1 set b1.当前库存= " & Target.Value & " where b1.物料号='" & Trim(Cells(Target.row, Col_GoodsCode).Value) & "'"
Else
Sql = "update [库存$] as b1 set b1.当前库存=b1.当前库存 + " & Target.Value & " where b1.物料号='" & Trim(Cells(Target.row, Col_GoodsCode).Value) & "'"
End If
cnn.Execute Sql
End If