Macro Insert New Field (Date datatype)

[CaptainCrazyHat]

Greetings!

I currently have a table named 2008-01-30. I would like to insert a new field, and I would like that field to be in a Date format and have it populated with the table name. I'll be using this macro on multiple tables.

I don't know how to pull from the table name, so I thought I'd try just getting it to insert the column/field and a date, hence the value 3/14/08. It doesn't work.. I just get a lot of runtime errors about syntax, but it looks right to me?

Here's what I have:

 
DoCmd.RunSQL "ALTER TABLE 2008-01-30 ADD COLUMN (Date)"
DoCmd.RunSQL "INSERT INTO 2008-01-30 ([Date]) VALUES (#03/14/2008#)"

As you can see, I'm trying to do this using SQL in VBA, but I'm open to other suggestions, as I have no idea what I'm doing here!

Also, I'm using XP Pro and Access 2002, FYI.

Thanks in advance! I really appreciate it!!

[FishVal]

Add date field to a table [2008-01-30]?
Would you like Database Normalization and Table structures article ?

Kind regards,
Fish

[CaptainCrazyHat]

Hi Fish,

Thank you for replying! Unfortunately, I don't think the article you provided will help me. I need help with writing the macro, including the VBA and, if necessary, SQL.

Any VBA pros out there? :)

Add date field to a table [2008-01-30]?
Would you like Database Normalization and Table structures article ?

Kind regards,
Fish

[FishVal]

Ok. It will be more clear with an example. Would you describe what your database is expected to do and what you've done already to achieve this?

Kind regards,
Fish

[CaptainCrazyHat]

Ok, no problem. I have two fields right now, employee name and employee number. I have many tables, each named with a date (example: 2008-01-30). I want to write a macro that will insert a new field in each table. This field will be for the date, and pull the date from the table name. As I noted in my first post, here is what I have tried:

      DoCmd.RunSQL "ALTER TABLE 2008-01-30 ADD COLUMN (Date)"
      DoCmd.RunSQL "INSERT INTO 2008-01-30 ([Date]) VALUES (#03/14/2008#)"

...but it doesn't work. Although it looks like it should to me. What am I missing?

As I also said in my first post, I don't know how to pull the date from the table name, so I was trying to get it to put in any date, so I used 3/14/08 as my test.

Thanks in advance!

[NeoPa]

It's a shame you didn't pick up on the main point that was being made (politely) :
"Don't do it this way!
This type of database design is utterly wrong and one of the side-effects of this is that you will create problems for yourself."

The fact that this question is even posted is an illustration of how this design has already caused you problems you shouldn't need to have to worry about.

[CaptainCrazyHat]

I'm sorry, but I'm very new to this and am just trying to pick this up. I'm open to all suggestions.

It's a shame you didn't pick up on the main point that was being made (politely) :
"Don't do it this way!
This type of database design is utterly wrong and one of the side-effects of this is that you will create problems for yourself."

The fact that this question is even posted is an illustration of how this design has already caused you problems you shouldn't need to have to worry about.

[NeoPa]

Bearing in mind we don't know very much about your database as a whole and the reasons for it, the best advice we can really give is to look at the article linked earlier about normalisation.

Microsoft have tried to sell the story that anyone can pick up Access and run with it, regardless of how little understanding they have of database concepts and structure. We have living proof in many, many threads that this is simply not the case. Unless the overview of the project is well structured (and typically conforms to the "rules" laid out in the normalisation article) then it will get progressively more difficult to understand and develop in.

Sometimes, helping someone directly with a problem when you know that they're going about things the wrong way, just leads them further into the mire before they eventually realise they need to stop and start again.

You need to ask yourself :
What exactly am I trying to achieve?
How do I envisage getting there?
Will a database system like Access fit into the plan neatly?

It is beyond the practical scope of a forum such as this, but maybe consider some introductory course on Access too - one which includes the database fundamentals. Coding is easy to pick up and is not what I'm banging on about anyway. It's the structure issues which are the tough one for those new to it to get to grips with.

PS. I hope you understand this is not about criticising you. I'm trying to be constructive.

[CaptainCrazyHat]

I appreciate your suggestions and I'll look into this further. Thanks