Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

SQL Queries to create,delete,rename a column column

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.
0
sivasubramaniam_it
Asked:
sivasubramaniam_it
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
dmitryz6Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dmitryz6Commented:
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
0
 
leonstrykerCommented:
Go with dmitryz6's suggestion and use ADOX.

Leon
0
 
sivasubramaniam_itAuthor 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
0
 
dmitryz6Commented:
Try this, not sure It supported,if not use adox or DAO

ALTER TABLE table_name
   RENAME COLUMN field_name to field_name1
0
 
sivasubramaniam_itAuthor Commented:
sir,
                  it is not woking(rename) visual datamanager.
0
 
dmitryz6Commented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now