473,320 Members | 1,904 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
2 1336
Seth Schrock
2,965 Expert 2GB
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
  2.  
  3. strSQL = "UPDATE Sheet1 SET " & Text6 & " = '" & Text47 & _
  4. "' WHERE Vertical = '" & Combo1 & "'"
May 6 '15 #2
NeoPa
32,556 Expert Mod 16PB
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
  9.  
  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

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

Similar topics

1
by: DrTebi | last post by:
Hello, I have the following problem: I used to "encode" my email address within links, in order to avoid (most) email spiders. So I had a link like this: <a...
2
by: DC Gringo | last post by:
I have an image control (that pulls an image off an ESRI map server): <ASP:IMAGE ID="imgZonedCountry" RUNAT="server"></ASP:IMAGE> In the code behind I am setting the ImageURL to a String value...
1
by: st | last post by:
Hi, I'm using xmlDocument.Save(xmlTextWriter) to create an Excel-readable file. All works well, except where I've replaced the carriage return chars in the .innertext to XML-compliant " "; It...
7
by: DC Gringo | last post by:
I am having a bear of a time with setting a URL query string as a text value in a dropdownlist and Server.URLEncode does not seem to do its job. theFullLink = theLinkPrefix &...
6
by: Rich | last post by:
Hello, I have to create a table in an Access mdb (remotely) on the fly. Create Table tbl1(fld1 Integer, fld2 varchar(10), fld3...) Then I have to insert data: Insert Into tbl1 Values(" &...
14
by: Arne | last post by:
A lot of Firefox users I know, says they have problems with validation where the ampersand sign has to be written as &amp; to be valid. I don't have Firefox my self and don't wont to install it only...
13
by: Ragnar | last post by:
Hi, 2 issues left with my tidy-work: 1) Tidy transforms a "&amp;" in the source-xml into a "&" in the tidied version. My XML-Importer cannot handle it 2) in a long <title>-string a wrap is...
1
by: ismailc | last post by:
Hi, I need help please. Update system to to new version & moved on to .Net2 But now my code that worked in my .Net1 xslt does not work. .Net1 fine: <xsl:stylesheet...
4
doma23
by: doma23 | last post by:
I have two comboboxes, cmbClient and cmbCountry. Both combos have On Key Down events set, so the user can easily go trough the combo values. Combo Client has "After Update" and "On Key Down"...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.