We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SQL Queries to create,delete,rename a column column

Medium Priority
265 Views
Last Modified: 2010-04-07
sir,
  how to create,delete,rename a filed in MS-ACCESS database by using SQL Queries. i am using visual basic 6.0 as a front end. The database is in Access 97 format created by using Visual dataManager in Visual basic 6.0. i feel great if the Queries are in VB example.
Comment
Watch Question

Top Expert 2005
Commented:
You can use ADOX or DAO to do it


1.Using ADOX to create fileds

http://www.freevbcode.com/ShowCode.Asp?ID=3315

same way delete

ADOXtable.Columns.Delete "FieldName"

2.USing DAO

Dim db as DAO.database
dim td as DAO.tabledef
dim f as Dao.field

Set db = OpenDatabase("c:\Test.mdb")

Set td = db.CreateTableDef(InitialTable)

Set f = td.CreateField("ID", dbLong)
td.Fields.Append f
Set f = td.CreateField("dtmDate", dbDate)
f.Required = True
td.Fields.Append f
Set f = td.CreateField("sDesc", dbText)
f.AllowZeroLength = True
f.SIZE = 50
td.Fields.Append f
Set f = td.CreateField("cMemo", dbMemo)
td.Fields.Append f
.......

db.TableDefs.Append td

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2005
Commented:
You can try to use alter statement as well,not sure it will work from VB

.Execute "ALTER TABLE TableName ADD COLUMN ColumnName YesNo"

YesNo - datatype
Go with dmitryz6's suggestion and use ADOX.

Leon

Author

Commented:
i have get the solution for add and delete but i need how to rename.

for add
alter table table_name add field_name type
for delete
alter table table_name drop [column] field_name
Top Expert 2005

Commented:
Try this, not sure It supported,if not use adox or DAO

ALTER TABLE table_name
   RENAME COLUMN field_name to field_name1

Author

Commented:
sir,
                  it is not woking(rename) visual datamanager.
Top Expert 2005

Commented:
Yes, what way sugested to use ADOX or ADO.

if you don't like to do it.Youcan Put data in temprory table.
Use Alter drop statement to delete dat
Use Alter Add statment to add column
And return dat back.

But better use Adox or DAO
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.