谁能给我一个EXCEL <em>VBA</em>往SQL数据库读取和写入记录的...-vba 读

VBE 工具 引用,选中 Microsoft ActiveX Data Object 2.8 Library
我不清楚是不是e79fa5e98193e58685e5aeb9363UFDATA_013_2008表的VENDOR列的数据,如果不是,你自己改一下SQL语句吧,sql = "select VENDOR from UFDATA_013_2008"

Sub SQL数据导入()

Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim str As String
Dim sql As String
Dim i As Integer

str = "Provider=SQLOLEDB;Data Source=SQLSERVER服务器名或IP;DATABASE=数据库;UID=用户名;PWD=密码"
cn.Open str
Set rs = New ADODB.Recordset
sql = "select VENDOR from UFDATA_013_2008"
rs.Open sql, cn, adOpenStatic, adLockOptimistic

If Not rs.EOF Then
For i = 1 To rs.RecordCount
Sheet2.Cells(i, 1).Value = rs.Fields(0).Value
Next i
rs.MoveNext
End If
rs.Close
cn.Close
End Sub

• 读取和写入操作方式一样，唯一不同的是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
      '连接字符串-以下e799bee5baa6e79fa5e98193e78988e69d83334是连接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

Dim conn As New ADODB.Connection
connStr = "Driver={SQL Server};DataBase=test;Server=(local);UID=sa;PWD=123"
conn.Open connStr '连接zhidao数据库回
Dim rs As New Recordset
sql = "select * from a" '查看答表a
rs.Open sql, conn, 3, 3
if Not rs.EOF then
   For iCols = 0 To rs.fields.Count - 1
       Sheets(1).Cells(1, iCols + 1).Value = rs.fields(iCols).Name
   Next iCols
   Sheets(1).Cells(2, 1).CopyFromRecordset rs
endif

'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