Solved

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

Posted on 2008-10-13
8
5,039 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

760 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

19 Experts available now in Live!

Get 1:1 Help Now