Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have access database table and recording data from my workbook to there.
I want to update individual item, however the code I created does not work.
Please help me figure out what the mistake is.

What I have tried:

Row = ActiveCell.Row
cn.Execute "UPDATE ([WBS Tasks] AS A " & _
            "SET (A.[Title] = '" & Sheets("TaskCreate").Cells(Row, 2) & "', " & _
           "[Assigned To Engineer] = (Select ID from [Engineers (master)] Where [Employee Name] ='" & Sheets("TaskCreate").Cells(Row, 5) & "')," & _
            "[Planned Eng Hours] = '" & Sheets("TaskCreate").Cells(Row, 4) & "', [Planned Variance Hours] = 0, [WBS Element] = '" & Sheets("TaskCreate").Cells(Row, 7) & "', " & _
 "[Description] = '" & Sheets("TaskCreate").Cells(Row, 8) & "', [ProductLine] = '" & Sheets("TaskCreate").Cells(Row, 9) & "', [Due Date] = '" & Sheets("TaskCreate").Cells(Row, 11) & "', [Modified] = '" & Date & "');" & _
            "WHERE ([ID] = '" & Sheets("TaskCreate").Cells(Row, 12) & "' );"
Posted
Updated 15-Feb-23 22:58pm
Comments
Andre Oosthuizen 15-Feb-23 10:25am    
We need more information, by stating the code does not work is not helping us a lot. Why does it not work, where are the errors occurring, is any data updated or returned etc.
CHill60 15-Feb-23 11:39am    
What is cn? Presumably this is Excel VBA?
Richard Deeming 15-Feb-23 11:56am    
Not like that!

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.
[no name] 15-Feb-23 21:23pm    
Build the query string and display it; so you know what you're trying to execute.
Member 15627495 16-Feb-23 2:58am    
as sheets("....")Cells(row,n) return an Object Type , do you have a '.tostring()' method ?

- you don't need an alias 'A' as long you're updating just one table and an easy query.

what is the error message you have ?

1 solution

Your first mistake is concatenating strings to create your sql statement - see the comment above from @Richard-Deeming. Use Parameterised queries instead. An example could look like this (NB this has not been tested because you haven't shared enough information about your tables or data)
VB
Sub demo()
    
    Dim sql As String
    sql = "UPDATE ([WBS Tasks] " & _
                        "SET (A.[Title] = ?, " & _
                        "[Assigned To Engineer] = (Select ID from [Engineers (master)] Where [Employee Name] =?)," & _
                        "[Planned Eng Hours] = ?, [Planned Variance Hours] = 0, [WBS Element] =?, " & _
                        "[Description] = ?, [ProductLine] = ?, [Due Date] = , [Modified] = ?);" & _
                        "WHERE ([ID] = ? );"
    
    Dim cn As ADODB.Connection, cmd As ADODB.Command
    Set cn = New ADODB.Connection
    cn.Open
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandText = sql
        .CommandType = adCmdText

        Dim parm(8) As ADODB.Parameter
        'These MUST be created in the correct order matching the ? in the sql statement
        Set parm(0) = .CreateParameter("title", adVarChar, adParamInput)
        parm(0).Value = Sheets("TaskCreate").Cells(Row, 2).Value
        parm(0).Size = 255      'required for adVarChar types

        Set parm(1) = .CreateParameter("empname", adVarChar, adParamInput)
        parm(1).Value = Sheets("TaskCreate").Cells(Row, 5).Value
        parm(1).Size = 255

        '.. etc
        
        Dim i As Integer
        For i = 0 To UBound(parm) - 1
            .Parameters.Append parm(i)
        Next
        
        .Execute
    End With
    
End Sub
Points to note:
- In the absence of a response to my question I've assumed you are using an ADODB connection, Excel VBA code to update an MS Access database
- The parameters must be appended in the correct order to match the "?" in the query
- I've only done the first two parameters for you
- It's better to explicitly state which property of the cell you are using rather than rely on the default i.e use Sheets("TaskCreate").Cells(Row, 2).Value
- This wouldn't pass our in-house peer review because you have not defined which workbook Sheets refers to (users have a habit of clicking elsewhere while things are running!)
- instead of having the sub-query for the Engineer consider running that query first and assign the value to a parameter - it will make your final query more efficient
- instead of having Date passed as a parameter use the built in Access function for date in the raw query
- you are using a lot of "magic" numbers for your columns - consider using an enum to make it clearer what is in each column e.g.
VB
Enum eCols
    etitle = 2
    empname = 5
    element = 7
    descrip = 8
    eproduct = 9
    taskcreate = 11
    ID = 12
End Enum
then you can do things like
VB
parm(0).Value = Sheets("TaskCreate").Cells(Row, eCols.etitle).Value

Edit: V2 - changed upper end of loop from hard-coded "magic" number to UBound(parm) - 1
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900