Query SQL Server On Entry

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

localhost - 13 Mar 2008 22:42 GMT

Please tell me if this is possible... A cell receives an ID number
entered by the user. Upon update of the cell, I'd like to query a view
in a SQL server database. If the entered ID exists, a value (say "X")
is placed in another cell, else, a different value (say "Y") is placed
in another cell.

Where should I start when developing interactive MSSQL queries from
Excel?

MS Excel 2002 SP3.

Thanks in advance.

Reply to this Message

DownThePaint - 14 Mar 2008 02:56 GMT

Hi LocalHost;

You can definitely do that, typically you would use the the code somehting
like:

varMyId = Inputbox("What is your Id")
If not varMyId = "" Then
'Go do the SQL thing here
End If

The SQL stuff is fairly advanced. You need to try and do a DSN-Less
connection using ADO. You can find more out about how to do this at
ASP101.Com and I'm sure a thousand other places. ASP101 is pretty simple and
straight forward. Go to their Samples page.

I hope it helps,

> Please tell me if this is possible... A cell receives an ID number
> entered by the user. Upon update of the cell, I'd like to query a view
[quoted text clipped - 8 lines]
>
> Thanks in advance.

Reply to this Message

localhost - 17 Mar 2008 23:00 GMT

On Mar 13, 6:56 pm, DownThePaint
<DownThePa...@discussions.microsoft.com> wrote:

> Hi LocalHost;
>
[quoted text clipped - 25 lines]
>
> > Thanks in advance.

Thank you. I will look into ASP101.com.

I've managed to implement a solution, though it will probably need
polishing after reading some more. Below is the code...

Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D3:D1000")) Is Nothing Then
Exit Sub
Else
Dim cn As ADODB.Connection
Dim cd As ADODB.Command
Dim rs As ADODB.Recordset

Target.Offset(0, 1).Clear

Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=sqloledb;" & _
"Data Source=MYSERVER;" & _
"Initial Catalog=MYDATABASE;" & _
"User Id=MYRESTRICTEDUSER;" & _
"Password=MYPASSWORD"
.CursorLocation = adUseClient
.Open
End With
Set cd = New ADODB.Command
With cd
Set .ActiveConnection = cn
.CommandType = adCmdText
.CommandText = "SELECT STATUS " & _
"FROM STATUSES " & _
"WHERE ID = '" & Range(Target.Address).Value & "'"
Set rs = .Execute
End With
Target.Offset(0, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cd = Nothing
cn.Close
Set cn = Nothing
End If

End Sub

Reply to this Message