Question

谁能给我一个EXCEL VBA往SQL数据库读取和写入记录的例子？

我是初学者，请给个完整的例子，有EXCEL文件最好了！

Answer 1

• 读取和写入操作方式一样，唯一不同的是sql语句，读取用select，读取用insert，以读取为例子，录入如下代码：
  

  'sql完整例子
  Sub testSql()

      '定义连接对象
      Dim cnn As New ADODB.Connection
      Dim rs As New ADODB.Recordset
     
      '定义连接字符串
      Dim conStr As String
      Dim sqlstr As String
      '连接字符串-以下是连接MSSQL数据库
      conStr = "Provider=sqloledb; " _
      & "Server=192.168.1.121; " _
      & "Database=DATABASENAME;Uid=admin;Pwd=admin;"
      cnn.Open conStr
     
      sqlstr = "SELECT * from tablename"
      rs.Open sqlstr, cnn
      Range("a2").CopyFromRecordset rs
      rs.Close
      cnn.Close

　　End Sub

Answer 2

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

Answer 3

我给你一个完整的例子

追问

好的，不过我要到晚上才可以上QQ，公司QQ被封了。