Solved

MS Access/VBA/SQL Syntax

Posted on 2011-03-10
11
612 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
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 …

770 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