[RESOLVED]create a prompt in Excel and run a sql server query

hi,
I usually run a query against sql server by going to Get external Data --> run database query.

can someone instruct me to how I can embbed a prompt in excel so when someone opens the excel spreadsheet they would get the prompts with paramters that they want to pass to the query?

the prompts must be a dropdown list populated from query then the final step is running last query with the three parameters they selected.

thank you very much for any help.
waely

in the code window:
In "ThisWorkbook"

VB Code:

Private Sub Workbook_Open()
    Project_ID = InputBox("Please enter the Project ID", "Project ID")
    Status = InputBox("Enter the STATUS for Project ID:" & Project_ID, "Status?")
    Manager = InputBox("Enter the MANAGERS name:", "Manager?")
    
End Sub

then just pass the three variable into your query ;)

thanks Static. how can I prepopulate three drop down list with these values? it would nice to dynamic but it doesn't have to. so three dropdown list with my values and after selecting then the parameters will be passed to my query.

thank you

how about... in the Open event...
Create an ADO connection to your DB and run a query

here is a code sample:

VB Code:

Dim CNN As New Connection
 
Private Sub Workbook_Open()
Dim rs As New Recordset
CNN.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\path to database\databasename.mdb"
If CNN.State <> 0 Then
    rs.Open "SELECT PROJECT_ID FROM TABLE1", CNN, adOpenDynamic, adLockOptimistic
    Do While Not rs.EOF
        Sheet1.ComboBox1.AddItem rs!ID
        rs.MoveNext
    Loop
    rs.close
    rs.Open "SELECT STATUS FROM TABLE2", CNN, adOpenDynamic, adLockOptimistic
    Do While Not rs.EOF
        Sheet1.ComboBox2.AddItem rs!ID
        rs.MoveNext
    Loop
    rs.close
    rs.Open "SELECT MANAGER FROM TABLE3", CNN, adOpenDynamic, adLockOptimistic
    Do While Not rs.EOF
        Sheet1.ComboBox3.AddItem rs!ID
        rs.MoveNext
    Loop
    rs.close
End If
set rs = Nothing
CNN.Close
set CNN = Nothing

static - I'm using sql server connection not mdb and it doesn't recognize the connection object. am I missing any libraries?


Dim CNN As ADODB.Connection
Dim rs As ADODB.Recordset
CNN.Open "Driver={SQL Server};Server=servername;Database=databasename;Uid=sa;Pwd=pass"


thank you

see the Connection Strings link in my signature for connection strings to many DBMS's.. including SQL Server.

actually i got the connection from that site. my problem is that connection or recordset objects are not recognized. am I missing a library or a referance?

thank you

Ah right, sorry.. I was reading too quickly!

You need a reference (I think under to "Tools" -> "References") to "Microsoft ActiveX Data Objects X.X".

Thanks Si.. I was at lunch ;) lol

got it Waely?

Static -- hope you had a good lunch.

I get this error : Run-Time '3265' : application-defined or object defined error
-------------------------
Do While Not rs.EOF
Sheet1.OLEObjects("cbox1").AddItem rs!ID ----> ERROR LINE
' OR Sheet1.OLEObjects.cbox1.AddItem rs!ID --still same error

rs.MoveNext
Loop
-----------------------

Sheet1.cbox1.AddItem rs!ID

Quote:

---

Originally Posted by Static

Sheet1.cbox1.AddItem rs!ID

---

another error message : Method or data member not found

my textbox snapshot:

when u added the control.. did u just use the "Control Toolbox" bar or "Forms" bar?

use the Controls Toolbox...

and is it on Sheet1? or did u name the sheet?

thank you static is works now on my mdb but when I switched the connection to sql server I got this error: Automation error upspecified error
on this line
CNN.Open "Driver={SQL Server};Server=serverName;Database=dbname;Uid=user;Pwd=pass;"

do you know what might be the issue.
thanks for your help.
waely

sure do :)

look at that line:

Is the Server really called "ServerName"??
is the database on the server called "dbname"??
user?
pass?
you need to change each of the values (in bold) to suit your needs

VB Code:

CNN.Open "Driver={SQL Server};Server=[B]serverName[/B];Database=[B]dbname[/B];Uid=[B]user[/B];Pwd=[B]pass[/B];"

Quote:

---

Originally Posted by Static

sure do :)

look at that line:

Is the Server really called "ServerName"??
is the database on the server called "dbname"??
user?
pass?
you need to change each of the values (in bold) to suit your needs

VB Code:

CNN.Open "Driver={SQL Server};Server=[B]serverName[/B];Database=[B]dbname[/B];Uid=[B]user[/B];Pwd=[B]pass[/B];"

---

Static - all parameters where changed from their original values to dummy names for security reasons. so the connection looks good?