VBA+SQL访问数据库基本框架

系统框架

代码框架

Public Const ConStrSCM = "Provider=xxx;Server=xxx;Database=xxx;User ID=xxx;Password=xxx;" '定义数据库链接 Sub Query_Click() 'Query按钮 Call QueryData End Sub Sub QueryData() Dim cn As Object Dim rs As Object Dim i As Integer Dim j As Integer Dim iID As Integer Dim sfirstName As String On Error GoTo err_label ' 在执行时不更新画面,也不报警 Application.ScreenUpdating = False Application.DisplayAlerts = False ' 新增DB Connection连线 Set cn = CreateObject("ADODB.Connection") ' 设定DB连线逾时的时间 cn.CommandTimeout = 300 ' 连接DB cn.Open ConStrSCM Set rs = CreateObject("ADODB.RecordSet") iID = Cells(1, 2).Value sfirstName = Cells(2, 2).Value sSQL = "select * from xxx" '下SQL指令 Set rs = cn.Execute(sSQL) '将数据整表抄入Excel If Not rs.EOF Then Cells(6, 1).CopyFromRecordset rs End If Call ReleaseDB(cn, rs) ' 计算完成后恢复用户Excel的默认计算方式 Application.Calculation = iDefaultCalculate Exit Sub err_label: Call ReleaseDB(cn, rs) ' 发生异常后也需恢复画面更新,以及报警通知 Application.DisplayAlerts = True Application.ScreenUpdating = True ' 计算完成后恢复用户Excel的默认计算方式 Application.Calculation = iDefaultCalculate '提示SQL语句错误信息 MsgBox Err.Description, vbOKOnly + vbExclamation, "提示" End Sub Sub ReleaseDB(ByRef cn As Object, ByRef rs As Object) ' 关闭Recordset If rs.State = 1 Then rs.Close End If ' 关闭DB连线 If cn.State = 1 Then cn.Close End If ' 释放Recordset资源 Set rs = Nothing ' 释放DB连线资源 Set cn = Nothing End Sub