By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Using variables in INSERT INTO statement

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a

"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

P: n/a
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.