Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update & strTableName (XP/2002)

    Tried SQL = "UPDATE " & strQuote & strTableName & strQuote & "Set etc. in lots of variations with no success. I need to supple a table name to a SQL statement at execution time. Thank you for any help.
    Claudette

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update & strTableName (XP/2002)

    You have to enter a space before Set or you sql statement will be
    Update TableNameSet ...
    So use :
    SQL = "UPDATE " & strQuote & strTableName & strQuote & " Set ...
    Francois

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update & strTableName (XP/2002)

    Addition :
    You don't have to supply quotes. USe:
    SQL = "UPDATE " & strTableName & " Set etc
    Francois

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update & strTableName (XP/2002)

    Addition to the addition: <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    If the table name may contain spaces or punctuation, it must be enclosed in square brackets [ and ], so it might be wise to use

    <code>SQL = "UPDATE [" & strTableName & "] Set ...</code>

  5. #5
    New Lounger
    Join Date
    Aug 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update & strTableName (XP/2002)

    Thank you, Hans and Francois. I never saw that the space had been left out before "Set" , and asumed my quotes were off. Completed working statement is
    SQL = "UPDATE " & strTableName & _
    " SET " & strTableName & ".[Supplier Number] = " & intNewNumber & _
    " WHERE " & strTableName & ".[Supplier Number] = " & intOldNumber
    This fragment is used to help repair a database where there are numerous duplicates of what should be unique keys that have to be tracked down and changed. Thank you again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •