Moving Columns using code 1

[RamziSaab]

Is there an easy way to move columns arround in access using code...pls help!

 

[KenReay]

Hi

Are you talking about columns within a table or columns within a datasheet view form?

If you are talking about a table see help for the Ordinal property of a field (DAO object)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MichaelRed]

If you are discussion moving the columns (Fields) in a "TABLE", it is (at best) a pointless exercise, and possibly dangerous approach.

One of the more basic tenants of relational database theory is to abstract the 'physical' details (such as fireld position) from the programmer / user. ths intent is to "view" the data is whatever manner (or order) desired through the use of 'abstract' (i.e. NAMED_ references, such as 'queries".

If you are interested in "views" such as a datasheet (continuious form), you may layout the "order" by simply binding the various controls to the desired column either during design or execution.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RamziSaab]

Micheal i can understnad that it is pointless, but could you explain why it would be dangerous? Could u explain this a bit further?

 

[MichaelRed]

If 'others' have also been using the ordinal position for their purpoes, then the results of theeir procedures are incorrect. Suppose, for instance, someone has built a 'dictionary' of field attributes (properties) and uses the (static) results for almost any reason (reporting OR manipulation). Their 'results' are now incorrect. For a simple example, let us assume that anopther party is finding the median values of a number of 'columns', using their ordinal position (admittedly, this is 'practice' is as poorly thought out as moving the columns) whose valus set is quite similar and you swap the actual positions, so the report now swaps the reporteed median values -and the other party will probably not notice the difference.

For me, the ONLY valid use of the 'ordinal position' reference would be to generate documentation, similar to the Ms. A. Tools-->Documention, with perhaps a more discreete selection of the properties, however ther is no restriction in the use of such references in Ms. literature.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RamziSaab]

I see your point! When i first started to design the database (with very little experience in programing) i made it select fields by the order they appear in forms etc. but i used to import files manually and add access default PK...however now i have made code to import the excel file automatically and using and use the following SQL to make the PK

strsql = "ALTER table [" & strtblname2 & "] add column Temp COUNTER"

this will add it to the end of the tbale and hence everything will be off by 1 (as it used to be the first column)...anyway now that i have a bit more understanding of vba i will go in and change the codes to look for column names instead of their positions, however i got used to the PK to be in the first column so will put it there anyway


Thanks for both ur help

Ramzi

 

[RamziSaab]

Ok i have a small problem...i have the following code

Set tbl = db.TableDefs(strtblName)
Set fld = tbl.Fields(19)
fld.OrdinalPosition = 0

hwoever it seems to move it to the 2nd column...not the first one on the table...

i found a way round this using this
Set tbl = db.TableDefs(strtblName)
Set fld = tbl.Fields(19)
fld.OrdinalPosition = 0

Set tbl = db.TableDefs(strtblName)
Set fld = tbl.Fields(0)
fld.OrdinalPosition = 1

however i am worried about this as the first time i tried it it moved it to the first column...can anyone explain this behaviour?

 

[MichaelRed]

Since I am a big (NOT) fan of this whole concept, you should take this with a BLOCK (not grain) of salt.

The Ordinal position property SEEMS to be "one based", not "Zero Based" (Unlike the Fields -which are certainly ZERO based), so the setting is skewed. Read the {F1} CAREFULLY - esp. the exampple.

A.[tab]it never sets the ordinal position to zero.

B.[tab]it explicitly notes that using the ordinal position property, it is possible to have tow (or more) fields with the SAME ordinal Position property (value).

Thus even MORE reason to avoid the pesky oddity.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RamziSaab]

unfortunately the help file does not work on my computer..could u possibly copu and paste it here , just gives me a grey screen when i press it??

 

[MichaelRed]

????? " ... help file does not work on my computer ... " ?????


How in the world did you get taht far w/o help?

You really SHOULD re-install/update the app to include it. Getting ISDN would also be to yor advantage, however both are free at ms website. You should visit and use their facillity, as changes / updates are made frequently.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RamziSaab]

i dont understand it myself ... it does work for most things but when i go to the object browser and search for ordinalposition and press F1 it gives me an error and tells ask me if i want to repair, i press ok but all iget is a grey screen...

If i go straight to the help file which does owrk and go to index and type oridinalposition and click on the results again nothng comes up....but the good news is that most things i look for in the help file are there