Query SQL Server On Entry

[localhost]

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.

 

[DownThePaint]

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,

 

[localhost]

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,

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("D31000")) 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