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]

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