How do I rename a field? 
Author Message
 How do I rename a field?

I have an Access Table, "tblData", with a Text field called "Program". I
need to convert the "Program" field to a Long data type, so I suppose I have
to create a new field and transpose the data from the old to the new (The
old field contains data like "1- One", "2- Two", "3- Three", which I will
transpose to the new field as 1, 2, 3, etc....using VB recordsets [Can it be
done with just an SQL?])

How do I rename the old field to, say, "Program_Old" before creating the
new, Long, field "Program"? Using DAO.

--

Regards

Julian Milano

[Remove "_NOSPAM" to reply]



Sat, 17 Dec 2005 01:11:37 GMT  
 How do I rename a field?
To work with the database structure you have to use DB specific code: ADOX
or DAO for Access and TSQL for SQL Server etc.

Chris.


Quote:
> I have an Access Table, "tblData", with a Text field called "Program". I
> need to convert the "Program" field to a Long data type, so I suppose I
have
> to create a new field and transpose the data from the old to the new (The
> old field contains data like "1- One", "2- Two", "3- Three", which I will
> transpose to the new field as 1, 2, 3, etc....using VB recordsets [Can it
be
> done with just an SQL?])

> How do I rename the old field to, say, "Program_Old" before creating the
> new, Long, field "Program"? Using DAO.

> --

> Regards

> Julian Milano

> [Remove "_NOSPAM" to reply]



Sat, 17 Dec 2005 02:50:55 GMT  
 How do I rename a field?
But I AM using DAO on my Access2000 database. I just can't figger out how to
rename a field in the database using DAO?

--

Regards

Julian Milano

[Remove "_NOSPAM" to reply]


Quote:
> To work with the database structure you have to use DB specific code: ADOX
> or DAO for Access and TSQL for SQL Server etc.

> Chris.



> > I have an Access Table, "tblData", with a Text field called "Program". I
> > need to convert the "Program" field to a Long data type, so I suppose I
> have
> > to create a new field and transpose the data from the old to the new
(The
> > old field contains data like "1- One", "2- Two", "3- Three", which I
will
> > transpose to the new field as 1, 2, 3, etc....using VB recordsets [Can
it
> be
> > done with just an SQL?])

> > How do I rename the old field to, say, "Program_Old" before creating the
> > new, Long, field "Program"? Using DAO.

> > --

> > Regards

> > Julian Milano

> > [Remove "_NOSPAM" to reply]



Sat, 17 Dec 2005 03:26:44 GMT  
 How do I rename a field?
Sorry - I probably misread the 'Can it be done with just SQL' and posted
based on that.

Anyway. Google has a lot of hits for this and I seriously recommend you get
hold of MDB2BAS and look at its generated code.

MDB2BAS (saves the code to create an entire database from scratch using DAO
as a VB module).
http://www.stamey.nu/VBAnswerNetwork/

Change field in MDB table (have to create a new field of the correct type,
copy the data and then delete the old field).
http://www.freevbcode.com/ShowCode.asp?ID=4599&NoBox=True

Hope that helps a bit.

Chris.


Quote:
> But I AM using DAO on my Access2000 database. I just can't figger out how
to
> rename a field in the database using DAO?

> --

> Regards

> Julian Milano

> [Remove "_NOSPAM" to reply]



> > To work with the database structure you have to use DB specific code:
ADOX
> > or DAO for Access and TSQL for SQL Server etc.

> > Chris.



> > > I have an Access Table, "tblData", with a Text field called "Program".
I
> > > need to convert the "Program" field to a Long data type, so I suppose
I
> > have
> > > to create a new field and transpose the data from the old to the new
> (The
> > > old field contains data like "1- One", "2- Two", "3- Three", which I
> will
> > > transpose to the new field as 1, 2, 3, etc....using VB recordsets [Can
> it
> > be
> > > done with just an SQL?])

> > > How do I rename the old field to, say, "Program_Old" before creating
the
> > > new, Long, field "Program"? Using DAO.

> > > --

> > > Regards

> > > Julian Milano

> > > [Remove "_NOSPAM" to reply]



Sat, 17 Dec 2005 05:14:11 GMT  
 How do I rename a field?
On Tue, 1 Jul 2003 03:11:37 +1000, "Julian Milano"

microsoft.public.vb.database:

It could be done with SQL as long as the data that needed to be transposed was
in an easy format with no large amounts of variations.  For example if the data
you described below was always in that format "number", "-", <space>, "Text
Description" then the following might work.

alter table tbldata add Program_old Long Integer;
update tbldata set Program_old = Mid$([Program],1,InStr(1,[Program],"-")-1);
alter table tbldata drop column [Program];
alter table tbldata add Program Long Integer;
update tbldata set Program = Program_old;
alter table tbldata drop column [Program_old];

You might want to look up DDL (Data Definition Language) in your help files or
on Google for more info.

Hope this helps
William

Quote:
>I have an Access Table, "tblData", with a Text field called "Program". I
>need to convert the "Program" field to a Long data type, so I suppose I have
>to create a new field and transpose the data from the old to the new (The
>old field contains data like "1- One", "2- Two", "3- Three", which I will
>transpose to the new field as 1, 2, 3, etc....using VB recordsets [Can it be
>done with just an SQL?])

>How do I rename the old field to, say, "Program_Old" before creating the
>new, Long, field "Program"? Using DAO.



Sat, 17 Dec 2005 06:27:45 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Doing a SaveAs without renaming the working document

2. Rename field using VBA

3. rename fields

4. Rename a field programmatically

5. Renaming a field in a recordset

6. Rename existing table fields

7. Need Help Renaming Tables and Fields

8. Renaming FIelds in a Table

9. Renaming table fields bug

10. Rename Field ??

11. Renaming a field in a table

12. HOWTO: rename a table's field from Access Basic

 

 
Powered by phpBB® Forum Software