vbs操作数据方法

Dim objConnection                          'CONNECTION object
Dim objRecordSet                                   'RECORDSET instance   
Dim objCommand                                'Command instance
Dim strConnectionString                        'Connection string

' ********************************************************************
' Function£ºConnect to DB
' Parameters£º(1)strDBType£¨DB Type£ºORACEL£»DB2£»SQL£»ACCESS£©
'             (2)strDBAlias£¨Alias of DB£©
'             (3)strUID£¨user ID£©
'             (4)strPWD£¨password£©
'             (5)strIP£¨IP of DB£ºonly SQL SERVER needed£©
'             (6)strLocalHostName£¨local machine name£ºonly SQL SERVER needed£©
'             (7)strDataSource£¨data source£ºonly ACCESS needed£»e.g. d:\yysc.mdb£©
' Return result£ºnull
' Invoke: ConnectDatabase(strDBType, strDBAlias, strUID, strPWD, strIP, strLocalHostName, strDataSource)
' ********************************************************************
Function ConnectDatabase(strDBType, strDBAlias, strUID, strPWD, strIP, strLocalHostName, strDataSource)    
    Set objConnection = CreateObject("ADODB.CONNECTION")                '1 - create CONNECTION object
    Log1("Connect to DB2: Connection String is ["&"Driver={IBM DB2 ODBC DRIVER};DBALIAS=" & strDBAlias & ";Uid="_
                & strUID )    
    Select Case UCase(Trim(strDBType))
        Case "ORACLE"
            strConnectionString = "Driver={Microsoft ODBC for Oracle};Server=" & strDBAlias & ";Uid="_
                & strUID & ";Pwd=" & strPWD & ";"                                '2 - construct connection string
            objConnection.Open strConnectionString                                '3 - open db with the connection string
        Case "DB2"
            strConnectionString = "Driver={IBM DB2 ODBC DRIVER};DBALIAS=" & strDBAlias & ";Uid="_
               & strUID & ";Pwd=" & strPWD & ";"
            objConnection.Open strConnectionString                
        Case "SQL"
             strConnectionString = "DRIVER=SQL Server; SERVER=" & strIP & "; UID=" & strUID & "; PWD="_
                 & strPWD & "; APP=Microsoft Office 2003;WSID=" & strLocalHostName & "; DATABASE=" & strDBAlias & ";"
            objConnection.Open strConnectionString                                           
        Case "ACCESS"
            strConnectionString = "provider=microsoft.jet.oledb.4.0;data source=" & strDataSource &_
                ";Jet OLEDBatabase Password=" & strPWD & ";"
            objConnection.Open strConnectionString      
        Case "MYSQL"
            strConnectionString = "Provider=MSDASQL.1;Persist Security Info=True;Extended Properties='Driver=MySQL ODBC 5.1 Driver;SERVER=" & strIP & ";Uid="_
               & strUID & ";Pwd=" & strPWD & ";DATABASE=" & strDBAlias & ";PORT=3306'"               
            objConnection.Open strConnectionString                
        Case Else
            Log1("The format is not correct" & vbCrLf & "only support DBs£ºORACLE£»DB2£»SQL£»ACCESS£»EXCEL")
            MsgBox "The format is not correct" & vbCrLf & "only support DBs£ºORACLE£»DB2£»SQL£»ACCESS£»EXCEL"
    End Select
   
    If (objConnection.State = 0) Then
        Log1("Fail to connect to DB£¡")
        MsgBox "Fail to connect to DB£¡"
    End If
   
End Function

' ********************************************************************
' Function£ºQuery DB (Query single column£©;
' Parameters£º  (1)strSql£ºSQL
'               (2)strFieldName£ºfield name
'               (3)str_Array_QueryResult£ºthe name of an array£¨return the result for this column£©
' Return£º  intArrayLength£ºthe account of records
'           str_Array_QueryResult£ºthe name of an array
' Invoke: intArrayLength = QueryDatabase(strSql, strFieldName, str_Array_QueryResult)
' ********************************************************************
Function QueryDatabase(strSql, strFieldName, str_Array_QueryResult)
    Dim intArrayLength                                                                                     'the length of array
     Dim i
   
    i = 0  
    str_Array_QueryResult = Array()                                'initialize an array as an empty array
   
    Set objRecordSet = CreateObject("ADODB.RECORDSET")                '4 - create RECORDSET object
    Set objCommand = CreateObject("ADODB.COMMAND")              '5 - create COMMAND object
    objCommand.ActiveConnection = objConnection
    objCommand.CommandText = strSql
        objRecordSet.CursorLocation = 3
        objRecordSet.Open objCommand                            '6 - Execute SQL, keep results in RECORDSET object
   
    intArrayLength = objRecordSet.RecordCount                  'Define an array which length is the account of returning results
   
    If intArrayLength > 0 Then
                ReDim str_Array_QueryResult(intArrayLength-1)
               
                Do While NOT objRecordSet.EOF                                                         
                    str_Array_QueryResult(i) = objRecordSet(strFieldName)
                        'Debug.WriteLine str_Array_QueryResult(i)
                        objRecordSet.MoveNext
                        i = i + 1
                Loop
'        Else
                'ReDim str_Array_QueryResult(0)      
                'str_Array_QueryResult(0) = ""    
    End If
   
    QueryDatabase = intArrayLength
    'Set objCommand = Nothing
    'Set objRecordSet = Nothing
    
End Function

' ********************************************************************
' Function£ºINSERT¡¢DELETE and UPDATE
' Parameters£º(1)strSql£ºSQL
' Return£ºnull
' Invoke: UpdateDatabase(strSql)
' ********************************************************************
Function UpdateDatabase(strSql)
        Dim objCommand
        Dim objField       
       
        Set objCommand = CreateObject("ADODB.COMMAND")
        Set objRecordSet = CreateObject("ADODB.RECORDSET")
        objCommand.CommandText = strSql
        objCommand.ActiveConnection = objConnection
        Log1("Execute SQL: "&strSql)
        Set objRecordSet = objCommand.Execute
       
'        Do Until objRecordSet.EOF
       
'                For Each objField In objRecordSet.Fields
'                        Log1(objField.Name & ": " & objField.Value & "   ")
'                        Debug.Write objField.Name & ": " & objField.Value & "   "
'                Next
               
'                objRecordSet.MoveNext
'                Debug.WriteLine
'        Loop       
       
'        Set objCommand = Nothing
'        Set objRecordSet = Nothing
               
End Function

Function CloseDatabase()
    If objRecordSet is Nothing then
    'msgbox("test1")
    else
    objRecordSet.Close
    'MsgBox("test2")
    end if
    
    If objConnection is Nothing then
    'msgbox("test1")
    else
    objConnection.Close
    'MsgBox("test2")
    end if
    
    Set objCommand = Nothing
    Set objRecordSet = Nothing
    Set objConnection = Nothing
    Log1("Close DB")
    Log1("*************########### End ###########*************")
End Function

转载于:https://www.cnblogs.com/ibelieveKelly/archive/2013/05/16/3081286.html