SQL Update query not running (might be confused by if statement)

[stevethefiend]

Hello, i am having some issues with an sql statement not running that has been built upo dynamically in code:

Private Sub cmdSave_Click()
Text8.SetFocus
StrDescription = Text8.Text
Text10.SetFocus
StrParent = Text10.Text
Text12.SetFocus
StrPIN = Text12.Text
Text14.SetFocus
StrDWG = Text14.Text
Text16.SetFocus
StrComment1 = Text16.Text
Text18.SetFocus
StrOldTag = Text18.Text
Text20.SetFocus
StrOldDWG = Text20.Text
Text22.SetFocus
StrComment2 = Text22.Text
Dim StrSQL As String
StrSQL = "UPDATE [" & StrUnit & " Instrumentation] SET [Description] = '" & StrDescription & "', [Parent] = '" & StrParent & "', [Plant Item No: PIN] = '" & StrPIN & "', [Dwg No:] = '" & StrDWG & "', [Comments / Issued To By] = '" & StrComment1 & "', [Old Tag ID] = '" & StrOldTag & "', [Old Dwg No:] = '" & StrOldDWG & "', [Comment] = '" & StrComment2 & "' WHERE [Inst ID] like '" & StrInstID & "' AND [Item No:] like '*" & StrItemNo & "*'"
If StrItemID <> vbNullString Then
StrSQL = StrSQL & " AND [Item ID] like '*" & StrItemID & "*'"
End If
If StrItemID <> "" Then
StrSQL = StrSQL & " AND [Item ID] like '*" & StrItemID & "*'"
End If
If StrItemID = "" Then
StrSQL = StrSQL & " AND ([Item ID] is null Or [Item ID] like '')"
End If
Debug.Print StrSQL
Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandText = StrSQL
cmd1.Execute
DoCmd.Close
End Sub

i have very similar code to this elsewhere in the application that works fine and when i run the sql query that gets spat out into the debug window it all goes as planned. it just doesnt execute, i have tried re-structuring the code in a variety of ways and am at a loss if anyone can help please please post

 

[stevethefiend]

very sorry guys but i have solved my own problem! rather than saying :

Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandText = StrSQL
cmd1.Execute

i just say:

docmd.runsql (strsql)

i shouldnt need to do it this way and would still be very interested to know why my original code didnt work because as i said earlier i have virtually identical code elsewhere in the app that works fine, but i suppose the age old saying of if you want something doing right do it yourself if you dont care use microsoft, applies.

 

[Moonshine]

Hi

I use the following piece of code to run an SQL statement on a server. Its in a module so i just use DbConnectionExecute(strSQL) to call it...

Sub DBConnectionExecute(CommandText As String)
'Main Use: Delete Or Update Statements, SQL String
    Set conn = Nothing
    Set cmd = Nothing
    Set rst = Nothing
    
    'Open the connection to the database
    conn.ConnectionString = strConnect
    conn.Open
    Set rst.ActiveConnection = conn
    
    strCommandText = CommandText
    
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = CurrentProject.Connection
        .CommandText = strCommandText
        .CommandType = adCmdUnknown
    End With
    
    cmd.Execute

End Sub

Might help deduce what is wrong with yours?

 

[stevethefiend]

hi moonshine, what you said is basically the same as what i was attempting in the first place (and didnt work).

Sub DBConnectionExecute(CommandText As String)
'Main Use: Delete Or Update Statements, SQL String
Set conn = Nothing <-------why do you need a connection
Set cmd = Nothing
Set rst = Nothing <------ why do you need a cursor?

'Open the connection to the database
conn.ConnectionString = strConnect
conn.Open
Set rst.ActiveConnection = conn

strCommandText = CommandText
<-----our code does EXACTLY the same thing from here on------>
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection <----this line sets cnn
.CommandText = strCommandText
.CommandType = adCmdUnknown
End With

cmd.Execute

End Sub

the really strange part is that i had the same code elsewhere in the app that worked perfectly. i do realise that if i were to declare my sql string in an if statement VBA wont like it if the cmd is executed outside of the if but all i was doing was performing concatactations (or however its spelt) inside the if statements. is this why the vba wasnt enjoying it possibly?

 

[Moonshine]

Set conn = Nothing <-------why do you need a connection
Set cmd = Nothing
Set rst = Nothing <------ why do you need a cursor?

The code is used many times, so i make sure that it clears the defaults first so dont end up with crossed connections or anything.

It took me a while to get this working, cos as you say VBA can be very annoying. I cant see anything wrong with the code you are using at all. All i can think is its the string your building, but if it works with DoCmd then it cant be :/

Sodding things!