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

[waely]

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

[Static]

in the code window:
In "ThisWorkbook"

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

[waely]

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

[Static]

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

[Static]

here is a code sample:

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

[waely]

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

[si_the_geek]

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

[waely]

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

[si_the_geek]

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".

[Static]

Thanks Si.. I was at lunch lol

got it Waely?

[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
-----------------------

[Static]

Sheet1.cbox1.AddItem rs!ID

[waely]

Sheet1.cbox1.AddItem rs!ID

another error message : Method or data member not found

my textbox snapshot:

[Static]

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?

[waely]

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

[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

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

[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

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?