PDA

View Full Version : For Each Loop to Execute SQL



aabnormal
09-05-2008, 07:28 AM
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 :dunno due completely to my inabilites :help.

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

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

Bob Phillips
09-05-2008, 07:40 AM
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
09-05-2008, 08:14 AM
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
For Each UpdateCell In UpdateRange
If Not IsEmpty(UpdateCell.Value) Then Call ExecuteSQLCommand(UpdateCell.Value, cnn)
Next UpdateCell
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
09-05-2008, 08:20 AM
IsEmpty Function


Returns a Boolean value indicating whether a variable (javascript:hhobj_4.Click()) has been initialized.
Syntax
IsEmpty(expression)
The required expression argument (javascript:hhobj_5.Click()) is a Variant (javascript:hhobj_6.Click()) containing a numeric (javascript:hhobj_7.Click()) or string expression (javascript:hhobj_8.Click()). 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 (javascript:hhobj_9.Click()); otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants (javascript:hhobj_10.Click()).



I think you should use the Len function instead:

If Len(UpdateCell.Value) > 0 Then
Call ExecuteSQLCommand(UpdateCell.Value, cnn)
End If

Bob Phillips
09-05-2008, 08:33 AM
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.