Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

changing the column name error 3293

Posted on 2006-05-01
8
Medium Priority
?
1,782 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:spacetraveller
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 16581083
Hi spacetraveller,
Look in on-line Help

ALTER TABLE MyTable
  ALTER COLUMN myfield COUNTER
  CONSTRAINT PrimaryKey PRIMARY KEY

Pete
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16581089
spacetraveller,
sorry - ignore that I thought you were doing something else


Pete
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 675 total points
ID: 16581128
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
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 77

Expert Comment

by:peter57r
ID: 16581173
or in DAO ....

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

Pete
0
 
LVL 4

Accepted Solution

by:
paulgrunner earned 675 total points
ID: 16581185
What about using:
tblImportedFATS.Fields("F1").Name ="ID"

instead of the SQL command?
0
 
LVL 8

Assisted Solution

by:Pigster14
Pigster14 earned 150 total points
ID: 16581247
Enclose your field name with [].

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

Thanks.
0
 

Author Comment

by:spacetraveller
ID: 16581465
Thanks Pete, paul and pigster14. I will try your solutions and get back to you.

Thanks,
st

0
 

Author Comment

by:spacetraveller
ID: 16597434
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 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