[RESOLVED] Help with MS Access Query String

[modpluz]

hi guyz,

pls does anybody know why this gives error

ALTER TABLE tblDVD CHANGE 'DVD_Name' 'DVD_Title' TEXT

I'm working with MS ACCESS

[RobDog888]

Change is probably your issue here.

See the syntax definition for ALTER TABLE in Access -
http://office.microsoft.com/assistan...&respos=1&rt=6

[modpluz]

yeah i've seen that but its not helping me.
ok let me come out clean...
how do i change the name of a particular column in a table, in an MS ACCESS Database?

[RobDog888]

Just specify the Column attribute property you need.

ALTER TABLE tblDVD ALTER COLUMN DVD_Name TEXT(25)

[modpluz]

Just specify the Column attribute property you need.

the column attribute i need is the name property

ALTER TABLE tblDVD ALTER COLUMN DVD_Name TEXT(25)

from what i can see above, there is no place to specify new column name

thanks

[ganeshmoorthy]

I doubt you can change the field name in access, instead i do this way to change the field name, add a new temp field transfer the old field values to the temp and drop the old field create a new field as you wish and then transfer the values from your temp field to this field...

ALTER TABLE tblDVD Add TempField TEXT(25)
Update Table tblDVD Set TempField=DVD_Name
Alter Table tblDVD Drop Column DVD_Name
Alter Table tblDVD Add NewFieldName VarChar(25) Null 'Access accepts varchar in sql
Update Table tblDVD Set NewFieldName=TempField
Alter Table Drop Column TempField

[modpluz]

I doubt you can change the field name in access, instead i do this way to change the field name, add a new temp field transfer the old field values to the temp and drop the old field create a new field as you wish and then transfer the values from your temp field to this field...

ALTER TABLE tblDVD Add TempField TEXT(25)
Update Table tblDVD Set TempField=DVD_Name
Alter Table tblDVD Drop Column DVD_Name
Alter Table tblDVD Add NewFieldName VarChar(25) Null 'Access accepts varchar in sql
Update Table tblDVD Set NewFieldName=TempField
Alter Table Drop Column TempField

i really appreciate your help but the code is not working
it bumped into an error on getting to

Update Table tblDVD Set TempField=DVD_Name

here is what i did...

'i created a sub function of my own
'the id_col helps in knowing what Record ID is
Sub changeFldName(old_name As String, new_name As String, _
                    id_col As String, table_name As String)
 Dim rsFld As ADODB.Recordset, _
 sSQL As String, _
 rcd_cnt As Long, r_i As Long
 
On Error Resume Next
 
 Set rsFld = New ADODB.Recordset
'pls note that the function [B]Connect[/B] is a function that initiates the connection
 If cn.State = adStateClosed Then Call Connect
 
 sSQL = "ALTER TABLE " & table_name & " ADD " & new_name & " TEXT"
 cn.Execute (sSQL)
 
 
 sSQL = "SELECT * FROM " & table_name
 rsFld.CursorType = 2
 rsFld.Open sSQL, cn
 rcd_cnt = rsFld.RecordCount
 For r_i = 1 To rcd_cnt
  sSQL = "UPDATE " & table_name & " SET " & new_name & "=" & old_name & _
            " WHERE " & id_col & "=" & rsFld(id_col)
  cn.Execute (sSQL)
  rsFld.MoveNext
 Next r_i
  rsFld.Close
  Set rsFld = Nothing
 
 sSQL = "ALTER TABLE " & table_name & " DROP " & old_name
 cn.Execute (sSQL)
End Sub

and you call it like this...

Call changeFldName("DVD_Name", "NewFieldName", "DVD_ID", "tblDVD")

and here is the Connect Function

Function Connect()
On Error Resume Next
 
If cn.State = adStateClosed Then
    cn.CursorLocation = adUseClient
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _ 
                "Source="some_db_file.mdb;Persist Security Info=False;" & _
                "Jet OLEDB:Database Password=password"
End If
 
End Function

[modpluz]

try this,

sSQL = "UPDATE Table " & table_name & " SET " & new_name & "=" & old_name & _
            " WHERE " & id_col & "=" & rsFld(id_col

you mind explaining what you're trying to do with your code above(because its an extract from what i posted earlier) or maybe perhaps you're refering to someone else

[danasegarane]

Sorry Yar,
I think i have misread the post.Any way wht is the error messge you are gettign?