ADD Field as DEFAULT VALUE in MS Access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

ADD Field as DEFAULT VALUE in MS Access

Author

Topic

c_o
Starting Member

6 Posts

Posted - 2004-11-18 : 05:52:46

I have a table, and I want to add a new column in a table with DEFAULT VALUE as int or text format by using Microsoft Access.

i did use the code below:


 alter table mytbl add default 100 for col1

but it show me the error in 100, with "Syntax error in field definition"

may i know where am i wrong?

anybody can help???

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-18 : 08:59:20

Did you check MS Access help for info on this?

i don't know if this type of statement is supported; however, if it is, it is probably closer to this:


alter table mytbl alter column col1 default 100

you may have to use DAO to do this, though. Are you familiar with VBA programming?

In VBA, you would do something like this:

currentdb.TableDefs("YourTable").Fields("YourColumn").DefaultValue = "XXX"

Though you might have to set an object variable along the way; sometimes in VBA it doesn't work the way you expect unless you set a variable to reference an object first, and then use that object variable to access/set properties and methods.

- Jeff

c_o
Starting Member

6 Posts

Posted - 2004-11-19 : 05:47:36

thanks for reply..jsmith8858

I using vb6 to create table,
I not ready familar with vba, i can change it to VBA programming also, but not very sure vba can read a text file
because i need to create those tables and fields base on a text file.

Can vba create table for others database?
normal i get use to the code below:


dim db as database
set db = currentdb()
set rst = db.OpenRecordset("tablename", dbOpenDynaset)

then.... following the code..

can i use db1.mde to create tables or fields in db2.mdb ?
db1.mde contant my vb programing
db2.mdb contant my database.

best regrad,
chloe

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-19 : 09:12:06

huh? the code you show does not create a table; it just returns a recordset from an existing table. i think you need to describe what you are trying to do.

and, yes, VBA can do just about anything that VB6 can do except that it only has basic support for creating of custom classes (but it can reference any DLL that VB6 can, so you could write the class in VB6 and use it in VBA).

- Jeff

c_o
Starting Member

6 Posts

Posted - 2004-11-19 : 10:31:48

the code that i show is to alter a table.

my main point is to create a field which contant default value in a table.

my proble is::

quote:

i have old database (db1.mdb), now i had update my software and it needs more tables and fields, so i need to write a small program to create more table and also the fields. but some of the fields have default value. so that when data added with out that field, the database will create itself.

so.. if i cant add the fields then i have to edit my software, it must add all the fields when add a new record.

chloe

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-19 : 10:38:57

this is a direct quote from your post"

quote:

Can vba create table for others database?
normal i get use to the code below:

dim db as database
set db = currentdb()
set rst = db.OpenRecordset("tablename", dbOpenDynaset)

That code does not alter or create any tables.

Either way, I showed you how to set the default value for particular column in a table -- did you understand my first post? did you try it? it is easy to use VB to alter a table and add columns and do whatever you want; read about it in Access VBA Help:


    Dim t As DAO.TableDef
    Dim f As DAO.Field
    Dim db As DAO.Database
    
    Set db = CurrentDb
    Set t = db.TableDefs("TableToAlter")
    Set f = t.CreateField("NewColumn", dbText, 30)
    f.DefaultValue = "Default"
    f.AllowZeroLength = False
    f.Required = True
    
    t.Fields.Append f

does that help you?

- Jeff

c_o
Starting Member

6 Posts

Posted - 2004-11-19 : 10:50:12

Yes it works, in ms access's form, so i am thinking whether want to change my program or not.
convert it from VB6 to VBA, because i did use some of the lib file like "shell32.dll", dun know that can use by ms access97 or not.

thanks a lot jeff.

chloe

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-19 : 11:19:16

you can run all this code in VB6. just set a reference to the Microsoft DAO 3.X Library or something like that.

- Jeff

c_o
Starting Member

6 Posts

Posted - 2004-11-20 : 12:24:07

i am now changing the program to vba.

somebody suggest my to copy the old data to new database. but i have no version control, so it will have some runtime error in no fields from old database, if i just copy directly.
is vba can check whether a/some field /s or table is inside the database? so that i will not have the error in fields missing.

since i have to redo my program in vba. so if this is better, then i should change it right?

chloe,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources