Link to home
Start Free TrialLog in
Avatar of morrisbo
morrisbo

asked on

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

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

Avatar of Farzad Akbarnejad
Farzad Akbarnejad
Flag of Iran, Islamic Republic of image

Hello,

Use:

ALTER TABLE tblName CHANGE COLUMN NameField testChange

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

-FA


Example:

To reanme Name field in Person Table to FirstName:

ALTER TABLE Person CHANGE COLUMN Name FirstName

-FA
Avatar of morrisbo
morrisbo

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Farzad Akbarnejad
Farzad Akbarnejad
Flag of Iran, Islamic Republic of image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello,
Try

DoCmd.RunSQL  strSQL

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

-FA

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.

=============================================================================