Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query SQL Server On Entry
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query SQL Server On Entry
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query SQL Server On Entry
On Mar 13, 6:56 pm, DownThePaint
wrote: 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" wrote: 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. 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sql server password entry during macro run | Excel Discussion (Misc queries) | |||
SQL Server Query to Excel | Excel Programming | |||
Query SQL Server from Excel | Excel Programming | |||
Query Table and SQL Server | Excel Programming | |||
Query SQL Server | Excel Programming |