VBA环境下ACCESS、EXCEL、SQL SERVER互相访问
实例7-1 判断SQL Server数据库是否存在(ADO)
引用:microsoft activex data objects 2.x library
Public Sub7_1()
Dim cnn As ADODB.Connection
Dim cnnStr As String
Dim mydata As String
mydata = "商品信息"
‘创建与SQL Server数据库服务器连接的Connection对象
Set cnn = New ADODB.Connection
‘设置建立与SQL Server数据库服务器中指定数据库连接的字符串
cnnStr = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & mydata
cnn.ConnectionString = cnnStr
‘试图打开指定的数据库
On Error Resume Next
cnn.Open
On Error GoTo 0
‘判断数据库是否存在
If cnn.State = adStateOpen Then
MsgBox "数据库<" & mydata & ">存在"
cnn.Close
Set cnn = Nothing
Else
MsgBox "数据库<" & mydata & ">不存在"
End If
End Sub
·分析:
判断在SQL Server数据库服务器上是否存在指定的某个数据库,方法就是度图去打开SQL Server数据库服务器的某个数据库,如能打开则表明该数据库存在,否则就不存在。利用ADODB.Connection对象的State属性即可实现。
State属性用于检查Connection对象是打开还是关闭,或处于连接状态。如返回值为adstateopen,则表明已打开,如返回adstateclosed,则表明关闭,如返回adstateconnecting,表明是在连接。
例:
cnnStr = "Provider=SQLOLEDB.1;" _ ‘SQLOLEDB.1表示数据库类型为SQL Server &”Password=12345;” _ ‘指定打开数据库的密码,可省略
& "User ID=sa;" _ ‘指定访问为数据库的用户名
& "Data Source=THTFCOMPUTER;" _ ‘指定数据库服务器名称或IP地址
& "Initial Catalog=" & mydata ‘指定要访问的数据库名
·实例7-2 检查数据表是否存在(ADOX)
引用:microsoft ado ext.2.x for ddl and security
Public Sub7_2()
Dim myCat As ADOX.Catalog
Dim mtb As ADOX.Table
Dim cnnStr As String
Dim mydata As String, myTable As String
mydata = "商品信息"
myTable = "商品名录"
‘建立与SQL Server数据库服务器指定数据库的连接
cnnStr = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & mydata
Set myCat = New ADOX.Catalog
myCat.ActiveConnection = cnnStr
‘判断数据表是否存在
For Each mtb In myCat.Tables
If LCase(https://www.360docs.net/doc/4c1631275.html,) = LCase(myTable) Then
MsgBox "数据表< " & myTable & "> 存在!"
GoTo hhh
End If
Next
MsgBox "数据表" & myTable & " 不存在!"
hhh:
Set mtb = Nothing
Set myCat = Nothing
End Sub
·实例7-3 创建新的SQL Server数据库和数据表(ADO)
Public Sub7_3()
Dim cnn As ADODB.Connection
Dim cnnStr As String, SQL As String
Dim mydata As String, myTable As String
mydata = "商品信息"
myTable = "商品名录"
‘创建与SQL Server数据库服务器中指定数据库连接的connection对象
Set cnn = New ADODB.Connection
cnnStr = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & mydata
cnn.ConnectionString = cnnStr
On Error Resume Next
cnn.Open
On Error GoTo 0
‘判断数据库是否存在
If cnn.State = adStateOpen Then
MsgBox "数据库已经存在,请重新命名数据库名!", vbCritical
cnn.Close
Set cnn = Nothing
Exit Sub
End If
‘建立与SQL Server数据库服务器的连接
cnnStr = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER"
cnn.ConnectionString = cnnStr
cnn.Open
‘执行SQL语句创建数据库
SQL = "create database " & mydata
cnn.Execute SQL
MsgBox "数据库创建成功!", vbInformation, "创建数据库"
‘关闭与SQL Server数据库服务器的连接
cnn.Close
‘重新建立与SQL Server数据库服务器的连接
cnnStr = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & mydata
cnn.ConnectionString = cnnStr
cnn.Open
‘执行SQL语句创建数据表
SQL = "create table " & myTable _
& "(商品编号varchar(10) not null primary key," _
& "商品名称varchar(20) not null)"
cnn.Execute SQL
MsgBox "数据表创建成功!", vbInformation, "创建数据表"
cnn.Close
Set cnn = Nothing
End Sub
·分析:
SQL=”create table 表名(字段字段类型[(字段长度)][是否允许空值][是否为主键]”
‘创建数据表
·实例7-4 在已有的SQL Servre数据库中创建数据表(ADO)
引用:microsoft active data objects 2.x library 和microsoft ado ext.2.x for ddl and security Public Sub 实例7_4()
Dim cnn As ADODB.Connection
Dim myCat As ADOX.Catalog
Dim mtb As ADOX.Table
Dim cnnStr As String, SQL As String
Dim mydata As String, myTable As String
mydata = "商品信息"
myTable = "商品价格信息"
‘建立与SQL Server数据库服务器中指定数据库的连接
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & mydata
.Open
‘判断在数据库中是否有同名的数据表
Set myCat = New ADOX.Catalog
Set myCat.ActiveConnection = cnn
For Each mtb In myCat.Tables
If LCase(https://www.360docs.net/doc/4c1631275.html,) = LCase(myTable) Then
MsgBox "数据表<" & myTable & ">已经存在!请重新命名数据表!", vbCritical
GoTo hhh
End If
Next
‘执行SQL语句创建数据表
SQL = "create table " & myTable _
& "(商品编号varchar(10) not null primary key," _
& "商品单价decimal not null)"
cnn.Execute SQL
MsgBox "数据表创建成功!", vbInformation, "创建数据表"
hhh:
cnn.Close
Set myCat = Nothing
Set cnn = Nothing
End Sub
·实例7-5 从SQL Server数据库服务器中删除数据库(ADO)
引用:microsoft activex data objects 2.x library
Public Sub7_5()
On Error GoTo hhh
Dim cnn As ADODB.Connection
Dim cnnStr As String, SQL As String
Dim mydata As String, myTable As String
‘指定要删除的数据库名称
mydata = "商品信息"
‘建立与SQL Server数据库服务器的连接
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER"
.Open
‘执行SQL语句删除指定的数据库
SQL = "drop database " & mydata
cnn.Execute SQL
MsgBox "数据库删除成功!", vbInformation, "删除数据库"
GoTo xxx
hhh: MsgBox Err.Description, vbCritical
xxx: cnn.Close
Set cnn = Nothing
End Sub
·分析:
SQL=”drop database 数据库名1,数据库名2,……”
‘删除指定的数据库
·实例7-6 从SQL Server数据库中删除数据表(ADO)
引用:microsoft activex data objects 2.x library
Public Sub7_6()
On Error GoTo hhh
Dim cnn As ADODB.Connection
Dim cnnStr As String, SQL As String
Dim mydata As String, myTable As String
mydata = "商品信息"
myTable = "商品名录,商品价格信息"
‘建立与SQL Server数据库的连接
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & mydata
.Open
End With
‘执行SQL语句删除指定的数据表
SQL = "drop table " & myTable
cnn.Execute SQL
MsgBox "数据表删除成功!", vbInformation, "删除数据表"
GoTo xxx
hhh: MsgBox Err.Description, vbCritical
xxx: cnn.Close
Set cnn = Nothing
End Sub
·实例7-7 将SQL Server数据库中的数据导入到Excel工作表(ADO)
引用:microsoft activex data objects 2.x library
Public Sub7_7()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Long
Dim mydata As String, myTable As String
mydata = "工资管理"
myTable = "基本信息"
‘建立与SQL Server数据库的连接
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & mydata
.Open
End With
‘打开查询数据集
Set rs = New ADODB.Recordset
rs.Open myTable, cnn, adOpenKeyset, adLockOptimistic
‘清除工作表数据
Cells.Clear
‘准备复制数据
With rs
‘复制字段名
For i = 1 To .Fields.Count
Cells(1, i).Value = .Fields(i - 1).Name
Next
‘复制记录
Range("A2").CopyFromRecordset rs
End With
‘关闭数据集和数据库连接,并释放变量
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
·实例7-8 将SQL Server数据库中的数据导入到Excel工作表(DAO)
引用:microsoft dao 3.6 object library
Public Sub7_8()
Dim myWsp As DAO.Workspace
Dim cnn As DAO.Connection
Dim rs As DAO.Recordset
Dim cnnStr As String
Dim mydata As String, myTable As String
Dim i As Long
mydata = "工资管理"
myTable = "基本信息"
‘设置连接字符串
cnnStr = "ODBC;" _
& "Driver=SQL Server;" _
& "SERVER=THTFCOMPUTER;UID=sa;" _
& "PWD=;DATABASE=" & mydata & ";FILEDSN=C:"
‘建立与数据库的连接
Set myWsp = CreateWorkspace("myWsp", "myName", "", dbUseODBC)
Workspaces.Append myWsp
Set cnn = myWsp.OpenConnection( _
Name:="cnnnection", _
Options:=dbDriverNoPrompt, _
Connect:=cnnStr)
‘建立查询数据集
Set rs = cnn.OpenRecordset(myTable)
‘清除工作表数据
Cells.Clear
‘准备复制数据
With rs
‘复制字段名
For i = 1 To .Fields.Count
Cells(1, i).Value = .Fields(i - 1).Name
Next
‘复制记录
Range("A2").CopyFromRecordset rs
End With
‘关闭数据集和数据库连接,并释放变量
rs.Close
cnn.Close
myWsp.Close
Set rs = Nothing
Set cnn = Nothing
Set myWsp = Nothing
End Sub
·分析:
利用DAO连接SQL Server数据库的连接字符串。一般形式为:
cnnStr = "ODBC;" _
& "Driver=SQL Server;" _
& "SERVER=SQL Server服务器名;UID=sa;" _
& "PWD=;DATABASE=数据库名;FILEDSN=C:"
建立与SQL Server数据库的连接,首先需要利用Createworkspace方法建立一个新的workspace对象:
set workspace对象变量=createworkspace(name,user,password,type)
·name指定该工作区workspace对象的名称,user设置该工作区的用户名,password 是用户密码,type指定类型:有jet型(dbusejet)和odbc型(dbuseodbc)本例:Set myWsp = CreateWorkspace("myWsp", "myName", "", dbUseODBC) 再使用workspace集合的append方法将这个workspace对象添加到workspaces集合中。
workspaces.append mywsp
利用openconnection方法建立与SQL Server数据库的连接
set connection=workspace.openconnection(name,options,readonly,connect)
·connection:表示DAO.connection对象变量
·workspace(可选):指定workspace对象
·name:为字符串表达式,是创建的workspace对象名称
·options(可选):指定连接的名称
·Readonly(可选):表示是否为只读打开数据库。默认为F。表示可读/写数据。
·connect(可选):指定ODBC连接字符串。
例:Set cnn = myWsp.OpenConnection( _
Name:="cnnnection", _
Options:=dbDriverNoPrompt, _
Connect:=cnnStr)
·实例7-9 查询获取SQL Server数据库的数据(ADO)
引用:microsoft activex data objects 2.x library
Public Sub7_9()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Long
Dim mydata As String, myTable As String, SQL As String
mydata = "工资管理"
myTable = "基本信息"
‘建立与SQL Server数据库的连接
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & mydata
.Open
End With
‘打开查询数据集
SQL = "select * from " & myTable & " where 性别='男'"
Set rs = New ADODB.Recordset
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
‘清除工作表数据
Cells.Clear
‘准备复制数据
With rs
‘复制字段名
For i = 1 To .Fields.Count
Cells(1, i).Value = .Fields(i - 1).Name
Next
‘复制记录
Range("A2").CopyFromRecordset rs
End With
‘关闭数据集和数据库连接,并释放变量
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
·实例7-10 查询获取SQL Server数据库的数据(DAO)
引用:microsoft dao 3.6 object library
Public Sub7_10()
Dim myWsp As DAO.Workspace
Dim cnn As DAO.Connection
Dim rs As DAO.Recordset
Dim cnnStr As String
Dim SQL As String
Dim mydata As String, myTable As String
Dim i As Long
mydata = "工资管理"
myTable = "基本信息"
‘设置连接字符串
cnnStr = "ODBC;" _
& "Driver=SQL Server;" _
& "SERVER=THTFCOMPUTER;UID=sa;" _
& "PWD=;DATABASE=" & mydata & ";FILEDSN=C:"
‘建立与数据库的连接
Set myWsp = CreateWorkspace("myWsp", "myName", "", dbUseODBC)
Workspaces.Append myWsp
Set cnn = myWsp.OpenConnection( _
Name:="cnnnection", _
Options:=dbDriverNoPrompt, _
Connect:=cnnStr)
‘建立查询数据集
SQL = "select * from " & myTable & " where 性别='男'"
Set rs = cnn.OpenRecordset(SQL)
‘清除工作表数据
Cells.Clear
‘准备复制数据
With rs
‘复制字段名
For i = 1 To .Fields.Count
Cells(1, i).Value = .Fields(i - 1).Name
Next
‘复制记录
Range("A2").CopyFromRecordset rs
End With
‘关闭数据集和数据库连接,并释放变量
rs.Close
cnn.Close
myWsp.Close
Set rs = Nothing
Set cnn = Nothing
Set myWsp = Nothing
End Sub
·实例7-11 将工作表数据导入到SQL Server数据库(ADO)
引用:microsoft activex data objects 2.x library
Public Sub7_11()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Long
Dim mydata As String, myTable As String, SQL As String
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook ‘指定工作簿
Set ws = wb.Sheets("sheet1") ‘指定工作表名称
mydata = "工资管理" ‘指定数据库
myTable = "基本信息" ‘指定数据表
‘建立与SQL Server数据库的连接
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & mydata
.Open
End With
‘获取要保存的记录数(行数)
n = ws.Range("A65536").End(xlUp).Row
For i = 2 To n
‘查询是否已经存在了某条记录
SQL = "select * from " & myTable _
& " where 职工编号='" & Trim(ws.Cells(i, 1).Value) & "'" _
& " and 姓名='" & Trim(ws.Cells(i, 2).Value) & "'" _
& " and 性别='" & Trim(ws.Cells(i, 3).Value) & "'" _
& " and 所属部门='" & Trim(ws.Cells(i, 4).Value) & "'" _
& " and 工资总额=" & Trim(ws.Cells(i, 5).Value) _
& " and 备注='" & Trim(ws.Cells(i, 6).Value) & "'"
Set rs = New ADODB.Recordset
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
If rs.EOF And rs.BOF Then
‘如要数据表中没有工作表的某行数据,就添加到数据表
rs.AddNew
For j = 1 To rs.Fields.Count
rs.Fields(j - 1) = Trim(ws.Cells(i, j).Value)
Next j
rs.Update
Else
‘如果数据表中有工作表的某行数据,就将数据进行更新
For j = 1 To rs.Fields.Count
rs.Fields(j - 1) = Trim(ws.Cells(i, j).Value)
Next j
rs.Update
End If
Next i
MsgBox "数据保存完毕!", vbInformation, "保存数据"
‘关闭数据库及查询数据集,并释放变量
rs.Close
cnn.Close
Set wb = Nothing
Set ws = Nothing
Set rs = Nothing
Set cnn = Nothing
End Sub
·实例7-12 向SQL Server数据库中添加记录的一般方法
引用:microsoft activex data objects 2.x library
Public Sub7_12()
Dim cnn As ADODB.Connection
Dim cnnStr As String, SQL As String
Dim mydata As String, myTable As String
mydata = "商品信息"
myTable = "商品名录"
‘建立与SQL Server数据库服务器的连接
cnnStr = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & mydata
Set cnn = New ADODB.Connection
cnn.ConnectionString = cnnStr
cnn.Open
‘为数据表添加新记录
SQL = "insert into " & myTable & " values('1000000001','25彩电')"
cnn.Execute SQL
SQL = "insert into " & myTable & " values('1000000002','29彩电')"
cnn.Execute SQL
SQL = "insert into " & myTable & " values('2000000001','空调')"
cnn.Execute SQL
MsgBox "为数据表添加了三条记录!", vbInformation, "添加记录"
cnn.Close
Set cnn = Nothing
End Sub
·分析:
SQL=”insert into 数据表名values(字段1值,字段2值,……)”
‘向SQL Server 数据库中添加记录
·实例7-13 将SQL Server数据库转换为Access数据库
引用:microsoft activex data objects 2.x library和microsoft ado ext.2.x for ddl and security Public Sub7_13()
Dim cnnSQL As New ADODB.Connection
Dim rsSQL As New ADODB.Recordset
Dim cnnData As New ADODB.Connection
Dim rsData As New ADODB.Recordset
Dim myCat As New ADOX.Catalog
Dim myTbl As New ADOX.Table
Dim mySQL As String
Dim myData As String
Dim myTable As String
Dim myName As String, myType As Integer, mySize As Integer
Dim i As Long, j As Long
‘******查询获取SQL Server数据库服务器上指定数据库表表示全部数据****** myData = "商品信息"
myTable = "商品名录"
‘建立与SQL Server数据库的连接
CnnStr = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & myData
With cnnSQL
.ConnectionString = CnnStr
.Open
End With
‘查询SQL Server数据库指定数据库表的全部记录
rsSQL.Open myTable, cnnSQL, adOpenKeyset, adLockOptimistic
‘******将查询到SQL Server数据库表全部数据保存到Access数据库表****** ‘创建Access数据库和数据表
myData = ThisWorkbook.Path & "\商品信息.mdb"
On Error Resume Next
Kill myData
On Error GoTo 0
myCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myData
With myTbl
.Name = myTable ‘指定Access数据表名
For i = 0 To rsSQL.Fields.Count - 1
myName = rsSQL.Fields(i).Name
myType = rsSQL.Fields(i).Type
If rsSQL.Fields(i).Type = 200 Then myType = adVarWChar
If rsSQL.Fields(i).Type = 135 Then myType = adDate
mySize = rsSQL.Fields(i).DefinedSize
.Columns.Append myName, myType, mySize
Next i
End With
myCat.Tables.Append myTbl
‘向access数据表中添加数据
Set cnnData = myCat.ActiveConnection
rsData.Open myTable, cnnData, adOpenKeyset, adLockOptimistic
For i = 1 To rsSQL.RecordCount
With rsData
.AddNew
For j = 0 To rsData.Fields.Count - 1
.Fields(j) = rsSQL.Fields(j).Value
Next j
.Update
End With
rsSQL.MoveNext
Next i
MsgBox "已经成功将SQL Server数据库数据保存为Access数据库!" & vbCrLf _ & "数据库名为:<" & Mid(myData, Len(ThisWorkbook.Path) + 2) & ">" _
& vbCrLf & "数据表名为:<" & myTable & ">", vbInformation
‘******关闭记录集与数据库的连接******
rsSQL.Close
cnnSQL.Close
rsData.Close
cnnData.Close
‘释放变量
Set rsSQL = Nothing
Set cnnSQL = Nothing
Set rsData = Nothing
Set cnnData = Nothing
End Sub
·实例7-14 将access数据库转换为SQL Server数据库
引用:microsoft activex data objects 2.x library
Public Sub7_14()
Dim cnnSQL As New ADODB.Connection
Dim rsSQL As New ADODB.Recordset
Dim cnnData As New ADODB.Connection
Dim rsData As New ADODB.Recordset
Dim mySQL As String
Dim myData As String
Dim myTable As String
Dim myName As String, myType As Integer, mySize As Integer
Dim i As Long, j As Long
Dim CnnStr As String, SQL As String
myData = "职工管理"
myTable = "职工基本信息"
'********查询Access数据库********
With cnnData
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open ThisWorkbook.Path & "\" & myData & ".mdb"
End With
rsData.Open myTable, cnnData, adOpenKeyset, adLockOptimistic
'********创建SQL Server数据库和数据表********
CnnStr = "Provider=SQLOLEDB.1; User ID=sa; Data Source=THTFCOMPUTER"
With cnnSQL
.ConnectionString = CnnStr
.Open
End With
'删除同名数据库
On Error Resume Next
SQL = "drop database " & myData
cnnSQL.Execute SQL
On Error GoTo 0
'创建新数据库
SQL = "create database " & myData
cnnSQL.Execute SQL
cnnSQL.Close
'建立与数据库的连接
With cnnSQL
.ConnectionString = "Provider=SQLOLEDB.1;" _
& "User ID=sa;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=" & myData
.Open
End With
'执行SQL语句创建数据表
SQL = "create table " & myTable & "("
For i = 0 To rsData.Fields.Count - 1
SQL = SQL & rsData.Fields(i).Name
If rsData.Fields(i).Type = adVarWChar _
Or rsData.Fields(i).Type = adChar Then
SQL = SQL & " varchar(" & rsData(i).DefinedSize & "),"
ElseIf rsData.Fields(i).Type = adDate Then
SQL = SQL & " datetime,"
ElseIf rsData.Fields(i).Type = adCurrency Then
SQL = SQL & " money,"
ElseIf rsData.Fields(i).Type = adSingle _
Or rsData.Fields(i).Type = adDouble Then
SQL = SQL & " real,"
ElseIf rsData.Fields(i).Type = adInteger Then
SQL = SQL & " int,"
End If
Next i
SQL = Left(SQL, Len(SQL) - 1) & ")"
cnnSQL.Execute SQL
'查询数据表
rsSQL.Open myTable, cnnSQL, adOpenKeyset, adLockOptimistic
'将Access数据库数据保存到SQL Server数据库
For i = 1 To rsData.RecordCount
With rsSQL
.AddNew
For j = 0 To rsSQL.Fields.Count - 1
.Fields(j) = rsData.Fields(j).Value
Next j
.Update
End With
rsData.MoveNext
Next i
MsgBox "已经成功将Access数据库数据保存为SQL Server数据库!" & vbCrLf _ & "数据库名为:<" & myData & ">" _
& vbCrLf & "数据表名为:<" & myTable & ">", vbInformation rsSQL.Close
cnnSQL.Close
rsData.Close
cnnData.Close
Set rsSQL = Nothing
Set cnnSQL = Nothing
Set rsData = Nothing
Set cnnData = Nothing
End Sub