SQL ADD COLUMN works but doesn't display

Quirkey

New member
Local time
Tomorrow, 01:51
Joined
Feb 15, 2011
Messages
4
G'day,
I am using Access 2007. The following code works to create a new column in the table called 'Matrix':

DoCmd.RunSQL "ALTER TABLE Matrix ADD COLUMN [" & newjob & "] INTEGER;"

However, the new column is not visible in the Table 'Matrix', even if I close and reopen the database. Yet I know it is there somewhere because if I try to run it again with the same value for variable 'newjob' I get an error message telling me the column already exists.

Curiously, if I create another new column with a new value for 'newjob', the previously created column will now appear in Table 'Matrix', but the last column created does not display - even in the Design view of the Table.

I figure I need to refresh the Table but am at a loss as to how to do it. Any assistance would be greatly appreciated.

Quirkey
 
I ran you code in Access 2007 and it ran fine. I can't think what could be wrong.

However, I'm slightly worried that you are creating and non-normalised database i.e. you have several columns for jobs.

If you normalise your database then adding another job is simply adding another record in a table.

Chris
 
Thanks for the reply Chris. If you saw my database you would be more than 'slightly worried' about non-normalised data. Absolutely horrified most likely. I am going to go back to the Bible and read up on data normalisation.

I confess I am a database dilletante. In this particular table I have a range of Jobs as Field headings, and a range of Skills as Records. At the intersection of the two, a numeral 1 means a particular skill is mapped to a particular job. It was the only way I could think of doing it.

Anyway, the weird thing about my issue is that my SQL queries are able to retrieve the data from the non-appearing column - it's just that I can't SEE IT until I create a subsequent column. I may have to learn to live with it. Thanks again for the reply.
 

Users who are viewing this thread

Back
Top Bottom