Sub connSQL()

Sub connSQL()

Dim t
'获取系统当前时间
t = Year(Now) & "." & Month(Now) & "." & Day(Now) & "-" & Hour(Now) & "." & Minute(Now) & "." & Second(Now)
Dim fileName, sheetname, xlApp, xlWorkbook, xlWorksheet
Dim I, constr, con, rst, iRowCount, sql

constr = "Provider=SQLOLEDB.1;Password=zh-db-srv;Persist Security Info=True;User ID=app;Initial Catalog=CasCenter;Data Source=172.16.129.68"
'如果是wincc在链接ip后面加 \wincc
Set con = CreateObject("ADODB.Connection")
con.ConnectionString = constr
con.Open

If con.State = 0 Then '判断数据库连接是否成功
MsgBox "连接数据库失败"
Else
MsgBox "连接数据库成功"
End If


'查询
Set rst = CreateObject("ADODB.Recordset")
sql = "select userName,password from CasHrUser where username='24226'"
rst.Open sql, con, 1, 3, 1
iRowCount = rst.recordcount '统计查询到的的数据量
MsgBox iRowCount
'写值到excel
For I = 2 To iRowCount + 1

MsgBox rst(0)
MsgBox rst(1)

rst.movenext
Next

rst.Close
Set rst = Nothing

'更新和新增
'sql = "update CasHrUser set description ='VBA OUTLOOK' where username='24226'"
sql = "insert into LogUserSys(remark) values (222)"

MsgBox sql
con.Execute sql

con.Close
Set con = Nothing

MsgBox "end"

End Sub