Results 1 to 13 of 13
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Error 3450 on an UPDATE query


    In the composite screenshot below, I'm getting the error code 3450. If, rather, I hard-code the table name is seen below the error-block the UPDATE query works perfectly. strTblName is Dim'd string and I verified its runtime value in Debug. It would seem that there's a syntax error of some sort but I'm not seeing it with what experimenting I've been doing.

    Click image for larger version. 

Name:	000.jpg 
Views:	17 
Size:	66.8 KB 
ID:	34748

    Is it obvious?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not obvious to me without being able to test and/or seeing the rest, but why surround the table name with double quotes? Normally you wouldn't put quotes (double or single) around the table name OR any field name. I don't use that style of embedding quotes in sql constructs, preferring singles instead.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Yes, I tried single quotes as well but get the same error:
    Code:
    CurrentDb.Execute "UPDATE '" & strTblName & "' Set Title = '" & Me.tbTitle & "' WHERE HDID = " & intHDID
    And, of course if I try to use the variable without the quotes Access thinks it's table name and blows out with a 3078 error:

    Click image for larger version. 

Name:	001.jpg 
Views:	19 
Size:	18.3 KB 
ID:	34749

    Code:
    CurrentDb.Execute "UPDATE strTblName Set Title = '" & Me.tbTitle & "' WHERE HDID = " & intHDID
    I only wrap variables with quotes single or double when the parameter is text (String), in which cases they are required.

    BTW, in case it isn't obvious from the screenshots, Me.tbTitle is a text box (current value is "Diet-2"); intHDID is Dim'd Integer (current value is 7); strTblName is Dim'd string (current value is "tblHD1").

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps:
    Code:
    CurrentDb.Execute "UPDATE " & strTblName & " Set Title = '" & Me.tbTitle & "' WHERE HDID = " & intHDID
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As Bob shows - still have to concatenate variable. Just don't use apostrophes or doubled quotes to delimit table name. Use brackets - [ ]. And they are only needed if table name includes spaces or punctuation/special characters (except underscore).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Bob,
    The code you posted worked successfully. What got me was the inconsistency in Access where strings in some instances must be wrapped in quotes or apostrophes while in other cases they don't, noting here that both strTblName and Me.tbTitle are strings.
    Thanks,
    Bill

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Is the code statement below what you mean when you referred to the use of brackets? Table name in strTblName something like My_Table?

    Code:
    CurrentDb.Execute "UPDATE [" & strTblName & "] Set Title = '" & Me.tbTitle & "' WHERE HDID = " & intHDID

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yes, I tried single quotes as well but get the same error:
    Then you missed my point, which was, why would you surround a table or field name in any quotes in this type of sql statement?? The only time I can think of that it is necessary is in a function such as DLookup.
    I was going to suggest you surround your names with brackets because of the numbers you use in the name examples, but you didn't mention the other error message. I figured you probably have spaces in some of the names, or names that start with numbers, or that violate one of the naming rules.

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Now I'm really confused. strTblName IS NOT the name of the table. Rather, it is a string variable whose current value is the name of the table of interest, e.g., strTblName = tblHD1. Right or wrong, my general understanding is that when string variables are used in SQL and DLookup statements that they need to be wrapped in quotes or apostrophes. Obviously, this post history and your help is telling me that there are exceptions to any such understanding?

  10. #10
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    It's a string representing a table name, not a string representing a string.

    Hence no need for the quotes.

    (I think that's the most times I've used the string word in a sentence I can string together...)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I guess I would argue then that strTblName ought not be Dim'd a string but something else............... God forbid a Variant.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The table name is a string of characters but a string for the name of an object, not a string of characters that are data.

    Literal strings are enclosed in quote marks whereas variables are not. And the type of delimiter depends on what is referenced.

    The first SQL below is all literal text, the second is constructed by concatenating literal text with variables.

    CurrentDb.Execute "UPDATE [customers] SET [Title] = 'President' WHERE [HDID]=456"

    CurrentDb.Execute "UPDATE [" & strTblName & "] SET [Title] = '" & Me.tbTitle & "' WHERE [HDID] = " & intHDID

    Apostrophe delimiter for text type data, # for date/time type data, none for numbers and brackets for object names.

    Brackets only needed if names include spaces or punctuation/special characters (except underscore) or names are reserved words. Doesn't hurt to include regardless.

    Why is the table name dynamic? Why is there more than one table with same structure?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    the type of delimiter depends on what is referenced.
    What you quote is the new key to my corrected thinking and understanding, so thank you for that.

    The tables with same structure pertain to a somewhat complicated hierarchy. The entries in those tables are at different levels in the hierarchy. Two factors drive the design of the multiple tables. One is that UNION queries are not updatable and two, and more importantly is that SQL queries lack sufficient definition to deal with the complex deeply nested sorting requirements. Think of what would be required if Windows were to display the entire nested folder tree of a hard-drive with each branch of the tree sorted independently, even if the level of nesting was only 4 levels deep.

    Thanks June! As usual, you are a major help when I get bogged down in my own ignorance.
    Bill

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Syntax error in UPDATE query
    By GraeagleBill in forum Programming
    Replies: 4
    Last Post: 02-14-2018, 12:38 PM
  2. update query error
    By koncreat in forum Forms
    Replies: 7
    Last Post: 02-22-2017, 01:53 PM
  3. Update query error
    By msaccessdev in forum Access
    Replies: 1
    Last Post: 06-06-2014, 06:08 AM
  4. Update query Error
    By rd.prasanna in forum Queries
    Replies: 1
    Last Post: 10-04-2013, 03:01 AM
  5. Update query error
    By dccjr in forum Queries
    Replies: 6
    Last Post: 05-21-2013, 08:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums