By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

DoCmd Alter Add Column Questions

P: 22
Below is my code for my program. I am stuck on the bottom code (SUB COMMAND 35) where I am trying to add a column to a table. The name of the column is REPO_NUM. I am getting an error code 'SYNTAX ERROR'. I would also like to add another InputBox and ask for a value that would be inserted in the column stating REPO_NUM. FYI, I am new at VB and SQL and Stewart Ross punched up Sub_Command 28.
Thanks,
Carl
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private Sub Command7_Click()
  3.    DoCmd.RunCommand acCmdImportAttachExcel
  4. End Sub
  5. Private Sub Command28_Click()
  6.    Dim StrSql As String
  7.    Dim tblname As String
  8.    MyValue1 = InputBox("Enter File Number", "MyInputbox")
  9.    tblname = "Sheet" & MyValue1
  10.    StrSql = "DELETE * FROM [" & tblname & "] WHERE Physician_Comment <>'y'"
  11.    DoCmd.RunSQL StrSql
  12. End Sub
  13. Private Sub Command35_Click()
  14.    Dim StrSql As String
  15.    Dim tblname As String
  16.    MyValue = InputBox("Enter Report Number Column", "MyInputbox")
  17.    tblname = "Sheet" & MyValue1
  18.    StrSql = "ALTER [" & tblname & "] ADD COLUMN REPO_NUM"
  19.    DoCmd.RunSQL StrSql
  20. End Sub
Mar 27 '12 #1

✓ answered by Stewart Ross

Hi Carl. The syntax for the ALTER statement can be checked in Access by typing ALTER into the Help search box. If you do so you will see that you are missing two things: the keyword TABLE after the ALTER, and the type of the field REPO_NUM. If I guess that it is a whole number represented by a long integer value, line 18 above should be

Expand|Select|Wrap|Line Numbers
  1. StrSql = "ALTER TABLE [" & tblname & "] ADD COLUMN REPO_NUM LONG"
If in fact REPO_NUM is a text value then change LONG to something more appropriate, like CHAR(20) for example (representing a character sequence of up to 20 characters).

Be aware that you cannot execute this on the same table more than once - a run-time error would occur if you attempt to add a field with a duplicate name.

-Stewart

Share this Question
Share on Google+
5 Replies


Expert Mod 2.5K+
P: 2,545
Hi Carl. The syntax for the ALTER statement can be checked in Access by typing ALTER into the Help search box. If you do so you will see that you are missing two things: the keyword TABLE after the ALTER, and the type of the field REPO_NUM. If I guess that it is a whole number represented by a long integer value, line 18 above should be

Expand|Select|Wrap|Line Numbers
  1. StrSql = "ALTER TABLE [" & tblname & "] ADD COLUMN REPO_NUM LONG"
If in fact REPO_NUM is a text value then change LONG to something more appropriate, like CHAR(20) for example (representing a character sequence of up to 20 characters).

Be aware that you cannot execute this on the same table more than once - a run-time error would occur if you attempt to add a field with a duplicate name.

-Stewart
Mar 27 '12 #2

P: 22
Command35 subroutine has been modified per your suggestion and now works and reads:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command35_Click()
  2.    Dim StrSql As String
  3.    Dim tblname As String
  4.    MyValue = InputBox("Add Column to Table--Enter Report Number that will be inserted in table", "MyInputbox")
  5.    tblname = "Sheet" & MyValue
  6.    StrSql = "ALTER TABLE [" & tblname & "] ADD COLUMN REPO_NUM LONG"
  7.    DoCmd.RunSQL StrSql
  8. End Sub
I looked up the help file-and saw the INSERT statement and I would like to know how to use the sql statement below instead of the 6th line above.
Expand|Select|Wrap|Line Numbers
  1. StrSql = "ALTER  [" & tblname & "] ADD COLUMN REPO_NUM" & "INSERT INTO [" & tblname & "] REPO_NUM 'MyValue'"
Thanks in advance,
Carl
Mar 27 '12 #3

Expert Mod 2.5K+
P: 2,545
I suspect you are confusing an Update action with an Insert. Insert is intended to add one or more new rows, update is intended to update field values in existing rows. It is of the form

Expand|Select|Wrap|Line Numbers
  1. UPDATE [yourtable]SET [yourfield]=somevalue WHERE somecondition
-Stewart
Mar 28 '12 #4

P: 22
YIPEE! Once again, big thanks. Below is the working code for the UPDATE.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command35_Click()
  2.    Dim StrSql As String
  3.    Dim tblname As String
  4.    Dim MyValue As Integer
  5.    MyValue = InputBox("Add Column to Table--Enter Report Number that will be inserted in table", "MyInputbox")
  6.    tblname = "Sheet" & MyValue
  7.    StrSql = "ALTER TABLE [" & tblname & "] ADD COLUMN REPO_NUM LONG"
  8.    StrSql1 = "UPDATE [" & tblname & "] SET REPO_NUM = MyValue"
  9.    DoCmd.RunSQL StrSql
  10.    DoCmd.RunSQL StrSql1
  11. End Sub
  12.  
Thanks again,
Carl
Mar 28 '12 #5

Expert Mod 2.5K+
P: 2,545
Hi Carl. If you want the field to be updated to the value of variable MyValue line 8 should actually be

Expand|Select|Wrap|Line Numbers
  1. StrSql1 = "UPDATE [" & tblname & "] SET REPO_NUM = " & MyValue
-Stewart
Mar 28 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.