VBA的数据库导入和导出

Sub 导数据库()

Dim conn As New ADODB.Connection '定义数据链接对象 ，保存连接数据库信息；请先添加ADO引用
Dim rs As New ADODB.Recordset  '定义记录集对象，保存数据表
Dim strCn As String, strSQL As String '字符串变量
strCn = "Provider=库名;Server=(IP地址);Database=test;Uid=sa;Pwd=sa;"    '定义数据库链接字符串

conn.Open strCn

i = 2
j = 1
Sheets(j).Select
Do While Cells(i, 1) <> ""
Cells(i, 1).Select
Sql = "insert语句"

conn.Execute Sql

i = i + 1

If i > 65536 Then
i = 2
j = j + 1

If j > Sheets.Count Then
Exit Do
End If

Sheets(j).Select
End If
Loop

conn.Close
set conn=Nothing
End Sub

 

 

Sub 读数据库()

Dim conn As New ADODB.Connection '定义数据链接对象 ，保存连接数据库信息；请先添加ADO引用
Dim rs As New ADODB.Recordset  '定义记录集对象，保存数据表
Dim strCn As String, strSQL As String '字符串变量
strCn = "Provider=库名;Server=(ip地址);Database=test;Uid=sa;Pwd=sa;"    '定义数据库链接字符串

conn.Open strCn

Sql = "查询语句"

rs.ActiveConnection = conn

rs.Open Sql
Range("A55520").CopyFromRecordset rs
rs.Close
conn.Close
set rs=Nothing
set conn=Nothing
End Sub