Solved

MS Access/VBA/SQL Syntax

Posted on 2011-03-10
11
617 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
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 57
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 84

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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 57
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

820 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