Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add Colums to MS Access table (English/Access/2000)

    Hi all,

    I'm pretty new to VB so my appologies for any glaringly obvious questions [img]/forums/images/smilies/wink.gif[/img] I'm trying to write a module for an Access report I'm doing. The first thing I need to do is to build a table. I've created the table manually and it has one field - "employee_number". A query then populates this field with employee numbers (bet you couldn't have guessed that! [img]/forums/images/smilies/wink.gif[/img]). The next thing I need to do is read in a list of values from another table and create a new field (column) in this table for each value. For example, if my first value I read is "0001", I want to create a new field in the table containing "employee_number" called "0001". I've tried to look for a VB equivalent of the "ALTER TABLE" command in SQL but can't find anything in the help - do I simply use SQL straight in my VB code? (like I said - VB newbie here [img]/forums/images/smilies/wink.gif[/img]).

    Thanks in advance,
    Chris.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Add Colums to MS Access table (English/Access/2000)

    Hi Chris,
    First of all, welcome to Woody's Lounge - we try to be the friendliest, most helpful place on the web for questions like yours. Also, note that I moved your question to the Access forum, operating under the presumption you are working in Access and VBA, rather than using VB and just the Jet engine. If I'm wrong, please let me know and we can move it back.

    Assuming you are doing this in Access, you can add the table field using the TableDef collection and modifying it's properties to add the new field, and then set the field properties. You can read more about this kind of stuff in the Help file by looking at the Object Model for Access, and also by looking at the properties information in the VBA help. To get to that you need to open a module in design mode and then click Help.

    Now for the $64 question - why don't you simply create the field at the time you create your table? The only reason I can think of for doing it programatically is that the name needs to be dependent on something in the data, but I would certainly advise against that in most situations. If you are creating the table using a make table query, you can still do it by simply creating an expression that ends up either being blank if you want a text field, or 0 if you want a numeric field. Hope this helps - let us know how you get on.
    Wendell

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Colums to MS Access table (English/Access/

    Hi,

    Thanks for the firiendly welcome [img]/forums/images/smilies/smile.gif[/img] Yes, it's fine to have moved the post here.

    The reason I don't want to create the table from scratch is that the fields may change depending on data in another table. It's not the most ideal situation but in the long run I think it'll be the easiest when I come to do the report that the table data will be based on.

    I'll check out the TableDef collection tomorrow at work.

    Thanks for the pointers [img]/forums/images/smilies/smile.gif[/img]

    Chris.

  4. #4
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Colums to MS Access table (English/Access/

    Hi again,

    Thanks for the TableDef pointers - that worked a treat. I managed to create a function that built the table and added all the columns based on data in another table. I'm now trying to populate the table and am running into further problems. I'm trying to colate data from another table into this one. Basically, I look at the "employee_number" field of my input table and then move down to the corresponding field in my output table (that's the one I've created in my code).

    I'm getting an error: "employee number is not an index in this table" (see attached) when I try to search for the information like this:

    '** Find the employee number in the output table **
    rstOutput.Index = "employee_number"
    rstOutput.Seek "=", rstInput!employee_number

    The error occurs on the "rstOutput.Index = "employee_number"" line when I go to debug.

    Any ideas? I can post more information if needed. I've nearly finished the whole funtion (if it works! ;o)) so I can post it if need be.

    Thanks in advance,
    Chris [img]/forums/images/smilies/ohmy.gif[/img])
    Attached Images Attached Images

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add Colums to MS Access table (English/Access/

    Try replacing the two lines

    rstOutput.Index = "employee_number"
    rstOutput.Seek "=", rstInput!employee_number

    by

    rstOutput.FindFirst "employee_number = " & rstInput!employee_number

  6. #6
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Colums to MS Access table (English/Access/

    Hi Hans,

    Thanks for the info. I tried in the code and got the error (attached): "operation is not supported for this type of object"

    Thanks,
    Rgds,
    Chris.
    Attached Images Attached Images

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add Colums to MS Access table (English/Access/

    Somewhat earlier in your code, you open rstOutput using OpenRecordset. Try opening it as a dynaset:

    Set rstOutput = CurrentDb.OpenRecordset("...", dbOpenDynaset)

  8. #8
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Colums to MS Access table (English/Access/

    I tried that and now I get the attached error. Both of the employee_number fields in the Input and Output table are text if that helps?

    Thanks,
    Chris [img]/forums/images/smilies/smile.gif[/img]
    Attached Images Attached Images

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add Colums to MS Access table (English/Access/

    That's what you get for having misleading field names. Use

    rstOutput.FindFirst "employee_number = " & Chr(34) & rstInput!employee_number & Chr(34)

    The Chr(34)'s put double quotes around the value; this is required for text values.

  10. #10
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Colums to MS Access table (English/Access/

    Nice one! That worked like a charm - thanks [img]/forums/images/smilies/smile.gif[/img] I have one (hopefully) final question that will make the code complete. I've tested it using static values so I know it works, the final thing I need to do is this:

    In my code I have a variable (string) that gets a skill code from the Input table like so:

    strSCode = rstInput!skill_code

    This is part of a loop that adds the value "1" to a column. The column it adds it to relates to the skill code read in by the strSCode variable. So, the table is updated like so:

    rstOutput.Edit

    rstOutput!strSCode = 1

    rstOutput.Update

    This doesn't work though because Access looks for a field called "strSCode" rather than replacing "strSCode" with the skill code. How would I use a variable in the above case? (the skill codes are like "0001", "0002" etc....).

    Thanks again,
    Chris.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add Colums to MS Access table (English/Access/

    Replace rstOutput!strSCode = 1 by rstOutput.Fields(strSCode) = 1 or by the shortened form rstOutput(strSCode) = 1.

  12. #12
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Colums to MS Access table (English/Access/

    Thanks very much for your help [img]/forums/images/smilies/smile.gif[/img] that's sorted now and works fine.

    Thanks again,
    Rgds,
    Chris.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •