Solved

MS Access/VBA/SQL Syntax

Posted on 2011-03-10
11
630 Views
Last Modified: 2012-05-11
Hi All,

I'm having some sytax issues with my VBA code using embedded SQL statements in my DoCmd.  Specifically, I have a table that is generated in my code dynamically.  This is fine.  When this table 'POC2' is created the columns are named with 'tempstr' prefacing the column heading.

For example, let's sya tempstr is 'bill' the I will have a column called bill_Obj, BUT I just want to rename the column (AFTER THE TABLE IS CREATED) to Obj.  I have been trying to figure out how to construct my Command to accurately rename the column 'bill_Obj' to just 'Obj'

I have tried:

DoCmd.RunSQL "ALTER TABLE POC2 RENAME COLUMN " & tempstr & "_Obj' TO Obj;"
DoCmd.RunSQL "ALTER TABLE POC2 RENAME COLUMN " & tempstr & " + '_Obj' TO Obj;"
DoCmd.RunSQL "ALTER TABLE POC2 RENAME COLUMN " & tempstr & " & '_Obj' TO Obj;"

etc.

I can NOT figure out the proper sytax to make this happen - help!
0
Comment
Question by:Tom_wbi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35096535
have you tried writing a query to do this manually, making it data definition.

I don;t know ( have never tried or heard of it working) to use runsql to alter access tables.

i am not sure DDL can rename....

might be best to add a new column of desiredname, run update to copy values, then drop field.

ALTER TABLE TestAllTypes DROP COLUMN MyBinary

http://www.databasejournal.com/features/msaccess/article.php/3460771/Access-Data-Definition-Language-DDL-Creating-and-Altering-Tables-On-The-Fly.htm

you can also programatically run a query... would be worth making a querydef, assigning it your run sql sql, and seeing if there is a difference between execute (DDL) query and run sql..

http://msdn.microsoft.com/en-us/library/bb177500(v=office.12).aspx


0
 
LVL 58
ID: 35096545
Tom,

  You can't alter the column name with SQL.  You need to add a new column and drop the old one.

JimD.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35096558
In Access, ALTER TABLE does not support the RENAME operator. See this from Help:

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL]     [CONSTRAINT index] |     ALTER COLUMN field type[(size)] |     CONSTRAINT multifieldindex} |     DROP {COLUMN field I CONSTRAINT indexname} }

If you need to rename a column, you must do this:

1) Add a new column with the new "name"
2) Copy everything to that new column
3) Delete the existing column



0
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 
LVL 58
ID: 35096576

  BTW, a very helpful series of articles on what you can do with JET SQL:

http://msdn.microsoft.com/en-us/library/aa140011(v=office.10).aspx

 Bottom of the page has links to part II and III (Intermediate and Advanced SQL).

JimD.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35096582
Access sql won't do this.

You would have to use vba code to reaname the fields.

(Untested)
dim tdf as dao.tabledef
dim fld as dao.field
set tdf = currentdb.tabledefs("Poc2")
for each fld in tdf.fields
if left(fld.name,5) = "bill_" then fld.name = mid(fld.name,6)
next fld
set tdf = nothing
0
 

Author Closing Comment

by:Tom_wbi
ID: 35096723
Thanks, LMS and All...

I have decided to RENAME the COLUMNS using the AS statement at the time my table POC2 is being created. That way the column headings will be named what I want and I won't have any need to RENAME them later!
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35106469

Someone else got points for proposing a solution I offered in the first answer...,

in fact three of us said it, and the accepted solution was the last person to say it?


0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35106477
And peter57 offered the most elegant solution anyway!.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35110175
Jim.

I suggested the same method as the person who go the points... as did you.

Surely a Split should have been in order.

Me: might be best to add a new column of desiredname, run update to copy values, then drop field.

Jim:You need to add a new column and drop the old one.

LMS: 1) Add a new column with the new "name"
2) Copy everything to that new column
3) Delete the existing column

They are all the same solution are they not?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

617 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