SitePoint Sponsor |
|
User Tag List
Results 1 to 8 of 8
-
Sep 19, 2002, 11:01 #1
- 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' numberDennis Pallett - NoCertainty - My Personal Weblog
The Web Network: ASPit | PHPit | WebDev-Articles
Blogs: TalkFones | Holidayzer | PHPit Blog
-
Sep 19, 2002, 11:55 #2
- 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
-
Sep 19, 2002, 12:02 #3
- 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
-
Sep 19, 2002, 12:31 #4
- 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
-
Sep 21, 2002, 07:54 #5
- 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
-
Sep 21, 2002, 08:09 #6
- 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
-
Sep 21, 2002, 10:22 #7
- 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
-
Sep 21, 2002, 10:43 #8
- 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