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

Add 2 fields in an Access table

Nathan H
100+
P: 104
I need to be able to add two fields into an external access database table. This is a one time function, and the values in the fields are not calculation.

In creating a database for multiple users, two fields were left out of one underlying table. I need to be able to do this while preserving the data in the existing table.

Doing a transferdatabase method with the corrected table wipes out the data.

Any ideas? This will not be an end user function, this is a one time fix so please don't blast me with normalization criticism or what not :)
Jan 24 '08 #1
Share this Question
Share on Google+
7 Replies


Nathan H
100+
P: 104
sending up the chain again
Jan 25 '08 #2

Nathan H
100+
P: 104
anybody got any ideas at all?
Jan 25 '08 #3

Nathan H
100+
P: 104
Can anyone offer any guidance?
Jan 28 '08 #4

Jim Doherty
Expert 100+
P: 897
Can anyone offer any guidance?
Expand|Select|Wrap|Line Numbers
  1.  
  2. Sub AlterMyTable()
  3.  
  4. Dim dbs As Database
  5.  
  6. ' Modify this line to include the path to the database.
  7. Set dbs = OpenDatabase("The complete path to your external database")
  8.  
  9. ' Add a Salary field to the table 
  10. ' and make it a Currency data type.
  11. dbs.Execute "ALTER TABLE YourTableName " _
  12. & "ADD COLUMN Salary CURRENCY;"
  13.  
  14. dbs.Close
  15.  
  16. End Sub
  17.  
  18.  
Jim :)
Jan 28 '08 #5

Nathan H
100+
P: 104
Expand|Select|Wrap|Line Numbers
  1.  
  2. Sub AlterMyTable()
  3.  
  4. Dim dbs As Database
  5.  
  6. ' Modify this line to include the path to the database.
  7. Set dbs = OpenDatabase("The complete path to your external database")
  8.  
  9. ' Add a Salary field to the table 
  10. ' and make it a Currency data type.
  11. dbs.Execute "ALTER TABLE YourTableName " _
  12. & "ADD COLUMN Salary CURRENCY;"
  13.  
  14. dbs.Close
  15.  
  16. End Sub
  17.  
  18.  
Jim :)

Jim,

Great...works on a test I just did. Thanks a lot!

Nathan
Jan 28 '08 #6

Nathan H
100+
P: 104
Jim,

Great...works on a test I just did. Thanks a lot!

Nathan

Jim,

Can I trouble you for one more thing. Is it possible to add an if then option to your code? If the field does not exist, then add it?

Nathan
Jan 28 '08 #7

Jim Doherty
Expert 100+
P: 897
Jim,

Can I trouble you for one more thing. Is it possible to add an if then option to your code? If the field does not exist, then add it?

Nathan
Sure...you can be quick and dirty about this if you want to by simply adding an......

On error Resume Next

statement immediately before the dbs.execute line. Because you cannot add the same field twice this will 'force an error' which will be skipped over to the next line where it simply closes the database. You can be relaxed about that quick method unless there is any particular reason for you not to be so.

Alternatively you can loop through the fields collection for the table testing for the fieldname and if it does not exist then invoke the code that adds the field at that point depends how pure you wish to be.

One...... On Error Resume next line.... is cheaper than the field name coding wise. but rather than me code this up reinventing the wheel here have a look at a similar thread coding answer provided by HighTechCoach it has the necessary ingredients you need if you study it

Add new field in the table using macro

Regards

Jim :)
Jan 28 '08 #8

Post your reply

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