SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    What a twist! Kings's Avatar
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    954
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inserting new field in MS Access database.

    How can I insert a new field in a MS Access database table. I've already got the following but I can't get it to work with a default value.
    ALTER TABLE Articles ADD 'Moderated' number
    How do I add a default value?
    Dennis Pallett - NoCertainty - My Personal Weblog
    The Web Network: ASPit | PHPit | WebDev-Articles
    Blogs: TalkFones | Holidayzer | PHPit Blog

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ALTER TABLE Articles
    ADD COLUMN Moderated NUMBER DEFAULT 99

    or, if the column exists
    ALTER TABLE Articles
    ALTER COLUMN Moderated SET DEFAULT 99

  3. #3
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And... don't forget:
    UPDATE Articles SET Moderated = 99
    after adding the column, because all existing rows in the table will get Moderated = NULL

  4. #4
    What a twist! Kings's Avatar
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    954
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much
    Dennis Pallett - NoCertainty - My Personal Weblog
    The Web Network: ASPit | PHPit | WebDev-Articles
    Blogs: TalkFones | Holidayzer | PHPit Blog

  5. #5
    What a twist! Kings's Avatar
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    954
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Those SQL Querys aren't working. This is an MS Access 2000 db.
    Dennis Pallett - NoCertainty - My Personal Weblog
    The Web Network: ASPit | PHPit | WebDev-Articles
    Blogs: TalkFones | Holidayzer | PHPit Blog

  6. #6
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This works for me (Access 2000):

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path\filename.mdb"
    strSql = "ALTER TABLE Articles ALTER COLUMN Moderated SET DEFAULT 99"
    Set objCon = CreateObject("ADODB.Connection")
    objCon.Open strCon
    objCon.Execute strSql
    Set objCon = Nothing

  7. #7
    What a twist! Kings's Avatar
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    954
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, I'll use my own code then (I was using a default database editor made by someone else). I'll let you know if it works
    Dennis Pallett - NoCertainty - My Personal Weblog
    The Web Network: ASPit | PHPit | WebDev-Articles
    Blogs: TalkFones | Holidayzer | PHPit Blog

  8. #8
    What a twist! Kings's Avatar
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    954
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I think it worked! From now on, I'll be using my own code instead of the default database editor.
    Dennis Pallett - NoCertainty - My Personal Weblog
    The Web Network: ASPit | PHPit | WebDev-Articles
    Blogs: TalkFones | Holidayzer | PHPit Blog


Bookmarks

Posting Permissions

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