changing the column name error 3293

hi

I am getting the following error

Runtime error 3293
syntax error  in ALTER TABLE STATEMENT

while implementing

Dim sqlStatement1 As String  ''' declaring the SQl statement string
sqlStatement1 = "ALTER TABLE tblImportedFATS CHANGE F1 ID INTEGER" ''' the reqd SQL sstatement

Dim dbImported As DAO.Database ''' declaring the variable for the DB
Dim tblImportedFATS As DAO.TableDef '''declaring the table
Set dbImported = CurrentDb ''' assinging the
Set tblImportedFATS = dbImported.TableDefs("tblFATS1")
DoCmd.RunSQL sqlStatement1

in the debug mode, I checked the columns of the tblImportedFATS and they (F1, F2, F3,...... ) are all there


Am I am using any SQL keyword not supported by the MS access/ or do I need to add any libraries ???

Please Help

Thanks,
st
spacetravellerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Hi spacetraveller,
Look in on-line Help

ALTER TABLE MyTable
  ALTER COLUMN myfield COUNTER
  CONSTRAINT PrimaryKey PRIMARY KEY

Pete
peter57rCommented:
spacetraveller,
sorry - ignore that I thought you were doing something else


Pete
peter57rCommented:
spacetraveller,
Now that I realise you want to rename a column I can give you tha bad news that you can't do that on JET sql.

To rename a column using DDL SQL (Data Definition Language) you have to add a column:

ALTER TABLE mytable ADD COLUMN NewName datatype(size)

Then run an update statement to copy the data from the old column to the new one:
UPDATE mytable SET NewName = OldName

Then drop the old column:
ALTER TABLE mytable DROP COLUMN OldName

You can rename in VBA/ADO.

Pete
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

peter57rCommented:
or in DAO ....

CurrentDb().TableDefs("MyTable").Fields("fff").Name = "ggg"

Pete
paulgrunnerCommented:
What about using:
tblImportedFATS.Fields("F1").Name ="ID"

instead of the SQL command?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pigster14IT ConsultantCommented:
Enclose your field name with [].

You have the field name as F1 ID. Make it [F1 ID].

Thanks.
spacetravellerAuthor Commented:
Thanks Pete, paul and pigster14. I will try your solutions and get back to you.

Thanks,
st

spacetravellerAuthor Commented:
Hi,


Paul, I tried the following and it worked.

tblImportedFATS.Fields("F1").Name = "ID" '''this works


Pete, I tried the following,

CurrentDb().TableDefs("tblImportedFATS").Fields("F2").Name = "Rno"
and
CurrentDb.TableDefs("tblImportedFATS").Fields("F2").Name = "Rno"

and I got the following error. Thanks for the information,  that the SQL method does not work here.
otherwise I would have wasted my time trying to trouble shoot.

Run-Time error 3265
Item not found in this collection


Pigster14, I tried [F1 ID ] but I got error

Runtime error 3293

Syntax error in ALTER TABLE statement.

Thanks A Lot
st
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.