VBA中用ADO连接SqlServer2005

关注

原创最新推荐文章于 2025-09-26 08:58:57 发布 2.4k 阅读 AI 写同款· GEO 优化 ›

后摄模块辐射超标整改某行车记录仪在EMI测试中出现辐射超标问题，通过模块剥离法定位到后摄模块的Ethernet数据线为噪声源。深入检查发现TYPE-C延长线屏蔽层未有效接地，形成辐射泄漏通道。整改措施包括加强屏蔽层360°接地、使用导电布改善连接处屏蔽等，最终使辐射降至EN55032 Class B限值以内。案例揭示了高速信号延长线中屏蔽连续性的重要性，建议设计阶段就重视连接器屏蔽设计和共模干扰抑制。阅读详情

按Alt+F11打开代码窗口，点击菜单上的"工具">>"引用"，勾选它:Microsoft ActiveX Data Objects 2.x

'================================
' 连接SqlServer2005

'================================

Private Sub ConnectDB()

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim strCnn As String

Dim strHost As String
Dim strDatabaseName As String
Dim strUserName As String
Dim strPassword As String
Dim strSQL As String

Dim lCountOfFields As Long
Dim lrow As Long
Dim lcol As Long

'================================
' 定义连接字符串
'================================

strHost = "10.203.0.XX"       'Sqlsever服务器IP地址或计算机名
strDatabaseName = "master"    '连接数据库名
strUserName = "sa"            '登录用户名

strPassword = "123" '登录密码

On Error GoTo ConnectErr:

'================================
' 建立连接
'================================

    strCnn = ""
    strCnn = strCnn & "Provider=SQLOLEDB;"
    strCnn = strCnn & "Password=" & strPassword & ";"
    strCnn = strCnn & "User ID=" & strUserName & ";"
    strCnn = strCnn & "Initial Catalog=" & strDatabaseName & ";"
    strCnn = strCnn & "Data Source = " & strHost

Set cnn= New ADODB.Connection

With cnn

        .ConnectionString = strCnn
        .Open
    End With

    MsgBox "Connect successfully!", vbOKOnly + vbInformation

'================================
' 查询数据集
'================================

Set rs = New ADODB.Recordset
strSQL = "select * from spt_values "

rs.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

'================================
' 将数据集填充到sheet
'================================

    lrow = 2
    lcol = 2

    With Worksheets("Sheet1")
        lCountOfFields = rs.Fields.Count
        .Range(Cells(lrow, lcol).Address & ":" & Cells(lrow + 2, lcol + lCountOfFields - 1).Address).ClearContents
        .Cells(lrow, lcol).CopyFromRecordset rs
    End With

    rs.Close

    Exit Sub

ConnectErr:
    MsgBox "Connect error!", vbOKOnly + vbCritical

End Sub

----------------------------------------------------------------------------------

'================================
' 断开连接

'================================

Public Sub CloseConnect()

On Error GoTo DisconnectErr:
    cnn.Close
    Set cnn = Nothing
    MsgBox "Disconnect successfully!", vbOKOnly + vbInformation
    Exit Sub

DisconnectErr:
    MsgBox "Disconnect error!", vbOKOnly + vbCritical

End Sub

收录于