By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,938 Members | 1,580 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,938 IT Pros & Developers. It's quick & easy.

UPDATE Sheet1 SET '"&text6.text&"'='"&text47.Text&"' Where Vertical = '" & Combo1.tex

P: 3
I am using VB6 and DAO connnection with Access.

I want to update the records in access using VB. The column name and comments to be updated will be provided by user in text box. Please provide me the SQL command for the same.

I tried below but not working. It is showing error "Expect End of statment"

Expand|Select|Wrap|Line Numbers
  1. UPDATE Sheet1 SET '"&text6.text&"'='"&text47.Text&"' Where Vertical = '" & Combo1.text & "'"
May 6 '15 #1
Share this Question
Share on Google+
2 Replies

Seth Schrock
Expert 2.5K+
P: 2,951
The field name wouldn't be placed inside quotes, so you would need to remove the single quotes from around the field name. Also, you wouldn't use the Text property of the controls. You could use the Value property or just not include a property name as the Value property is the default anyway. So your SQL string would look like this:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  3. strSQL = "UPDATE Sheet1 SET " & Text6 & " = '" & Text47 & _
  4. "' WHERE Vertical = '" & Combo1 & "'"
May 6 '15 #2

Expert Mod 15k+
P: 31,769
The first thing to remember when working with SQL is that you need to create a string value to pass to SQL. Of course, within that string value may also be substrings that are string values to SQL. When you appreciate which is which it makes everything so much easier.

Typically, when I'm creating complex strings that need values inserted into them I use the Replace() function. Actually, I use it so much I have a MultiReplace() function that I use instead for ease of use. I'll include the code for that below but my illustration will use the inbuilt Replace() instead :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE [Sheet1] SET [%F]='%V' Where [Vertical]='%C'"
  2. strSQL = Replace(strSQL, "%F", Me.Text6)
  3. strSQL = Replace(strSQL, "%V", Me.Text47)
  4. strSQL = Replace(strSQL, "%C", Me.Combo1)
Notice that the first line shows clearly what it is you're working with and what delimiters are used for each of the values. This is much easier to read and work with than multiple concatenation of literal and other values to create a complex string.

The code for MultiReplace() is :
Expand|Select|Wrap|Line Numbers
  1. 'MultiReplace() takes each pair of parameters from avarArgs() and replaces the
  2. '  first with the second wherever found in strMain.
  3. 'Using VbBinaryCompare means that case is recognised and not ignored.
  4. '08/05/2013 Updated to support passing of an array directly into avarArgs.
  5. Public Function MultiReplace(ByRef strMain As String _
  6.                            , ParamArray avarArgs() As Variant) As String
  7.     Dim intX As Integer
  8.     Dim avarVals() As Variant
  10.     'Code to handle avarArgs passed as an existing array.
  11.     If (UBound(avarArgs) = LBound(avarArgs)) _
  12.     And IsArray(avarArgs(LBound(avarArgs))) Then
  13.         ReDim avarVals(LBound(avarArgs) To UBound(avarArgs(LBound(avarArgs))))
  14.         For intX = LBound(avarVals) To UBound(avarVals)
  15.             avarVals(intX) = avarArgs(LBound(avarArgs))(intX)
  16.         Next intX
  17.     Else
  18.         avarVals = avarArgs
  19.     End If
  20.     If (UBound(avarVals) - LBound(avarVals)) Mod 2 = 0 Then Stop
  21.     MultiReplace = strMain
  22.     For intX = LBound(avarVals) To UBound(avarVals) Step 2
  23.         MultiReplace = Replace(Expression:=MultiReplace, _
  24.                                Find:=Nz(avarVals(intX), ""), _
  25.                                Replace:=Nz(avarVals(intX + 1), ""), _
  26.                                Compare:=vbBinaryCompare)
  27.     Next intX
  28. End Function
May 6 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.