Solved

how to alter column name in access 2000 table using sql and vb6.0

Posted on 2008-10-13
8
5,165 Views
Last Modified: 2013-12-17
I am attempting to change a field (column) name in a table in access 2000 mdb from VB6.0 program using the sql alter command.

I am getting the following error message "Syntax error in ALTER TABLE statement. in Rename column procedure.

The error occurs in the Cmd.Execute in the code snippet below.

I performed a debug.print to display the actual syntax of strSQL when the Cmd.Execute is executed. It is the next statement below.

ALTER TABLE 'TblWrittenPrem' RENAME COLUMN 'TbWrittenPremID' to 'TblwrittenPremID';

Hope someone can point me in the right direction.

Thanks,

Morris
Private Sub RenameColumn()
    On Error GoTo RenameColumnErr
    Dim DSource As String
    DSource = gPathData$ & "\" & "ARSSL_DATA.mdb"
    Dim Cmd As New ADODB.Command
    Dim cnn As New ADODB.Connection
    Dim strSQL As String
    Dim strDBPath As String
    cnn.Mode = adModeReadWrite
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0;Jet OLEDB"
    strDBPath = DSource
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath
    Cmd.ActiveConnection = cnn
    Cmd.CommandType = adCmdText
  
    Dim chrTable As String
    Dim old_name As String
    Dim new_name As String
  
    chrTable = "TblWrittenPrem"
    old_name = "TbWrittenPremID"
    new_name = "TblwrittenPremID"
 
    strSQL = "ALTER TABLE " & Chr(39) & chrTable & Chr(39) & " RENAME COLUMN " & Chr(39) & old_name & Chr(39) & " to " & Chr(39) & new_name & Chr(39) & "; "
    Cmd.CommandText = strSQL
    Cmd.Execute
 
    cnn.Close
    Set Cmd = Nothing
    Set cnn = Nothing
 
    Exit Sub
    
RenameColumnErr:
    Screen.MousePointer = vbDefault
    MsgBox Err.Description & " in RenameColumn procedure"
 
End Sub

Open in new window

0
Comment
Question by:morrisbo
  • 4
  • 3
8 Comments
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 22708866
Hello,

Use:

ALTER TABLE tblName CHANGE COLUMN NameField testChange

http://www.eggheadcafe.com/software/aspnet/30410750/sql-change-column-name.aspx

-FA


0
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 22708867
Example:

To reanme Name field in Person Table to FirstName:

ALTER TABLE Person CHANGE COLUMN Name FirstName

-FA
0
 

Author Comment

by:morrisbo
ID: 22709058
FarzadA,

I changed the statement to

    strSQL = "ALTER TABLE " & Chr(39) & chrTable & Chr(39) & " CHANGE COLUMN " & Chr(39) & old_name & Chr(39) & " " & Chr(39) & new_name & Chr(39) & "; "

but got same error message.

The change was to replace the word RENAME with CHANGE.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 14

Accepted Solution

by:
Farzad Akbarnejad earned 400 total points
ID: 22709089
You don't need to use Chr(39). Remove all Chr(39).

strSQL = "ALTER TABLE " & chrTable & " CHANGE COLUMN " & old_name & " " & new_name & "; "

-FA

0
 

Author Comment

by:morrisbo
ID: 22709148
I made the change to remove the chr(39).  Ran program with same error message, then I removed the ; at the end, but same error message.  I have used similiar logic to add  new column , and also to add a new table with no problem.  I appreciate your help.  Seems so simple but just can't seem to get it to work.


     strSQL = "ALTER TABLE " & _
               chrTable & _
               " CHANGE COLUMN " & _
               old_name & _
               " " & _
               new_name
0
 
LVL 15

Assisted Solution

by:cquinn
cquinn earned 100 total points
ID: 22709670
If the column names (old or new) or table name contains spaces, you will need to surround them with braces - try this:

strSQL = "ALTER TABLE [" & chrTable & "] CHANGE COLUMN [" & old_name & "] [" & new_name & "]; "

0
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 22709726
Hello,
Try

DoCmd.RunSQL  strSQL

where strSql is your query string. DoCmd uses DAO instead of ADO.

-FA
0
 

Author Comment

by:morrisbo
ID: 22717857

Could not get anything to work, so I finally went into the MSDN web site to the area discussing Morcosoft's implementation of SQL for access2000 and apparently a column cannot be renamed directly.

Do you think I am reading their comments correctly?

I appreciate your help.

Here is their comments.
=================================================================================
http://msdn.microsoft.com/en-us/library/aa140015(office.10).aspx
=================================================================================
If you want to change the name of a field, you will have to remove the field and then recreate it. To remove a field, use the DROP COLUMN clause with the field name only. I have also included the address below.

 Copy Code
ALTER TABLE tblCustomers
   DROP COLUMN Address
 

Note that using this method will eliminate the existing data for the field. If you want to preserve the existing data, you should change the field's name with the table design mode of the Access user interface, or write code to preserve the current data in a temporary table and append it back to the renamed table.

A default value is the value that is entered in a field any time a new record is added to a table and no value is specified for that particular column. To set a default value for a field, use the DEFAULT keyword after declaring the field type in either an ADD COLUMN or ALTER COLUMN clause.

=============================================================================
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The viewer will learn how to set up a document for the web and print and the recommended PPI for printing.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

685 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