VBA ADO备份,只有csv数据源进行了测试

最新推荐文章于 2020-12-10 11:04:34 发布

dcw0402

最新推荐文章于 2020-12-10 11:04:34 发布

阅读量220

点赞数

分类专栏：娱乐

本文链接：https://blog.csdn.net/dcw0402/article/details/104546599

版权

娱乐专栏收录该内容

16 篇文章 1 订阅

订阅专栏

'import Microsoft ActiveX Data Objects 6.1 Library   or 2.X
Public gBaseLibConn As Object, gBaseLibRs As Object, gBaseLibConnStr$
Sub getConnectionObj(ByVal dbtype$, ByVal ServerPath$, ByVal db$, ByVal uid$, ByVal pwd$)
    If Not gBaseLibConn Is Nothing Then Set gBaseLibConn = Nothing

    Set gBaseLibConn = CreateObject("ADODB.Connection")
    Set gBaseLibRs = CreateObject("ADODB.Recordset")
    gBaseLibConnStr = ""

    Select Case dbtype
    Case "SQL"
        gBaseLibConnStr = "driver={sql server};" & "server=" & ServerPath & ";" & "database=" & db & ";" & "uid=" & uid & ";" & "pwd=" & pwd & ";"
    Case "Access"
        gBaseLibConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ServerPath & ";Jet OLEDB:Database Password=" & pwd
    Case "Excel"
        'gBaseLibConnStr = "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties=Excel 8.0;Data Source=" & ServerPath
        gBaseLibConnStr = "Excel File=" & ServerPath
    Case "MYSQL"
        gBaseLibConnStr = "DRIVER={MySql ODBC 3.51 Driver};SERVER=" & ServerPath & ";Database=" & db & ";Uid=" & uid & ";Pwd=" & pwd & ";Stmt=set names GBK"
    Case "CSV"
        'gBaseLibConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ServerPath & ";Extended Properties='Text;HDR=Yes;FMT=Delimited'"  'for before Office 2007 version
        gBaseLibConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ServerPath & ";Extended Properties='Text;HDR=Yes;FMT=Delimited'" 'after Office 2007 version
    End Select
End Sub

Sub openConnect(ByVal dummy%)
    If gBaseLibConn.State = 1 Then gBaseLibConn.Close
    gBaseLibConn.Open gBaseLibConnStr
End Sub

Sub closeConnect(ByVal dummy%)
    Set gBaseLibRs = Nothing
    gBaseLibConn.Close
    Set gBaseLibConn = Nothing
End Sub

Sub excuteSql(ByVal ssql$)
    If gBaseLibRs Is Nothing Then
        Set gBaseLibRs = New ADODB.Recordset
    ElseIf gBaseLibRs.State = adStateOpen Then
            gBaseLibRs.Close
            Set gBaseLibRs = Nothing
            Set gBaseLibRs = New ADODB.Recordset
    End If
    gBaseLibRs.Open ssql, gBaseLibConn, 1, 3
End Sub

Sub testADO_csv()
    Dim ssql$, i%
    Call getConnectionObj("CSV", "E:\tmp\", "", "", "")
    Call openConnect(1)
    ssql = "select * from (select * from [a.csv] union select * from [b.csv]) as ut where ut.aType='A'  and ut.x > 1.0"
    Call excuteSql(ssql)
    Sheets("testAdo").Cells.Clear
    
    For i = 0 To gBaseLibRs.Fields.Count - 1
        Sheets("testAdo").Cells(1, i + 1) = gBaseLibRs.Fields(i).Name
    Next
    Sheets("testAdo").Range("a2").CopyFromRecordset gBaseLibRs
    Call closeConnect(1)
End Sub