Consulting

Results 1 to 2 of 2

Thread: Multiple SQL Statments in Excel

  1. #1

    Multiple SQL Statments in Excel

    Hello All-

    Sorry I posted this thread in the Excel listing as well though wanted to bring it into this section as well:


    I am attempting to execute a list of update statements in excel to write to an Access db. I have been able to connect to the db with a ADO connection and execute select statements and such by creating dynamic strings in my code --though I now would like to run a number of dynamic update statements built off of the data in my worksheets. My issues is that I am not sure whether I should/could write an Update string in my code that loops though cells to create and execute the multiplte SQL statemetns or if I should/could construct the Update statements in a column on the sheet, using concatenate, etc. functions, and then loop an execute statement through this column???

    Examples are greatly appreciated with any reccommendations. Thanks for all your help in advance and please let me know if I can provide any additional information.
    AAB

    below is some of the code that I have been mesing with - as I said I can get a singular Update to work though I have multiple similar statements that I need to run that have differing T/D's, B/S's and Prices that I need to key off of in the Update statement. THanks.

    [VBA]
    Sub UpdateSomeRecordsADO()
    Dim cnn As ADODB.Connection
    Dim UpdCommand As ADODB.Command
    Dim dbstrg As String
    Dim UpdStrg As String
    dbstrg = ""
    dbstrg = dbstrg & "O:\AP\2008\testdb.mdb"
    ' dbstrg = dbstrg & Cells(2, 12)
    ' dbstrg = dbstrg & "\North American "
    ' dbstrg = dbstrg & Cells(2, 11)
    ' dbstrg = dbstrg & ".mdb"

    '' Open the connection.
    Set cnn = New ADODB.Connection
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cnn.Open dbstrg

    '' Set the command text.
    Set UpdCommand = New ADODB.Command
    Set UpdCommand.ActiveConnection = cnn
    '' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    ' UpdStrg = "UPDATE [Sheet1] SET [Paid]= 'CONFIRMED' WHERE [Acct] = '00003' AND [T/D] = 20080512 AND [B/S] = '1' AND [Price] = ' 1388.25'"
    ' UpdCommand.Execute "UPDATE [Sheet1]SET [Paid]= 'Confirmed' WHERE [Acct] = '00003' AND [T/D] = '20080512' AND [B/S] = '1' AND [Price] = ' 1388.25'"
    ' vtSql = vtSql & " UPDATE " & "Sheet1"
    ' vtSql = vtSql & " SET Salaryx= 24680"
    ' vtSql = vtSql & " WHERE Namex='Jennings'"

    With UpdCommand
    ' .CommandText = UpdStrg
    ' .CommandType = adCmdText
    ' .Execute
    End With


    Sheets("Sheet2").Select
    Range("Q2").Select

    Set UpdStrg = Range("Q2").CurrentRegion

    UpdCommand.CommandType = adCmdText
    Dim strArray()
    strArray() = Split(CurrentRegion, [";"])
    For Each Item In strArray
    UpdCommand.Execute
    Next

    ' UpdStrg = Range("Q2")
    ' Loop
    'Do
    ' UpdCommand.Execute
    ' ActiveCell.Offset(1, 0).Select
    'Loop Until IsEmpty(ActiveCell.Offset(0, -16))

    '' Close the connections and clean up.
    cnn.Close
    Set UpdCommand = Nothing
    Set cnn = Nothing
    End Sub
    [/VBA]

  2. #2
    VBAX Regular
    Joined
    Sep 2008
    Posts
    36
    Location
    I think your problem is in this code:

    Dim strArray()
    strArray() = Split(CurrentRegion, [";"])
    For Each Item In strArray
    UpdCommand.Execute
    Next

    Instead:

    Dim strArray
    strArray = Split(CurrentRegion,";")
    For t = 1 to uBound(strArray)
    ' MsgBox strArray(t) -- use this to check the query syntax
    UpdCommand.CommandText = strArray(t)
    UpdCommand.Execute
    Next t

    S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •