Altering table by code

  • Thread starter Thread starter Günter Brandstätter
  • Start date Start date
G

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
 
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 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


Michel Walsh said:
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 said:
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
Click to expand...
Click to expand...
 
Günter Brandstätter said:
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.
Click to expand...

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
 
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


Bas Cost Budde said:
Günter Brandstätter said:
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.
Click to expand...

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
Click to expand...
 
Günter Brandstätter said:
Hi Bas,
Is this really, what happens when I drag a field to another position in
design-mode???
Click to expand...

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

:-)
 
You are right, that was the reason for my question. I thought, there was an
easier way to do it
Günter
 
Günter Brandstätter said:
You are right, that was the reason for my question. I thought, there was an
easier way to do it
Günter
Click to expand...

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.
 
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
 
Günter Brandstätter said:
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.
Click to expand...

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. :-)
 
Back
Top