Using variables in INSERT INTO statement

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • franc sutherland
    #1

    Using variables in INSERT INTO statement

    Hello,

    I am using Access 2003.

    Is it possible to use string variables in the INSERT INTO statement?

    I am using the INSERT INTO statement to add a long list of contacts to
    a group by looping through the recordset (based on a linked
    spreadsheet, 'tbl_group_impo rt') and using the INSERT INTO statement
    on each loop. I am using the rst.Fields.Item ("FieldName" ) method to
    assign a value to the string variables. However, when I run the code,
    it prompts me for the variable values.

    I am executing the SQL using DoCmd.RunSQL.

    I would really appreciate any assistance.

    Thanks,

    Franc.

    Here is the code;

    Dim contact As Recordset

    Dim title As String
    Dim first_name As String
    Dim insert_sql As String
    Dim group_id As Integer

    group_id = Forms![frm_group_view]![group_id]

    Set contact = CurrentDb.OpenR ecordset("tbl_g roup_import")

    contact.MoveFir st

    Do Until contact.EOF

    title = contact.Fields. Item("contact_t itle")
    first_name = contact.Fields. Item("contact_f irst_name")

    insert_sql = "INSERT INTO
    qry_group_membe r(group_member_ group_id,contac t_title,contact _first_name)"
    & _
    "VALUES (group_id,title ,first_name)"

    DoCmd.RunSQL insert_sql

    contact.MoveNex t

    Loop
    Tags: None
    • paii, Ron
      #2
      Re: Using variables in INSERT INTO statement


      "franc sutherland" <franc.sutherla nd@googlemail.c omwrote in message
      news:26ca613c-aa5c-474d-bcde-6cb89c8ba5f0@k3 0g2000hse.googl egroups.com...
      Hello,
      >
      I am using Access 2003.
      >
      Is it possible to use string variables in the INSERT INTO statement?
      >
      I am using the INSERT INTO statement to add a long list of contacts to
      a group by looping through the recordset (based on a linked
      spreadsheet, 'tbl_group_impo rt') and using the INSERT INTO statement
      on each loop. I am using the rst.Fields.Item ("FieldName" ) method to
      assign a value to the string variables. However, when I run the code,
      it prompts me for the variable values.
      >
      I am executing the SQL using DoCmd.RunSQL.
      >
      I would really appreciate any assistance.
      >
      Thanks,
      >
      Franc.
      >
      Here is the code;
      >
      Dim contact As Recordset
      >
      Dim title As String
      Dim first_name As String
      Dim insert_sql As String
      Dim group_id As Integer
      >
      group_id = Forms![frm_group_view]![group_id]
      >
      Set contact = CurrentDb.OpenR ecordset("tbl_g roup_import")
      >
      contact.MoveFir st
      >
      Do Until contact.EOF
      >
      title = contact.Fields. Item("contact_t itle")
      first_name = contact.Fields. Item("contact_f irst_name")
      >
      insert_sql = "INSERT INTO
      qry_group_membe r(group_member_ group_id,contac t_title,contact _first_name)"
      & _
      "VALUES (group_id,title ,first_name)"
      >
      DoCmd.RunSQL insert_sql
      >
      contact.MoveNex t
      >
      Loop
      With your current code you will need to pass the values to the query.
      Assuming there are no special characters in title or first_name.

      "VALUES(" & group_id & ",""" & title & """,""" & first_name & """)"

      Other choices
      1) Save your query, converting the variables to parameters. Then pass the
      values though the parameters.
      2) Link to or import the spreadsheet and modify the query to append all the
      records at once.
      3) Open a recordset to your table to insert the records.


        Comment

        • franc sutherland
          #3
          Re: Using variables in INSERT INTO statement

          On Sep 23, 1:18 pm, "paii, Ron" <n...@no.comwro te:
          "franc sutherland" <franc.sutherl. ..@googlemail.c omwrote in message
          >
          news:26ca613c-aa5c-474d-bcde-6cb89c8ba5f0@k3 0g2000hse.googl egroups.com...
          >
          >
          >
          Hello,
          >
          I am using Access 2003.
          >
          Is it possible to use string variables in the INSERT INTO statement?
          >
          I am using the INSERT INTO statement to add a long list of contacts to
          a group by looping through the recordset (based on a linked
          spreadsheet, 'tbl_group_impo rt') and using the INSERT INTO statement
          on each loop.  I am using the rst.Fields.Item ("FieldName" ) method to
          assign a value to the string variables.  However, when I run the code,
          it prompts me for the variable values.
          >
          I am executing the SQL using DoCmd.RunSQL.
          >
          I would really appreciate any assistance.
          >
          Thanks,
          >
          Franc.
          >
          Here is the code;
          >
          Dim contact As Recordset
          >
              Dim title As String
              Dim first_name As String
              Dim insert_sql As String
              Dim group_id As Integer
          >
              group_id = Forms![frm_group_view]![group_id]
          >
              Set contact = CurrentDb.OpenR ecordset("tbl_g roup_import")
          >
              contact.MoveFir st
          >
                  Do Until contact.EOF
          >
                      title = contact.Fields. Item("contact_t itle")
                      first_name = contact.Fields. Item("contact_f irst_name")
          >
                      insert_sql = "INSERT INTO
          qry_group_membe r(group_member_ group_id,contac t_title,contact _first_name)"
          & _
                      "VALUES (group_id,title ,first_name)"
          >
                      DoCmd.RunSQL insert_sql
          >
                      contact.MoveNex t
          >
                  Loop
          >
          With your current code you will need to pass the values to the query.
          Assuming there are no special characters in title or first_name.
          >
          "VALUES(" & group_id & ",""" & title & """,""" & first_name & """)"
          >
          Other choices
          1) Save your query, converting the variables to parameters. Then pass the
          values though the parameters.
          2) Link to or import the spreadsheet and modify the query to append all the
          records at once.
          3) Open a recordset to your table to insert the records.
          Thanks Ron,
          That's sorted it.
          All the best,
          Franc.

            Comment

            Previous template Next
            Working...