Move column position use query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

can i move last column (last field) to second position

this is my example, but doesn't work
DoCmd.RunSQL "ALTER TABLE [MyTable] ALTER [MyField] POSITION = 1"

Thank You
 
Is there a reason you want to do this? Maybe there is a solution that meets
your needs that doesn't involve changing the order of the columns/fields.
 
this is bcos many new field created in runtime, and these new field will
sorting according with diff purpose. Access have this function? bcos delphi
SQL command is "ALTER TABLE MyTable ALTER COLUMN MyField POSITION 2"

Duane Hookom said:
Is there a reason you want to do this? Maybe there is a solution that meets
your needs that doesn't involve changing the order of the columns/fields.

--
Duane Hookom
MS Access MVP


HongSeng said:
can i move last column (last field) to second position

this is my example, but doesn't work
DoCmd.RunSQL "ALTER TABLE [MyTable] ALTER [MyField] POSITION = 1"

Thank You
Click to expand...
Click to expand...
 
can i move last column (last field) to second position

this is my example, but doesn't work
DoCmd.RunSQL "ALTER TABLE [MyTable] ALTER [MyField] POSITION = 1"
Click to expand...

The order of fields in a table should make NO difference to any
application. Why do you feel the need to do this?

If you (unwisely IMO) want to present users with a datasheet with
fields in a particular order, you can create a Query selecting the
fields in the desired order. Or, you could create a new table (empty)
with the fields in the desired order, run an Append query to migrate
the data into it, and delete the old table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
i just wan to know access provide this function? because the field name
always change by user in runtime, so i can not use "Select ..." query.

John Vinson said:
can i move last column (last field) to second position

this is my example, but doesn't work
DoCmd.RunSQL "ALTER TABLE [MyTable] ALTER [MyField] POSITION = 1"
Click to expand...

The order of fields in a table should make NO difference to any
application. Why do you feel the need to do this?

If you (unwisely IMO) want to present users with a datasheet with
fields in a particular order, you can create a Query selecting the
fields in the desired order. Or, you could create a new table (empty)
with the fields in the desired order, run an Append query to migrate
the data into it, and delete the old table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
I would question any application that requires users to change table
structures of tables. However, you can search Help (while in a module) on
"OrdinalPosition". There is sample DAO code that may help you:
OrdinalPosition Property Example
This example changes the OrdinalPosition property values in the Employees
TableDef in order to control the Field order in a resulting Recordset. By
setting the OrdinalPosition of all the Fields to 1, any resulting Recordset
will order the Fields alphabetically. Note that the OrdinalPosition values
in the Recordset don't match the values in the TableDef, but simply reflect
the end result of the TableDef changes.
Sub OrdinalPositionX()

Dim dbsNorthwind As Database
Dim tdfEmployees As TableDef
Dim aintPosition() As Integer
Dim astrFieldName() As String
Dim intTemp As Integer
Dim fldTemp As Field
Dim rstEmployees As Recordset

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set tdfEmployees = dbsNorthwind.TableDefs("Employees")

With tdfEmployees
' Display and store original OrdinalPosition data.
Debug.Print _
"Original OrdinalPosition data in TableDef."
ReDim aintPosition(0 To .Fields.Count - 1) As Integer
ReDim astrFieldName(0 To .Fields.Count - 1) As String
For intTemp = 0 To .Fields.Count - 1
aintPosition(intTemp) = _
.Fields(intTemp).OrdinalPosition
astrFieldName(intTemp) = .Fields(intTemp).Name
Debug.Print , aintPosition(intTemp), _
astrFieldName(intTemp)
Next intTemp

' Change OrdinalPosition data.
For Each fldTemp In .Fields
fldTemp.OrdinalPosition = 1
Next fldTemp

' Open new Recordset object to show how the
' OrdinalPosition data has affected the record order.
Debug.Print _
"OrdinalPosition data from resulting Recordset."
Set rstEmployees = dbsNorthwind.OpenRecordset( _
"SELECT * FROM Employees")
For Each fldTemp In rstEmployees.Fields
Debug.Print , fldTemp.OrdinalPosition, fldTemp.Name
Next fldTemp
rstEmployees.Close

' Restore original OrdinalPosition data because this is
' a demonstration.
For intTemp = 0 To .Fields.Count - 1
.Fields(astrFieldName(intTemp)).OrdinalPosition = _
aintPosition(intTemp)
Next intTemp

End With

dbsNorthwind.Close

End Sub

--
Duane Hookom
MS Access MVP


HongSeng said:
this is bcos many new field created in runtime, and these new field will
sorting according with diff purpose. Access have this function? bcos delphi
SQL command is "ALTER TABLE MyTable ALTER COLUMN MyField POSITION 2"

Duane Hookom said:
Is there a reason you want to do this? Maybe there is a solution that meets
your needs that doesn't involve changing the order of the columns/fields.

--
Duane Hookom
MS Access MVP


HongSeng said:
can i move last column (last field) to second position

this is my example, but doesn't work
DoCmd.RunSQL "ALTER TABLE [MyTable] ALTER [MyField] POSITION = 1"

Thank You
Click to expand...
Click to expand...
Click to expand...
 
i just wan to know access provide this function? because the field name
always change by user in runtime, so i can not use "Select ..." query.
Click to expand...

Ow. I agree wholeheartedly with Duane - this is *very* unusual design
and a wide-open invitation to lots of trouble!

As a rule, tables should be designed and set up ONCE, and if you need
to select portions of the data, calculations using the data, and so
on, you should use Queries to do so. Having users construct new tables
is NOT usual.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Dear HongSeng:

I agree with both Duane and John, but there is a possibility to
satisfy your requirements using an advanced technique called
meta-data. This technique allows users to add their own new
attributes to the objects represented in a table, and to dynamically
control their sequence.

This is a large subject, and you would be well served to search the
internet or some books for further details to see if this can be made
to fit your requirements. For instance, I'm thinking that a crosstab
of meta-data, ordered as prescribed in the meta-data table, could work
as you propose.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
thank for your help, i thought it have easy way to do this. because i'm a
beginner, so i don't wan to spend more time on this. I give up.

Thank a lot.

Tom Ellison said:
Dear HongSeng:

I agree with both Duane and John, but there is a possibility to
satisfy your requirements using an advanced technique called
meta-data. This technique allows users to add their own new
attributes to the objects represented in a table, and to dynamically
control their sequence.

This is a large subject, and you would be well served to search the
internet or some books for further details to see if this can be made
to fit your requirements. For instance, I'm thinking that a crosstab
of meta-data, ordered as prescribed in the meta-data table, could work
as you propose.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


can i move last column (last field) to second position

this is my example, but doesn't work
DoCmd.RunSQL "ALTER TABLE [MyTable] ALTER [MyField] POSITION = 1"

Thank You
Click to expand...
Click to expand...
 
Back
Top