Access开发培训
网站公告
·Access专家课堂QQ群号:151711184    ·Access快速开发平台下载地址及教程    ·欢迎加入Access专家课堂微信群!    ·如何快速搜索本站文章|示例|资料    
您的位置: 首页 > 技术文章 > 综合其它

excel与spl server远程交互

时 间:2018-06-08 21:17:09
作 者:萤火虫   ID:66752  城市:大理
摘 要:excel与spl server远程交互/Excel VBA + ADO + SQL
正 文:

将excel工作表数据导入到spl serve,可以充分利用SQL Server数据库在数据查询等方面的优势,提高数据

管理与分析处理效率




1,[开始]→[程序]→[Microsoft SQL Server]→[导入和导出数据]命令,打开[DTS导入/导出向导]对话框。
2,单击[下一步]按钮,打开[选择数据源]对话框。
3,由于要将excel工作表数据导入到SQL Server数据库,因此在“数据源”下拉列表中选择“Microsoft Excel 97-2000”,然后在“文件名”文本框中输入excel文件,如D:\分析数据.xls,(也可以通过右边的按钮☐查找并选择excel文件。
4,单击[下一步]按钮,打开[选择目的]对话框,在“目的”下拉列表中选择“用于SQL Server的Microsoft OLE DB提供程序”,在“服务器”下拉列表中选择SQL Server服务器名称,在“数据库”下拉列表中选择要保存数据的数据库。
5,单击[下一步]按钮,打开[指定表复制或查询]对话框,这里选择“从源数据库复制表和视图”单选按钮。
6,单击[下一步]按钮,打开[选择源表和视图]对话框,选择复制数据的工作表。
7,单击[下一步]按钮,打开[保存,调度和复制包]对话框,这里选择“立即运行”复选框和“用复制方法发布目的数据”复选框。
8,单击[下一步]按钮,打开[正在完成DTS导入/导出向导]对话框
9,单击[完成]按钮,系统弹出[正在执行包]对话框,完毕后单击[完成]按钮。关闭是[正在执行包]对话框
10,[开始]→[程序]→[Microsoft SQL Server]→[企业管理器]命令,打开SQL Server企业管理器窗口,展开数据库和表目录,可以见到已经在指定的数据库创建了一个名为工作表名称的表

在excel工作表中制作一个按钮,绑定如下代码 或读或写 SQL Server中表的数据
参见实例:
点击下载此示例

Public 多层嵌套查询()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL As String
    '建立与指定SQL Server数据库的连接
    cnn.ConnectionString = "Provider=SQLOLEDB;" _
        & "User ID=sa;" _
        & "Password =180****;" _
        & "Data Source=***.16.222.1;" _
        & "Initial Catalog =学生管理"
    cnn.Open
    '查询数据
    SQL = "select 姓名 from 学生  where 学号 in" _
        & "(select 学号 from 成绩 where 成绩=" _
        & "(select max(成绩) from 成绩))"
    Set rs = cnn.Execute(SQL)
    '复制查询出的数据
    Cells.Clear
    Range("A1") = "姓名"
    Range("A1").Font.Bold = True
    Range("A2").CopyFromRecordset rs
    Columns.AutoFit
    '关闭记录集以及与数据库的连接
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

Public Sub 查询表中所有记录的指定字段()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL As String, mydata As String, mytable As String
    mydata = "学生管理"           '指定要修改的数据库
    mytable = "班主任"              '指定数据表
    '清除工作表的所有数据
    Cells.Clear
    '建立与指定SQL Server数据库的连接
    cnn.ConnectionString = "Provider=SQLOLEDB;" _
        & "User ID=sa;" _
        & "Password =180****;" _
        & "Data Source=***.16.222.1;" _
        & "Initial Catalog =" & mydata
    cnn.Open
    '查询全表某些字段的记录
    SQL = "select 姓名,性别,职称 from " & mytable
    Set rs = cnn.Execute(SQL)
    '复制字段名
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1) = rs.Fields(i).Name
        Cells(1, i + 1).Font.Bold = True
    Next i
    '复制全部记录数据
    Range("A2").CopyFromRecordset rs
    '字段设置列宽至合适的宽度
    Columns.AutoFit
    '关机记录集以及与数据库的连接
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

