Results 1 to 1 of 1
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Thanked 0 Times in 0 Posts

    Adding fields to a Made table

    Using Access 2010. I need to combine material from two different types of data.
    First type of information is coming from a table regarding class objectives.
    Second type of information is coming from a table with events for CE classes.
    The user is looking at the class objectives and once approved they would commit to them and a SQL statement is ran creating a new table.
    The user then has the option to pick components that are involved in an all day class. Things like registration, welcome, lunch, and breaks etc.
    The end goal is to develop a schedule for the day's class.
    I want to then run an append query with SQL adding the components to the objectives. But there are fields in the 2nd table that doesn't exist in the 1st SQL table.

    All of the above to ask this question. How do I get the fields into the SQL made table using VBA so that I can then append info from the component tables?

    Thank you for your time to review and considering to answer.

    Further information I am trying the following code, but having trouble with syntax for the strAccColumns line

    Private Sub cmdCommit_Click()
    Dim Message As Long
    Dim strSQL As String
    Dim strAddColumns As Variant
    DoCmd.SetWarnings False

    Message = MsgBox("Are you ready to commit these objectives to the class schedule", vbYesNo, "Commit to Schedule Objectives")
    If Message = vbYes Then

    strSQL = "SELECT tbl_CL_ClassObj.ObjectivesID, tbl_CL_ClassObj.ClassID, tbl_CL_ClassObj.SortOrder, " & _
    "tbl_CL_ClassObj.Objective, tbl_CL_ClassObj.Length, tbl_CL_ClassObj.CountsAsCE, tbl_CL_ClassObj.Day, " & _
    "tbl_CL_ClassObj.Inactivate INTO tbl_CL_MakeSchStep1_ClassObjSchedule" & _
    " FROM tbl_CL_ClassObj " & _
    " WHERE (((tbl_CL_ClassObj.ClassID)=[Forms]![frmMain]![frm_CL_Classes]![txtClassID]) AND ((tbl_CL_ClassObj.Inactivate)=False));"

    strAddColumns = Alter Table "tbl_CL_MakeSchStep1_ClassObjSchedule" Add (ScheduleEventID number, Event text, AlternateTitle Text, AddEvent bit);

    DoCmd.RunSQL strSQL
    DoCmd.RunSQL strAddColumns
    End If

    DoCmd.SetWarnings True

    End Sub

    I hope this helps.

    Got it to work the format for the problem line is as follows:

    strAddColumns = "Alter Table tbl_CL_MakeSchStep1_ClassObjSchedule Add ScheduleEventID number, Event text, AlternateTitle Text, AddEvent bit"

    Thanks to those that reviewed this question. Fay
    Last edited by Fay; 2014-09-23 at 20:37. Reason: Additional available data

Tags for this Thread

Posting Permissions

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