DoCmd.DoMenuItem - delete column? 1

[kjv1611]

Could anyone point me in the correct direction of how to code VBA to delete a specific column either by name or by numeric reference? I was thinking it would be something like this:

DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, Column(3)

[b]or[/b]

DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, Column("Name")

Or if there is a better way than using the DoMenuItem command, since according to the help file, it is only included in current versions of Access VBA for compatibility reasons with older versions.

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

 

[RoyVidar]

This a form, report, table???

For tables, you might look into some sql, for instance something like this general statement:

[tt]"alter table mytable drop column mycolumn"[/tt]

Roy-Vidar

 

[kjv1611]

Yes, it is a table.. And, I did not even consider sql for it. My guess is that would be preferable. I'll try that and post back. Thanks for the suggestion.

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]

THANKS! That worked perfectly, and very quickly at that! Excellent! Here is my code as I used it:

DoCmd.RunSQL ("alter table " & strOriginalTable & " drop column [TransDate]")

Now I just have to rename an another column to that same name.. I'm guessing use rename column as apposed to drop coumn... I'll try that now..


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]

Hmm... well, I'm trying something like this so far:

DoCmd.RunSQL ("alter table " & strOriginalTable & " rename column [NewTranDate], to [TransDate]")

But it doesn't like my syntax.. will keep trying..

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

 

[PHV]

kjv1611, no luck as there is no provision in DDL to rename a column.
Maybe you can do this within the Fields collection of a DAO.TableDef object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kjv1611]

Well, it's worth a try.. From reading some in my Access 2002 VBA Handbook - Sybex, it says something about using the Drop command to delete old column, and the add command for the new column. My only problem with that method (I think) is the place where I am wanting to do this, a simple rename would be less time consuming, b/c already have data in the new field before deleting the old field. I'll look into the tabledefs property some more here, and see if that will the the "ticket" - using the drop in sql and rename in tabledef..

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

 

[PHV]

using the drop in sql and rename in tabledef
And perhaps the two things with TableDef only.
Sorry I don't have access at hand here, so I can't be more specific.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kjv1611]

Well, looking at another website, the author stated this:

Add a Field to a Table
SQL can be used to make changes to the structure of an existing table. Fields can be added, removed or changed. Here's how to add a new field. Close the table if it is open, and return to the Immediate Window.

Enter the following line of code as a single line, then press Enter:

DoCmd.RunSQL "ALTER TABLE tblTest ADD COLUMN [Age] BYTE;”

But did not give an example for changing... this is the link in case anyone is curious...

http://www.fontstuff.com/access/acctut16pfv.htm

title is "Access and SQL Part 3: Some Practical Examples"


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