Queries in MS-ACCESS

[Eyal]

Hi



I would like to use SQL statments for doing the following operations in
MS-ACCESS tables.



1. Rename a column name?

2. Change columns order in a table? (For new column and for existing
column)



I tried to use SQL statement, which I found on the Internet but it doesn’t
work.



I used:



ALTER TABLE tblname RENAME COLUMN oldName TO new Name



ALTER TABLE tblname ADD COLUMN colName colType [FIRST / AFTER existColName]



Thanks,



Eyal

 

[James Goodman]

1. You cannot do this in Access.

However, you could alter the schema using ADO/DAO from VBA.

2. There is no concept of column order in a relational database.

 

[David Kennedy]

Eyal,
first off Im no expert on this subject but maybe my answers can help.

1.Rename a column name?
Im not sure about this but I would using TableDef create a new field,
update the contents of the 'old' field to the new field then delete the old
field.
ie
Dim YourTableName As TableDef
Dim db As Database




2.Change columns order in a table? (For new column and for existing
column)
as an example try using something like: tdf.Fields![ID].OrdinalPosition = 1
setting OrdinalPosition to 1 places the ID field as the first field in the
table

 

[David Kennedy]

Eyal,
Firstly I am no expert on this subject but maybe my answers will help

1. Rename a column name? google for infrmation on TableDef
Im not sure about this one but I would create a new field name,update from
the old
field to the new field and delete the old field
This code below is a very rough outline but if you play around with it
it might work:

Dim aTableName As TableDef
Dim db As Database
Dim sqlStmt as string
Dim sqlDelStmt as string

Set db = CurrentDb
Set aTableName = db.CreateTableDef(YourTableName)

aTableName .Fields.Append aTableName .CreateField("newFieldName", dbText)

db.TableDefs.Append aTableName
.......
sqlStmt = "UPDATE " & YourTableName & " SET " & YourTableName &
".newFieldName = [" & YourTableName & "]![oldFieldName];"
DoCmd.RunSql sqlStmt
.......
sqlDelStmt = "DELETE " & YourTableName & ".oldFieldName FROM " &
YourTableName & ";"
DoCmd.RunSql sqlDelStmt

close db etc...

2. Change columns order in a table? (For new column and for existing
column)
you dont need an sql stmt for this try using
tdf.Fields![ID].OrdinalPosition = 1
this places the ID field as the first field in the processing table

As i said im no expert
Regards
David K

 

[sam]

1 rename coloum
use a query to produce the view you want

In the query editor >>> Field
select all the field you want to view
On the field you want the name changed
new name: [original field name]

this also works if you create a new table
I have done the following
new name: str([Phone Number])
where the phone number was a number

HTH

Sam

 

[Eyal]

Thank you for your replay.



Unfortunately, I would like to do those actions only using sql statements
and on the original table



Thanks again



Eyal