Altering table by code

[Günter Brandstätter]

Hi all,
just by interest:
if I append a field to a table programmatically, is it possible to change
this fields position in the table by code also?
i used SQL to append an AutoNumber-field "Key" to an existing table.

strsql="ALTER TABLE [MyTable] ADD [Key] AutoNumber"
dbs.Execute strsql

is it now possible to "shift" this field programmatically to the first
position of the table like I would do it in design mode by dragging it
upwards??

Any answer appreciated
Günter

 

[Michel Walsh]

Hi,


For presentation? use a form. You can position the controls in the form
through the left property of the control.


Hoping it may help,
Vanderghast, Access MVP

 

[Günter Brandstätter]

Hi Michel,
Thanks for your response. It's not for presentation, just in table design
where I wanted to have this new field in first position.
I know that in forms I can haven the fields where I want, it is just for
"esthetics" of my table's design.

thanks again
Günter
-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com

Hi,


For presentation? use a form. You can position the controls in the form
through the left property of the control.


Hoping it may help,
Vanderghast, Access MVP

Hi all,
just by interest:
if I append a field to a table programmatically, is it possible to change
this fields position in the table by code also?
i used SQL to append an AutoNumber-field "Key" to an existing table.

strsql="ALTER TABLE [MyTable] ADD [Key] AutoNumber"
dbs.Execute strsql

is it now possible to "shift" this field programmatically to the first
position of the table like I would do it in design mode by dragging it
upwards??

Any answer appreciated
Günter

 

[Bas Cost Budde]

Hi Michel,
Thanks for your response. It's not for presentation, just in table design
where I wanted to have this new field in first position.
I know that in forms I can haven the fields where I want, it is just for
"esthetics" of my table's design.

Using DAO, yes.

cLocalTable: name of the table
cVeld: name of the original field

dim td as tabledef
dim fd as field
dim db as database
set db=currentdb
Set td = db.TableDefs(cLocalTable)
'it is forbidden to directly change type. So we do:
'1 insert new field
Set fd = td.CreateField(cVeld & "1", nType, nSize)
td.Fields.Append fd
td.Fields.Refresh
'2 copy data (convert, you may hope)
db.Execute "update [" & td.Name & "] set [" & fd.Name & "]=" & cVeld
'3 remove old field but store its position
nCol = td.Fields(cVeld).OrdinalPosition
td.Fields.Delete cVeld
'4 rename new field
td.Fields(cVeld & "1").Name = cVeld
'5 put field on former position
td.Fields(cVeld).OrdinalPosition = nCol

 

[Günter Brandstätter]

Hi Bas,
Is this really, what happens when I drag a field to another position in
design-mode???
I will give your code a try, i'm sure, it works
thakns
Günter


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com

Hi Michel,
Thanks for your response. It's not for presentation, just in table design
where I wanted to have this new field in first position.
I know that in forms I can haven the fields where I want, it is just for
"esthetics" of my table's design.

Using DAO, yes.

cLocalTable: name of the table
cVeld: name of the original field

dim td as tabledef
dim fd as field
dim db as database
set db=currentdb
Set td = db.TableDefs(cLocalTable)
'it is forbidden to directly change type. So we do:
'1 insert new field
Set fd = td.CreateField(cVeld & "1", nType, nSize)
td.Fields.Append fd
td.Fields.Refresh
'2 copy data (convert, you may hope)
db.Execute "update [" & td.Name & "] set [" & fd.Name & "]=" & cVeld
'3 remove old field but store its position
nCol = td.Fields(cVeld).OrdinalPosition
td.Fields.Delete cVeld
'4 rename new field
td.Fields(cVeld & "1").Name = cVeld
'5 put field on former position
td.Fields(cVeld).OrdinalPosition = nCol

 

[Bas Cost Budde]

Hi Bas,
Is this really, what happens when I drag a field to another position in
design-mode???

The complete procedure contains the remark "how the heck does Access do
this from the user interface?"

 

[Günter Brandstätter]

You are right, that was the reason for my question. I thought, there was an
easier way to do it
Günter

 

[Bas Cost Budde]

You are right, that was the reason for my question. I thought, there was an
easier way to do it
Günter

I just read that from Access 2000 the DDL statement "alter table ...
alter column" is valid. That is much what we want. But do we have A2K? I
don't.

 

[Günter Brandstätter]

Hi Bas,
Thanks again, your code pointed me in the right direction. I found the
easiest way to do the job:

For Each fldMyField In tblMyTable
fldMyField.OrdinalPosition = fldMyField.OrdinalPosition + 1
Next
tbl.Fields![MyFieldName].OrdinalPosition = 0

that's what answered my question.

Thanks
Günter

 

[Bas Cost Budde]

Hi Bas,
Thanks again, your code pointed me in the right direction. I found the
easiest way to do the job:

For Each fldMyField In tblMyTable
fldMyField.OrdinalPosition = fldMyField.OrdinalPosition + 1
Next
tbl.Fields![MyFieldName].OrdinalPosition = 0

that's what answered my question.

Aah, so you asked for a finger and I gave you the arm? Funny. When I
read your post some event must have fired in my head telling me that you
wanted to change the field type.

Well, maybe you get to use the complete routine once.