If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Getting error with table update
db.Execute "ALTER TABLE [" & vTableName & "] ALTER COLUMN [" & vFieldName &
"] TEXT (" & vParameters & ")" 'change field size where vParameters = 10 Gives Run-time error '3293' Syntax error in ALTER TABLE statement The field is created with field size =255 but not 10 |
#2
|
|||
|
|||
Getting error with table update
Jason,
I am using A2K/ WinXP I used your SQL and I did not get an error. I changed the field from 50 to 10, then to 55, then to 35..... no errors. Are you using any special chars in the table name or field name??? Is the column you are trying to alter TEXT?? Try changing your code to: Dim sSQL as string ' this should be one line sSQL = "ALTER TABLE [" & vTableName & "] ALTER COLUMN [" & vFieldName & "] TEXT (" & vParameters & ")" ' find out what is in the string sSQL Debug.Pring sSQL db.Execute sSQL, dbFailOnError Set a breakpoint on the "db.Execute" line and post back with the sSQL results (from the immediate window) HTH -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Jason" wrote: db.Execute "ALTER TABLE [" & vTableName & "] ALTER COLUMN [" & vFieldName & "] TEXT (" & vParameters & ")" 'change field size where vParameters = 10 Gives Run-time error '3293' Syntax error in ALTER TABLE statement The field is created with field size =255 but not 10 |
#3
|
|||
|
|||
Getting error with table update *Access*
No special text just the number 10. No special text in fieldname - just the
letters A to Z What do you mean by Is the column you are trying to alter TEXT?? Text was on one line - OE craps entries made here "Steve Sanford" limbim53 at yahoo dot com wrote in message ... Jason, I am using A2K/ WinXP I used your SQL and I did not get an error. I changed the field from 50 to 10, then to 55, then to 35..... no errors. Are you using any special chars in the table name or field name??? Is the column you are trying to alter TEXT?? Try changing your code to: Dim sSQL as string ' this should be one line sSQL = "ALTER TABLE [" & vTableName & "] ALTER COLUMN [" & vFieldName & "] TEXT (" & vParameters & ")" ' find out what is in the string sSQL Debug.Pring sSQL db.Execute sSQL, dbFailOnError Set a breakpoint on the "db.Execute" line and post back with the sSQL results (from the immediate window) HTH -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Jason" wrote: db.Execute "ALTER TABLE [" & vTableName & "] ALTER COLUMN [" & vFieldName & "] TEXT (" & vParameters & ")" 'change field size where vParameters = 10 Gives Run-time error '3293' Syntax error in ALTER TABLE statement The field is created with field size =255 but not 10 |
#4
|
|||
|
|||
Getting error with table update *Access*
Text is ALTER TABLE [PreviousAddress] ALTER COLUMN [Address1] TEXT (20)
Table = PreviousAddress Fieldname = Address1 Text Field Length = 20 Select Case vPropertyType Case "Text Field Size =" "Steve Sanford" limbim53 at yahoo dot com wrote in message ... Jason, I am using A2K/ WinXP I used your SQL and I did not get an error. I changed the field from 50 to 10, then to 55, then to 35..... no errors. Are you using any special chars in the table name or field name??? Is the column you are trying to alter TEXT?? Try changing your code to: Dim sSQL as string ' this should be one line sSQL = "ALTER TABLE [" & vTableName & "] ALTER COLUMN [" & vFieldName & "] TEXT (" & vParameters & ")" ' find out what is in the string sSQL Debug.Pring sSQL db.Execute sSQL, dbFailOnError Set a breakpoint on the "db.Execute" line and post back with the sSQL results (from the immediate window) HTH -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Jason" wrote: db.Execute "ALTER TABLE [" & vTableName & "] ALTER COLUMN [" & vFieldName & "] TEXT (" & vParameters & ")" 'change field size where vParameters = 10 Gives Run-time error '3293' Syntax error in ALTER TABLE statement The field is created with field size =255 but not 10 |
#5
|
|||
|
|||
Getting error with table update
Jason,
I have also tried it and it works OK (which is not really surprising as it is my code you are using). However, I noticed on one of your other posts that you are using Access 97 and I am wondering if this is the problem. I know that Microsoft made quite a few enhancements to the SQL Jet Engine between versions 97 and 2000 and it may be that the ALTER COLUMN function did not have this facility on the earlier version. Also, looking at my old Access 97 books, I don't see any commands to change the field size. Maybe someone else has more info on the changes made. Is it possible for you to try the code out with Access 2000 (or later) to see if it works then. There is some information on SQL 97 at :- http://www.personal.kent.edu/~gthoma...htm#altertable No mention of changing the field size on an existing field, although you can set the field size when adding a new field to a table. You could delete the field with the DROP command and then add it in again but you would lose any data in the field (which would not be a good idea). Also this site has more information on SQL 2000 which may be useful :- http://www.personal.kent.edu/~gthoma...htm#altertable HTH Peter Hibbs. On Fri, 9 May 2008 20:47:24 +1200, "Jason" wrote: db.Execute "ALTER TABLE [" & vTableName & "] ALTER COLUMN [" & vFieldName & "] TEXT (" & vParameters & ")" 'change field size where vParameters = 10 Gives Run-time error '3293' Syntax error in ALTER TABLE statement The field is created with field size =255 but not 10 |
#6
|
|||
|
|||
Getting error with table update
It is during the add command where it fails. Access 2000 file in Access 2003
works. So How can I add text fields in 97 (That is the run time version I have otherwise I would use 2003). From the form: Action: New Field Table Name: PreviousAddress FieldName: Address3 Field Type: text Property: Text Field Size= Additional Data: 10 "Peter Hibbs" wrote in message ... Jason, I have also tried it and it works OK (which is not really surprising as it is my code you are using). However, I noticed on one of your other posts that you are using Access 97 and I am wondering if this is the problem. I know that Microsoft made quite a few enhancements to the SQL Jet Engine between versions 97 and 2000 and it may be that the ALTER COLUMN function did not have this facility on the earlier version. Also, looking at my old Access 97 books, I don't see any commands to change the field size. Maybe someone else has more info on the changes made. Is it possible for you to try the code out with Access 2000 (or later) to see if it works then. There is some information on SQL 97 at :- http://www.personal.kent.edu/~gthoma...htm#altertable No mention of changing the field size on an existing field, although you can set the field size when adding a new field to a table. You could delete the field with the DROP command and then add it in again but you would lose any data in the field (which would not be a good idea). Also this site has more information on SQL 2000 which may be useful :- http://www.personal.kent.edu/~gthoma...htm#altertable HTH Peter Hibbs. On Fri, 9 May 2008 20:47:24 +1200, "Jason" wrote: db.Execute "ALTER TABLE [" & vTableName & "] ALTER COLUMN [" & vFieldName & "] TEXT (" & vParameters & ")" 'change field size where vParameters = 10 Gives Run-time error '3293' Syntax error in ALTER TABLE statement The field is created with field size =255 but not 10 |
#7
|
|||
|
|||
Getting error with table update
Jason,
Don't know, if it is failing because the Run Time version is Access 97 then I don't see what else you can do (apart from upgrade of course). What I would do is allow the field to be added with a field size of 255 and then change the code in the front end file to limit the number of characters entered in the field to 10. You could do this by setting the Input Mask property on the form or, perhaps, setting a Validation Rule or maybe write some VBA code to limit the input. Peter Hibbs. On Sun, 11 May 2008 08:15:34 +1200, "Jason" wrote: It is during the add command where it fails. Access 2000 file in Access 2003 works. So How can I add text fields in 97 (That is the run time version I have otherwise I would use 2003). From the form: Action: New Field Table Name: PreviousAddress FieldName: Address3 Field Type: text Property: Text Field Size= Additional Data: 10 "Peter Hibbs" wrote in message .. . Jason, I have also tried it and it works OK (which is not really surprising as it is my code you are using). However, I noticed on one of your other posts that you are using Access 97 and I am wondering if this is the problem. I know that Microsoft made quite a few enhancements to the SQL Jet Engine between versions 97 and 2000 and it may be that the ALTER COLUMN function did not have this facility on the earlier version. Also, looking at my old Access 97 books, I don't see any commands to change the field size. Maybe someone else has more info on the changes made. Is it possible for you to try the code out with Access 2000 (or later) to see if it works then. There is some information on SQL 97 at :- http://www.personal.kent.edu/~gthoma...htm#altertable No mention of changing the field size on an existing field, although you can set the field size when adding a new field to a table. You could delete the field with the DROP command and then add it in again but you would lose any data in the field (which would not be a good idea). Also this site has more information on SQL 2000 which may be useful :- http://www.personal.kent.edu/~gthoma...htm#altertable HTH Peter Hibbs. On Fri, 9 May 2008 20:47:24 +1200, "Jason" wrote: db.Execute "ALTER TABLE [" & vTableName & "] ALTER COLUMN [" & vFieldName & "] TEXT (" & vParameters & ")" 'change field size where vParameters = 10 Gives Run-time error '3293' Syntax error in ALTER TABLE statement The field is created with field size =255 but not 10 |
#8
|
|||
|
|||
Getting error with table update
Wouldn't that inflate the file size? I would upgrade but too expensive for
me. "Peter Hibbs" wrote in message ... Jason, Don't know, if it is failing because the Run Time version is Access 97 then I don't see what else you can do (apart from upgrade of course). What I would do is allow the field to be added with a field size of 255 and then change the code in the front end file to limit the number of characters entered in the field to 10. You could do this by setting the Input Mask property on the form or, perhaps, setting a Validation Rule or maybe write some VBA code to limit the input. Peter Hibbs. On Sun, 11 May 2008 08:15:34 +1200, "Jason" wrote: It is during the add command where it fails. Access 2000 file in Access 2003 works. So How can I add text fields in 97 (That is the run time version I have otherwise I would use 2003). From the form: Action: New Field Table Name: PreviousAddress FieldName: Address3 Field Type: text Property: Text Field Size= Additional Data: 10 "Peter Hibbs" wrote in message .. . Jason, I have also tried it and it works OK (which is not really surprising as it is my code you are using). However, I noticed on one of your other posts that you are using Access 97 and I am wondering if this is the problem. I know that Microsoft made quite a few enhancements to the SQL Jet Engine between versions 97 and 2000 and it may be that the ALTER COLUMN function did not have this facility on the earlier version. Also, looking at my old Access 97 books, I don't see any commands to change the field size. Maybe someone else has more info on the changes made. Is it possible for you to try the code out with Access 2000 (or later) to see if it works then. There is some information on SQL 97 at :- http://www.personal.kent.edu/~gthoma...htm#altertable No mention of changing the field size on an existing field, although you can set the field size when adding a new field to a table. You could delete the field with the DROP command and then add it in again but you would lose any data in the field (which would not be a good idea). Also this site has more information on SQL 2000 which may be useful :- http://www.personal.kent.edu/~gthoma...htm#altertable HTH Peter Hibbs. On Fri, 9 May 2008 20:47:24 +1200, "Jason" wrote: db.Execute "ALTER TABLE [" & vTableName & "] ALTER COLUMN [" & vFieldName & "] TEXT (" & vParameters & ")" 'change field size where vParameters = 10 Gives Run-time error '3293' Syntax error in ALTER TABLE statement The field is created with field size =255 but not 10 |
#9
|
|||
|
|||
Getting error with table update
On Sun, 11 May 2008 10:54:29 +1200, "Jason" wrote:
Wouldn't that inflate the file size? No. Access does not store trailing blanks. A Text(10) field and a Text(255) field occupy exactly the same space if each contains ten characters. I would upgrade but too expensive for me. A97 can change table definitions - using VBA code; it just doesn't support DDL queries in the same way that later versions do. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|