Question

谁能给我一个EXCELVBA往SQL数据库读取和写

Answer 1

rptNo = Cells(10, "E") & Format(Cells(10, "F").Value, "0000") & Format(Cells(10, "G").Value, "0000")
conn.ConnectionString = "Driver={sql server};server=SVCTAG-JH5JL2X;uid=lims;pwd=;database=lims"
conn.ConnectionTimeout = 100
conn.Open
Sql = "select * from [M_CONSIGN] where ConsignNo='" & rptNo & "'"
rs.Open Sql, conn, adOpenKeyset, adLockOptimistic, adCmdText

If rs.EOF Then
MsgBox "未找到“" & rptNo & "”的号子！请核对。", 64, "提示"
conn.Close
Set rs = Nothing
Set conn = Nothing
Exit Sub
Else
rs.Fields("BookDate") = Cells(10, "J").Value
ys = rs.Fields("PayBefore")
If IsNull(ys) Then
rs.Fields("PayBefore") = Cells(25, "G")
Else
a = WorksheetFunction.Sum(yy, ys)
rs.Fields("PayBefore") = Val(ys) + yy
End If
If Cells(22, "E") - rs.Fields("PayBefore") <= 0 Then
rs.Fields("feiyongzhifu") = "已付清"
ElseIf IsNull(rs.Fields("PayBefore")) = True Or rs.Fields("PayBefore") = 0 Then
rs.Fields("feiyongzhifu") = "未付"
Else
rs.Fields("feiyongzhifu") = "未付清"
End If
End If
rs.UpdateBatch