469,344 Members | 5,350 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

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_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
Sep 23 '08 #1
2 28864

"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.
Sep 23 '08 #2
On Sep 23, 1:18*pm, "paii, Ron" <n...@no.comwrote:
"franc sutherland" <franc.sutherl...@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.
Thanks Ron,
That's sorted it.
All the best,
Franc.
Sep 23 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by newbie_mw | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.