CODEWITHSUNDEEP
sqlvbams-accesssql-update
Nasim
Nasim

Reputation: 21

Use SQL code in vba access

I use the following code in vba access to update a column of a table, but it is not working. Please help me.

Best regards.

Dim sqlupdate As String
sqlupdate = "UPDATE Assay" _
& "SET Assay.assay_repeat = " & 0 & "" _
 & "WHERE (((Assay.[assay_repeat])= " & 1 & "));"

DoCmd.RunSQL sqlupdate

Upvotes: 1

Views: 1380

Answers (4)

Double E CPU
Double E CPU

Reputation: 69

I recommend you use Recordsets. 

Private Sub Update_My_Records(Parent As Object)
Dim Data_Recset As Object
Dim Parent_Reference As Object
Set Data_Recset = Parent_Reference.Application.DBEngine.Workspaces(0).Databases(0).OpenRecordset("SELECT * FROM Assay WHERE assay_repeat = " & 0 & ";", DB_OPEN_DYNASET)
    Data_Recset.MoveLast
    Data_Recset.MoveFirst
    Data_Recset.Edit
    Data_Recset.Fields("assay_repeat") = 1
    Data_Recset.Update
    Data_Recset.Close
Set Data_Recset = Nothing
End Sub

assumptions Parent has reference to Access.Application. (I usually pass: Form.Module.Parent reference to Sub/Function)
the table or query "Assay" already exists. You only need to update 1 row at a time But if you want to use Queries In Your Form: 

Private Sub Query_Definition_Update()
  Dim Obj_Qdef As Object
  Dim Query_Name As String
  Query_Name = "Q_Assay"
  Me.Form.Application.DBEngine.Workspaces(0).Databases(0).QueryDefs.Refresh
  Set Obj_Qdef = Me.Form.Application.DBEngine.Workspaces(0).Databases(0).QueryDefs(Query_Name)
  Obj_Qdef.SQL = Query_Update(1)
  Debug.Print Obj_Qdef.SQL
  Obj_Qdef.Execute
  ''When finished updating
  Obj_Qdef.Close
  Set Obj_Qdef = Nothing
 End Sub

'------------------------------------------------------------'

 Private Function Query_Update(New_Value as Integer) As String
  Query_Update = "UPDATE Assay" & _
           " SET Assay.assay_repeat = " & 0 & "" & _
           " WHERE (((Assay.[assay_repeat])= " & New_Value & "));"
 End Sub

Upvotes: -1

ASH
ASH

Reputation: 20302

Here is a great way to convert a SQL string to VBA code.

Creating the form

The form just needs two text boxes, and a command button. SQL statements can be quite long, so you put the text boxes on different pages of a tab control.

Create a new form (in design view.)
Add a tab control.
In the first page of the tab control, add a unbound text box.
Set its Name property to txtSql.
Increase its Height and Width so you can see many long lines at once.
In the second page of the tab control, add another unbound text box.
Name it txtVBA, and increase its height and width.
Above the tab control, add a command button.
Name it cmdSql2Vba.
Set its On Click property to [Event Procedure].
Click the Build button (...) beside this property.
When Access opens the code window, set up the code like this:

Private Sub cmdSql2Vba_Click()
    Dim strSql As String
    'Purpose:   Convert a SQL statement into a string to paste into VBA code.
    Const strcLineEnd = " "" & vbCrLf & _" & vbCrLf & """"

    If IsNull(Me.txtSQL) Then
        Beep
    Else
        strSql = Me.txtSQL
        strSql = Replace(strSql, """", """""")  'Double up any quotes.
        strSql = Replace(strSql, vbCrLf, strcLineEnd)
        strSql = "strSql = """ & strSql & """"
        Me.txtVBA = strSql
        Me.txtVBA.SetFocus
        RunCommand acCmdCopy
    End If
End Sub

http://allenbrowne.com/ser-71.html

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

You just missed space chars at end of the table name and before where.

Dim sqlupdate As String
sqlupdate = "UPDATE Assay " _
& "SET Assay.assay_repeat = " & 0 & " " _
 & "WHERE (((Assay.[assay_repeat])= " & 1 & "));"

Upvotes: 1

braX
braX

Reputation: 11755

You have an extra double quote and are missing a couple of spaces - try it like this:

Dim sqlupdate As String
sqlupdate = "UPDATE Assay" _
& " SET Assay.assay_repeat = " & 0 & " _
 & " WHERE (((Assay.[assay_repeat])= " & 1 & "));"

Upvotes: 3

Related Questions