Public Sub 根据表达式对查询结果进行排序()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL As String, mydata As String, mytable As String
    mydata = "销售管理"           '指定数据库
    mytable = "订单资料"              '指定数据表
    '清除工作表的所有数据
    Cells.Clear
    '建立与指定SQL Server数据库的连接
    cnn.ConnectionString = "Provider=SQLOLEDB;" _
        & "User ID=sa;" _
        & "Password =180****;" _
        & "Data Source=***.16.222.1;" _
        & "Initial Catalog =" & mydata
    cnn.Open
    '查询全表某些字段的记录
    SQL = "select 数量*单价 as total,* from " & mytable _
        & " order by 数量*单价 desc"
    Set rs = cnn.Execute(SQL)
    '复制字段名
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1) = rs.Fields(i).Name
        Cells(1, i + 1).Font.Bold = True
    Next i
    '复制全部记录数据
    Range("A2").CopyFromRecordset rs
    '字段设置列宽至合适的宽度
    Columns.AutoFit
    '关机记录集以及与数据库的连接
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

Public Sub 将工作表的数据添加到数据库()
    Dim cnn As New ADODB.Connection
    Dim i As Integer
    Dim myStr As String
    '建立与指定SQL Server数据库的连接
    cnn.ConnectionString = "Provider=SQLOLEDB;" _
        & "User ID=sa;" _
        & "Password =180****;" _
        & "Data Source=***.16.222.1;" _
        & "Initial Catalog =学生管理"
    cnn.Open
    '添加新记录
    If MsgBox("下面将把工作表的数据添加到数据表<成绩>!是否添加?", _
        vbQuestion + vbYesNo) = vbYes Then
        myColumn = "(学号, 课程代码, 成绩, 学期) "
        For i = 2 To Range("A65536").End(xlUp).Row
            myStr = " values("
            myStr = myStr & "'" & Cells(i, 1) & "','" & Cells(i, 2) _
                & "'," & Cells(i, 3) & ",'" & Cells(i, 4) & "')"
            SQL = "insert into 成绩 " & myColumn & " " & myStr
            cnn.Execute SQL
        Next i
        MsgBox "数据添加成功!", vbInformation
    Else
        MsgBox "添加数据操作被取消!", vbExclamation
    End If
    '关闭记录集以及与数据库的连接
    cnn.Close
    Set cnn = Nothing
End Sub

Public 一次修改多条记录()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    '建立与指定SQL Server数据库的连接
    cnn.ConnectionString = "Provider=SQLOLEDB;" _
        & "User ID=sa;" _
        & "Password =180****;" _
        & "Data Source=***.16.222.1;" _
        & "Initial Catalog =学生管理"
    cnn.Open
    '添加新记录
    If MsgBox("下面将修改数据表<成绩>的记录!是否修改?", _
    vbQuestion + vbYesNo) = vbYes Then
        SQL = "update 成绩 set 成绩 = 成绩 + 50 where 课程代码='200601003'"
        cnn.Execute SQL
        MsgBox "数据修改成功!", vbInformation
    Else
        MsgBox "修改数据操作被取消!", vbExclamation
    End If
    '关闭记录集以及与数据库的连接
    cnn.Close
    Set cnn = Nothing
End Sub

Public 删除数据表的全部记录()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    '建立与指定SQL Server数据库的连接
    cnn.ConnectionString = "Provider=SQLOLEDB;" _
        & "User ID=sa;" _
        & "Password =180****;" _
        & "Data Source=***.16.222.1;" _
        & "Initial Catalog =学生管理"
    cnn.Open
    '删除记录
    If MsgBox("下面将删除数据表<研究生>的所有记录!是否删除?", _
    vbQuestion + vbYesNo + vbDefaultButton2) = vbYes Then
        SQL = "delete from 学生"
        cnn.Execute SQL
        MsgBox "数据删除成功!", vbInformation
    Else
        MsgBox "删除数据操作被取消!", vbExclamation
    End If
    '关闭记录集以及与数据库的连接
    cnn.Close
    Set cnn = Nothing
End Sub

Public Sub 为某字段插入空值()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    '建立与指定SQL Server数据库的连接
    cnn.ConnectionString = "Provider=SQLOLEDB;" _
        & "User ID=sa;" _
        & "Password =180****;" _
        & "Data Source=***.16.222.1;" _
        & "Initial Catalog =学生管理"
    cnn.Open
    '添加新记录
    If MsgBox("下面将向数据表<成绩>添加新记录!是否添加?", _
    vbQuestion + vbYesNo) = vbYes Then
        SQL = "insert into 学生 values('A04200603','祝丽萍','女'," _
            & "'2007-9-1','A01200602 ',null,'A03001')"
        cnn.Execute SQL
        MsgBox "数据添加成功!", vbInformation
    Else
        MsgBox "添加数据操作被取消!", vbExclamation
    End If
    '关闭记录集以及与数据库的连接
    cnn.Close
    Set cnn = Nothing
End Sub


Access软件网QQ交流群 (群号:54525238)       Access源码网店

常见问答:

技术分类:

相关资源:

专栏作家

关于我们 | 服务条款 | 在线投稿 | 友情链接 | 网站统计 | 网站帮助