VBA环境下ACCESS、EXCEL、SQL SERVER互相访问

VBA环境下ACCESS、EXCEL、SQL SERVER互相访问
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

相关主题
相关文档
最新文档