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
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
Example:
To reanme Name field in Person Table to FirstName:
ALTER TABLE Person CHANGE COLUMN Name FirstName
-FA
To reanme Name field in Person Table to FirstName:
ALTER TABLE Person CHANGE COLUMN Name FirstName
-FA
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
strSQL = "ALTER TABLE " & _
chrTable & _
" CHANGE COLUMN " & _
old_name & _
" " & _
new_name
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello,
Try
DoCmd.RunSQL strSQL
where strSql is your query string. DoCmd uses DAO instead of ADO.
-FA
Try
DoCmd.RunSQL strSQL
where strSql is your query string. DoCmd uses DAO instead of ADO.
-FA
ASKER
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.
==========================
Use:
ALTER TABLE tblName CHANGE COLUMN NameField testChange
http://www.eggheadcafe.com/software/aspnet/30410750/sql-change-column-name.aspx
-FA