Solved

How to change field-order in Access table ?

Posted on 1998-08-07
6
497 Views
Last Modified: 2008-02-01
Hello, How to change field-order in Access table (Row-order in design).
I like to make the fieldorder in Table_2 same as in Table_1:

I try to explain what i like to do:

Table_1 looks like this:

Table_1.Field(0).Name = Field0
Table_1.Field(1).Name = Field1
Table_1.Field(2).Name = Field2

Table_2 looks like this:

Table_2.Field(0).Name = Field2
Table_2.Field(1).Name = Field1
Table_2.Field(2).Name = Field0

*****************************************************************
* I like to have 'Your code' here to make the changes. *
*****************************************************************

Table_2 should looks like this after 'Your code':

Table_2.Field(0).Name = Field0
Table_2.Field(1).Name = Field1
Table_2.Field(2).Name = Field2

/Janne, SWEDEN
0
Comment
Question by:jannea
6 Comments
 
LVL 9

Expert Comment

by:cymbolic
ID: 1468272
You can only do this by creating a temporary table with the right field order, performing a "Select Into" SQL sequence, dropping the old table, and renaming the temporary table.  This becomes much more difficult and slower if you have a number of relationships defined between your target table and other tables in the form of foreign keys.  I'm dropping this in as a comment.  The actual code can be done in SQL DDL statements particular to your target Database and Access method.  You can dig these out of your relevant help systems.

Of course, who really needs to order the fields anyway?  You shouldn't be using integer offsets to pick up your columns in code, since that makes your app insensitive (not strongly typed) to your table structure.  If you get a table in with one column missing, your app is too dumb to know it, and your programmer has to figure it out.  SInce you can order your resultsets out an any order, and you should be identifying your columns by name, not position, why do you feel you need to do this?  Even if you want to make generic routines you can use the rs(colname) syntax.
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1468273
You can only do this by creating a temporary table with the right field order, performing a "Select Into" SQL sequence, dropping the old table, and renaming the temporary table.  This becomes much more difficult and slower if you have a number of relationships defined between your target table and other tables in the form of foreign keys.  I'm dropping this in as a comment.  The actual code can be done in SQL DDL statements particular to your target Database and Access method.  You can dig these out of your relevant help systems.

Of course, who really needs to order the fields anyway?  You shouldn't be using integer offsets to pick up your columns in code, since that makes your app insensitive (not strongly typed) to your table structure.  If you get a table in with one column missing, your app is too dumb to know it, and your programmer has to figure it out.  SiInce you can order your resultsets out in any column order, and you should be identifying your columns by name, not position, why do you feel you need to do this?  Even if you want to make generic routines you can use the rs(colname) syntax, or you can have an external list off field offsets to use with each table, which could externally control the order you process the fields.
0
 
LVL 2

Expert Comment

by:percosolator
ID: 1468274
If all you want to do is to visually re-order the tables, in table view click once on the column header and hold, drag it to where you would like to place it and release the mouse button.

If you want to physically move the columns in a table, then you _are_ going to have to create an intermediary table as cymbolic detailed above.

I also strongly agree with his last statement.  The actual physical layout of a table is unimportant, if, you use field names to specify columns (I.E.  rs!LastName instead of rs.field(2), etc.)

I've had to go into code that was written that way, it's an absolute nightmare to debug; and if I ever get my hands on the guy who wrote it.....

It's an absolute nightmare to debug.

Use field names.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Accepted Solution

by:
dapperry earned 50 total points
ID: 1468275
Sure you can do it with VB, and you don't need to make a new table as 'cymbolic' stated.  Just
make sure you have the DAO Library referenced in your application, and add the following:

Dim db As Database        
Dim tdf1 As TableDef
Dim tdf2 As TableDef
Dim fld1 As Field
Dim fld2 As Field

Set db = Opendatabase("c:\mytest.mdb")
Set tdf1 = db.TableDefs("Table1")
Set tdf2 = db.TableDefs("Table2")

For Each fld1 In tdf1.Fields
    Set fld2 = tdf2.Fields(fld1.Name)
    fld2.OrdinalPosition = fld1.OrdinalPosition
Next fld1
tdf2.Fields.Refresh
MsgBox "All done"

Let me know if you have any questions.

:) D Perry
0
 
LVL 2

Expert Comment

by:percosolator
ID: 1468276
dapperry:

now that was cool! learn something new every day.  i'm almost of a mind to give you points for showing me that.
0
 
LVL 1

Author Comment

by:jannea
ID: 1468277
Thanks it did work fine !

I will use the code in a function who compare and make too databases identical (Tables, Qurrys, Relations...), instead of doing this manually.
Nice to have this when working with cliens beta versions, to update their DB whithoute deleting data.

Thanks again to all comments and the excellent answer.

/Janne
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA error replacing data 6 36
passing parameter in sql procedure 9 56
SQL VB connection works in one PC and doesn't in another 15 55
Problem to With line 4 36
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now