alter table method to rename a table field 1

[kjv1611]

I was attempting to use 2 short sql strings to basically remove a field from a table, and rename an existing field to the removed field's name. Here is what I have been trying.. any suggestions?

DoCmd.RunSQL ("alter table " & strOriginalTable & " drop column [TransDate]")
DoCmd.RunSQL ("alter table " & strOriginalTable & " modify column [NewTransDate] Date to TransDate")

I've tried using modify, rename, change, and add in the second option there, but can't seem to get it to work. Does anyone know a good way to rename a table field (there is existing data in the field if that helps).



Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

As an update, I've also tried doing something like this:

DoCmd.RunSQL ("alter table " & strOriginalTable & " drop column [TransDate]")
DoCmd.RunSQL ("alter table " & strOriginalTable & " add column [TransDate] Date")
CurrentDb.TableDefs(strOriginalTable).Fields("TransDate") = ("SELECT " & strOriginalTable & ".NewTransDate FROM " & strOriginalTable & ";")

The 3rd argument to no avail thus far, b/c getting following error for that argument:
[blue]Run-time error '3219':
Invalid operation.[/blue]



Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Well, in case anyone is interested, I got it to work like this (if there is a faster work around anyone knows of, that would be much appreciated, b/c it does take a few seconds):

    rs.MoveFirst
    Do While Not rs.EOF
        rs.Edit
        rs.Fields("TransDate") = rs.Fields("NewTransDate")
        rs.Update
        rs.MoveNext
    Loop
    Set rs = Nothing
    
    DoCmd.RunSQL ("alter table " & strOriginalTable & " drop column [NewTransDate]")

So, this is working, but if anyone has any ideas to make it a little quicker by using soley sql, I'm all ears.



Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[JonFer]

I don't think you can rename a field with SQL but you can add and drop fields as well as update one field with the contents of another:

DoCmd.RunSQL("Update YourTable Set Transdate=NewTransdate")


John

http://www.interautomateconsulting.com

 

[kjv1611]

JonFer, thanks a ton! That did the same thing and ran faster than the Loop! Have a star!

By the way, would you recommend any book(s) or other resources as a good reference and/or tutorial for SQL as used with Access, or one that would cover both Access and SQL Server? Any recommendations?


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[JonFer]

I don't know any off the top of my head but I think I saw some books mentioned a few months back in other posts.

John

http://www.interautomateconsulting.com