# MS Access: Re-order and rename field columns



## Chalo (Apr 27, 2007)

Hi there,

Is it possible somehow to reorder and rename a table field column in Access automatically by any means? Any solutions

Thanks
Chalo


----------



## Ziggy1 (Jun 18, 2002)

for what purpose? Maybe if you describe why we can give you another idea


----------



## Rockn (Jul 29, 2001)

You can reorder and rename columns by using queries or reports and have them display in any order you wish.


----------



## Chalo (Apr 27, 2007)

Thank you for your replies.

I'm trying to automate the process of importing an Excel file that contains the headers in a colummn run a crosstab query to create the fields, once in access I need to come up with a routine that will in some cases the columns are re-order and sometimes the header names changed. 

Right now these is done manually and it needs to be automated to minimize the time it takes.

Any help will be much appreciate.


----------



## Rollin_Again (Sep 4, 2003)

Can you post a sample Excel file? Creating tables, fields, and records can all be done automatically using VBA. It would be easier to understand what you want to do if we can view sample data.

Regards,
Rollin


----------



## Chalo (Apr 27, 2007)

The Excel file contains several columns which one containing the headers for my table in Access. Once imported I need to put these header names into Access then reorder the columns and/or rename some of these. Hope this helps. 

You reply partially answers my question about using VBA, but I don't know where to start. Once I've done this part I have to import the data for this table which is another Excel file.

Thanks
Chalo


----------



## Rollin_Again (Sep 4, 2003)

Can you post a sample Excel file showing how the data is layed out? You can use the "Manage Attachments" button at the bottom of the posting window to add the Excel file to upload with your next post. If you have problems with uploading it you can email to me at rollin_again at hotmail dot com and I will post it for you.

There are several people in the forum including myself who can help you with the VBA part.

Regards,
Rollin


----------



## Chalo (Apr 27, 2007)

Hi Rollin

Here is the file which is one several others I will need to import into access. The file is in .CSV format I saved it as text delimited for upload.

The headers are in column B (fieldnames). The rest of the information is not needed. This file can contain 50 or more rows in some other files. Once in Access these fieldnames have to be reorder into columns and in some cases renamed. The variable here is that is not known which fieldnames have to be reordered or renamed. I guess an option to input the sequence at process will be required for the user to enter the information. 

Thanks again,
Chalo


----------



## Rollin_Again (Sep 4, 2003)

Automating the creation of the fields into a table is very easy using VBA. Will the database table already exist or will it need to be created at run-time?

As far a the renaming of fields and/or reordering of them. Why can't the names and order of fields be done in the Excel file prior to running the code? You implied in your prior post that the renaming and re-ordering would need to be done after entering the info into Access. Why can't this be done before?

Regards,
Rollin


----------



## Chalo (Apr 27, 2007)

The tables already exist as it have been created previously but since the data changes with every cycle I will need to remove/delete these tables and create them from scratch again.

Regarding the renaming and reordering of the fields it is a valid question. If is easier to automate this portion in Excel then import to Access, I will certainly take it into account. 

Regards,
Chalo


----------



## Rollin_Again (Sep 4, 2003)

In your sample file you list the field names below. Do all these fields go into the same table or different tables? (please provide the names of the tables involved)

From what I gather so far you want to delete an existing table in the database and then re-create it using the field names from the CSV file? You also want the option of using a different name for the field instead of what is listed in the CSV file? In addition to this, you want to be able to enter the fields into the table in a different order than what is listed in the CSV file? Does this sound right?


DATEFROM
DATETO
JOB
LANGU
TXTMD
TXTSH


----------



## Chalo (Apr 27, 2007)

These fieldnames belong to one table. The table is called "ACT". To give you a bit of history, these files come from another system (of course incompatible) so it generates a file .CSV for the fieldnames (unfortunately in a column) and a corresponding file for the data that will be incorporated into the access table once the fieldnames have been reposition.

Your assessment is correct. To be precise not all the fieldnames need to be rename or reorder just some of them, which I don't know. Here is a sample picture of what could be the end result.

Regards,
Chalo


----------



## Chalo (Apr 27, 2007)

Hi Rollin, any more suggestions. I found a solution in this section under the archive on how to rename the fields, jus need how to reorder the columns. Half way there.

thx
Chalo


----------



## Rollin_Again (Sep 4, 2003)

Sorry....been busy with work and didn't have a chance to play with this yet. Since the file comes to you in CSV format you should be able to open it in Excel. Manually cut and paste and re-name the field names in the Excel file exactly as you want them to appear in the database. When you run the macro it will go through each record one by one and put them into the table exactly as you have them listed in the Excel file.

At what point will you know if the field names will need to be re-named and re-ordered ?

Regards,
Rollin


----------



## Chalo (Apr 27, 2007)

Sorry... been quite sick and my project delayed also.

The files need to be reordered and rename after the import to Access, but if there is a way to do it before it will be great too. Another programmer here has told me that they will try to get the header field reordered before download but don't have confirmation on that.

Thanks,
Chalo


----------



## OBP (Mar 8, 2005)

Chalo, if you are doing this in Access as your first post says, you do not have to rename or move any fields at all.
All you have to do is set up a table the way that you want it to appear.
Import the data in to a Temporary table, I have a form that was created for another poster that allows you to select the field names on the left hand side (Imported table) and match them to the Left Hand side (Required layout Table) and an SQL routine puts the data in the correct place. The version that I have was for importing an Excel Worksheet.

Do you think you could modify it to do what you want?


----------



## Chalo (Apr 27, 2007)

OBP This sounds good. I believe I recently did something like this. I already have the table layed out so it's a matter of importing that. For now I'm on hold for this project but I will revisit this topic at a latter time.

Thank you all who have replied to my post.
Chalo


----------



## slurpee55 (Oct 20, 2004)

The easiest way to do what you want without any coding would be to import the columns you need from one or more tables into a query. In the query you can arrange them in any order you wish (even column1 from Table1, column4 from Table2, column2 from Table1 or any mixture you want) and you can rename them by typing the name you want into the Field Properties row called Caption. If you export your data from the query, it will retain the Caption names.


----------



## Rockn (Jul 29, 2001)

WIth the import process you can map fields quite easily without having to rename fields in the Access table at all. I am sure this can be done quite easily using VBA as well.


----------



## jdesine (Jul 23, 2007)

Chalo,
I have the same situation like you but was unable to find a solution as how to rename the filed. Can you please provide me the link that you mention in you messages?
Thanks,


----------



## Chalo (Apr 27, 2007)

Jdesine, sorry I didn't keep the link. Your best bet is to look again if it still there.

Chalo


----------



## jdesine (Jul 23, 2007)

I actually trried to query but wasnt able to find it.
K anyways no worries.
Thanks


----------

