Solved

MS Access/VBA/SQL Syntax

Posted on 2011-03-10
11
623 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 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

710 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