Results 1 to 7 of 7
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Alter Table Statement Giving Error

    I am trying to change the column type of a field in my table with this statement



    Note - tbl is me looping thro an array of tables but for brevity sake I just hardcoded the name, yes it is a valid table name and properly spelled

    Code:
    tbl = "Products"
    strSQL = "Alter Table [" & tbl & "] Alter Column [UPC] Short Text"
    db.Execute strSQL
    but I am getting this error
    Code:
    Run-time error '3293':
    Syntax error in ALTER TABLE statement

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it won't be recognising 'short text'

    try just text or 10

    see this link for the names for various datatypes

    https://bytecomb.com/microsoft-acces...ype-reference/

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ... Alter Column [UPC] Text(n)" where n is a valid number?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    So even when I design view the table and it shows a type of Short Text, the ACTUAL type is text?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't confuse the values of properties ("Short Text" / data type) with SQL keywords/statements.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Short Text and Long Text are newer terms for Text and Memo. SQL was not modified to use Short Text and Long Text and never used Memo, only variations on Text().
    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.

  7. #7
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by Micron View Post
    Don't confuse the values of properties ("Short Text" / data type) with SQL keywords/statements.
    Hah, that makes perfect sense.

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

Similar Threads

  1. Alter Table Statement Not Executing
    By jo15765 in forum Macros
    Replies: 3
    Last Post: 02-17-2020, 10:01 AM
  2. DAO ALTER TABLE syntax error
    By GraeagleBill in forum Programming
    Replies: 7
    Last Post: 03-15-2019, 05:03 PM
  3. Iif statement giving error
    By Tazmaniac in forum Queries
    Replies: 5
    Last Post: 03-25-2018, 01:29 PM
  4. Alter Table Statement
    By jo15765 in forum Modules
    Replies: 5
    Last Post: 03-09-2017, 05:05 PM
  5. Replies: 7
    Last Post: 10-29-2014, 11:48 AM

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