Using variables in INSERT INTO statement

[franc sutherland]

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_import') 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.OpenRecordset("tbl_group_import")

contact.MoveFirst

Do Until contact.EOF

title = contact.Fields.Item("contact_title")
first_name = contact.Fields.Item("contact_first_name")

insert_sql = "INSERT INTO
qry_group_member(group_member_group_id,contact_tit le,contact_first_name)"
& _
"VALUES (group_id,title,first_name)"

DoCmd.RunSQL insert_sql

contact.MoveNext

Loop

[paii, Ron]

"franc sutherland" <fr**************@googlemail.comwrote in message
news:26**********************************@k30g2000 hse.googlegroups.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_import') 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.OpenRecordset("tbl_group_import")

contact.MoveFirst

Do Until contact.EOF

title = contact.Fields.Item("contact_title")
first_name = contact.Fields.Item("contact_first_name")

insert_sql = "INSERT INTO
qry_group_member(group_member_group_id,contact_tit le,contact_first_name)"
& _
"VALUES (group_id,title,first_name)"

DoCmd.RunSQL insert_sql

contact.MoveNext

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.

[franc sutherland]

On Sep 23, 1:18*pm, "paii, Ron" <[e-mail]:

"franc sutherland" <[e-mail] in message

news:26**********************************@k30g2000 hse.googlegroups.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_import') 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.OpenRecordset("tbl_group_import")

* * contact.MoveFirst

* * * * Do Until contact.EOF

* * * * * * title = contact.Fields.Item("contact_title")
* * * * * * first_name = contact.Fields.Item("contact_first_name")

* * * * * * insert_sql = "INSERT INTO
qry_group_member(group_member_group_id,contact_tit le,contact_first_name)"
& _
* * * * * * "VALUES (group_id,title,first_name)"

* * * * * * DoCmd.RunSQL insert_sql

* * * * * * contact.MoveNext

* * * * 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.