Solved

MS Access/VBA/SQL Syntax

Posted on 2011-03-10
11
606 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now