Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

passing variables into sql query

Status
Not open for further replies.

CamelBack

IS-IT--Management
Aug 8, 2006
1
US
Let me preface this message by stating that i am a newbie.
Here's my senario and what I would like to do.

I have a table in a SQL database called 'users'. I want to update the a field in 'users' called 'subscription' . I want to do this for multiple users who I have listed in an excel sheet.

Excel sheet has:
[user ID] [FirstName] [LastName]

The subscriptions field has two available values:
' This user is subscribed to newsletter.'
' This user is not subscribed to newsletter.'

This is the query I currently have:

UPDATE Users
SET Subscriptions = ' This user is currently subscribed to newsletter.'
WHERE LastName = 'smith' and FirstName = 'joe'

This works great when I manually type in Joe Smith in my query, but I would like this query to automatically go through the [FirstName] and [LastName] in the Excel sheet and update the table 'users'.

Any help would be greatly apprecieated. Thanks in advance.
 
Do you need to do this once, or on a regular basis?

If once, you could import the excel spreadsheet in to the database and use it to update your existing table.

If you need to do this on a regular basis... well... things get a little more complicated.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 


Hi,

Assuming that your data is CONTIGUOUS and begins in A1
Code:
  dim r as range
  for each r in range([A1], [A1].end(xldown))
    sSQL = "UPDATE Users "
    sSQL = sSQL & "SET Subscriptions = ' This user is currently subscribed to newsletter.' "
    sSQL = sSQL & "WHERE LastName = '" & r.offset(0,2).value & "' and FirstName = '" & r.offset(0,1).value & "'"
     '... use sSQL in your recordset call
  next


Skip,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top