472,778 Members | 2,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,778 software developers and data experts.

DoCmd Alter Add Column Questions

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

5 5662
Stewart Ross
2,545 Expert Mod 2GB
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
Carl23
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
Stewart Ross
2,545 Expert Mod 2GB
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
Carl23
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
Stewart Ross
2,545 Expert Mod 2GB
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

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

Similar topics

1
by: luc h | last post by:
Hello, Easy one for the SQL experts. I have a simple table. For the example let's say it looks like this: CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL) Now I want to alter...
2
by: me | last post by:
I would like to add an Identity to an existing column in a table using a stored procedure then add records to the table and then remove the identity after the records have been added or something...
3
by: Metal Dave | last post by:
Hello, A script we run against the database as part of the upgrade of our product is failing with the following message: ALTER TABLE ALTER COLUMN EncodedID failed because STATISTICS hind_61_3...
2
by: sdowney717 | last post by:
I have a table with a column defined thus: LOCNumber Varchar(100) in a table called Bookdata. If I execute ALTER TABLE BookData ALTER COLUMN LOCNumber varchar(100) on the table, does any work...
10
by: Jane | last post by:
Does any one know why this statement is failing? db2 => ALTER TABLE ELMT_T ALTER COLUMN CDTY_CD SET DATA TYPE VARCHAR(51) DB21034E The command was processed as an SQL statement because it was...
3
by: Darin | last post by:
I have many columns in different tables set to money. I want to change those to be decimal(18,2). I do the following: ALTER TABLE AROpenItem ALTER COLUMN copn_taxamt decimal(19,2) I get an...
4
by: Jeff Kish | last post by:
Hi. I have a database I need to supply something (I'm assuming a t-sql script.. maybe something else is better) to update customer tables with. The operations include mostly changing varchar...
3
by: Kimi | last post by:
Hi, I'm trying to change a column on a DB2 table so that it has a default value but I cannot get it to work. Does anyone have any experience in this area. I'm trying with this statement: ...
3
by: Gregor Kovańć | last post by:
Hi! I have a table with a column of BIGINT and I want to change the type to SMALLINT. Command ALTER TABLE TABLE1 ALTER COLUMN COL1 SET DATA TYPE SMALLINT does not work since I cannot specify...
3
by: sparks | last post by:
several months ago I was trying to set the field size of text fields in a table and Lyle Fairfield was nice enought to post a way using alter column. it was surprising since it was ALTER COLUMN...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.