ALTER COLUMN parameters via code

[kimkamp]

Hi - first post!

I am using vb.net to work with an MS Access database. I want to programatically change a parameter of a particular field, such as the "Format" selection in an MS Access "Date/Time" field.

I'd like to be able to change the Format of a Date/Time field to a "Short Date" - for example:

str = "ALTER TABLE ContactData ALTER COLUMN [ADVdate] Format: 'Short Date' NOT NULL"

How do I know what choices there for adding parameters to an ALTER COLUMN string?

Is there a list somewhere of all the Parameters I can use after the "ALTER COLUMN [fieldname]..." expression? If so, where would I look?

Thanks for your reply.

Kim

 

[boblarson]

First, and I apologize if this seems a little off your original topic, but why would you need to do this programmatically?

Second, properties like that are not available via SQL DDL. You would need to use DAO to take care of it. But the reason why we ask the "why" question is that this type of question usually means that best practices are not being followed and that can lead to more pain and suffering in the long run. So, that is the reason for that question.

 

[kimkamp]

Thanks for the quick reply.

The main reason I want to do this is because I want to have a Date field use the "Short Date" setting, so that I do not have a Date/Time field that includes the time.

My application will create the necessary Table in Access, but when I create Date fields it always ends up as a Date/Time field.

Do you have any suggestions on a better way?

 

[boblarson]

Well, for one - having to have code to create a table in Access (with RARE EXCEPTION) is a symptom of poor design. So we come back to the larger question, why do you have code to create a table in Access? A properly designed database would RARELY need an additional table created and to have code to create the table is a bit questionable. I will admit that there could be exceptions but I've not seen many who can tell me what they are doing it for and rationalize the table creation via code.

So, like I said, you could do it using DAO but I would be interested to know why your application is creating the table in the first place. Is this application a separate one from the place that the table is going? Also, you can create a table manually and insert it into another database using the TransferDatabase method.

So, I hope you aren't being offended by the questions (I know many are) but we don't just give blind answers here (at least those of us who have been here quite a while and who are professional programmers) as we want to ensure that you get the BEST solution, not necessarily the EXPEDIENT one and that may involve going down another path than you originally were going down.