For Each Loop to Execute SQL

[aabnormal]

So I got this code to work the other day - I believe - though I could have been halucinating due to high levels of improperly functioning code due completely to my inabilites .

What I am trying to have this code do is execute a list of SQL Update statements that I have created in column Q. I have rechecked the syntax of the SQL multiple times though I cannot figure out what is occuring - I do not even get an error message. Thanks

[vba]Option Explicit


Public Function ExecuteSQLCommand(ByVal SQL As String, con As ADODB.Connection) 'As ADODB.Recordset
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
'initialize command object
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = con
'execute the SQL statement
With cmd
.CommandText = Trim$(SQL)
.CommandType = adCmdText
.Execute
End With
ExecuteSQLCommand_Exit:
Set rst = Nothing
Set cmd = Nothing
Exit Function
End Function

Sub UpdateRecordsADO()
Dim cnn As ADODB.Connection
Dim UpdCommand As ADODB.Command
Dim dbstrg As String
Dim SQLstrg 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
Sheets("Sheet2").Select
Range("Q2").Select
Dim UpdateCell As Range
Dim UpdateRange As Range

Set UpdateRange = Range("Q2").CurrentRegion
For Each UpdateCell In UpdateRange
If Not IsEmpty(UpdateCell.Value) Then Call ExecuteSQLCommand(UpdateCell.Value, cnn)
Next UpdateCell

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

[Bob Phillips]

So what is the question? You want us to debug some code, against a database that we haven't seen, using SQL we haven't seen, and with no idea of what is or is not happening? Sounds like a good gig.

[aabnormal]

I guess my question is whether there are any apparent issues in my code - the are that I am particularly uncomfortable with is the for each loop
[VBA] For Each UpdateCell In UpdateRange
If Not IsEmpty(UpdateCell.Value) Then Call ExecuteSQLCommand(UpdateCell.Value, cnn)
Next UpdateCell [/VBA]
I do not know if this a good method for executign a list of Update statements. Thanks - pelase let me know what you would like me to provide that would help and I would be happy to attach.

[Mavyak]

IsEmpty Function


Returns a Boolean value indicating whether a variable has been initialized.
Syntax
IsEmpty(expression)
The required expression argument is a Variant containing a numeric or string expression. However, because IsEmpty is used to determine if individual variables are initialized, the expression argument is most often a single variable name.
Remarks
IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.

I think you should use the Len function instead:

[VBA]If Len(UpdateCell.Value) > 0 Then
Call ExecuteSQLCommand(UpdateCell.Value, cnn)
End If[/VBA]

[Bob Phillips]

From 6 stages divorced from the action, apart from the fact that you keep re-opening the connection, that seems OKish to me, it's a pretty standard loop.