changing the column name error 3293

spacetraveller
spacetraveller used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi spacetraveller,
Look in on-line Help

ALTER TABLE MyTable
  ALTER COLUMN myfield COUNTER
  CONSTRAINT PrimaryKey PRIMARY KEY

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


Pete
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

or in DAO ....

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

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

instead of the SQL command?
Pigster14IT Consultant
Commented:
Enclose your field name with [].

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

Thanks.

Author

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

Thanks,
st

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial