Re: Query from ms sql server question

Hi canix,

Here's some code that should get you started. To run it, you'll have to set a reference to the "Microsoft ActiveX Data Objects Library 2.x" (with x being the highest # you have listed) via Tools | References in the VBE (Visual Basic Editor).

You'll have to modify the ConnectionString property to reflect the name of your server, database, username, and password. You'll have to modify the CommandText property to reflect your table and field names. You'll likely want to add some error handling once you get it working.

Sub GetSQLData()
   Dim cn As ADODB.Connection
   Dim cd As ADODB.Command
   Dim rs As ADODB.Recordset

   Set cn = New ADODB.Connection
   With cn
       .ConnectionString = "Provider=sqloledb;" & _
          "Data Source=myServer;" & _
          "Initial Catalog=myDatabase;" & _
          "User Id=myUsername;" & _
          "Password=myPassword"
       .CursorLocation = adUseClient
       .Open
   End With
   Set cd = New ADODB.Command
   With cd
       Set .ActiveConnection = cn
       .CommandType = adCmdText
       .CommandText = "SELECT * " & _
                      "FROM dbo.mytable m WITH (NOLOCK) " & _
                      "WHERE m.ID=" & Range("A1").Value
       Set rs = .Execute
   End With
   Range("A2").CopyFromRecordset rs
   rs.Close
   Set rs = Nothing
   Set cd = Nothing
   cn.Close
   Set cn = Nothing
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


canix wrote: 
I am new to this. I hope someone here with the expertise can help
creating a syntax to perform a query ms sql server in Excel .  For
example, in my work***, there a cell which user will enter the
production number and macro will perform an automatic connection to
sql server and query the table base on the production number and
retrieve the data base on the production alone and place it on the
excel ***. I don't have background in VB code. So please provide me
with the syntax that I need to put in. Here's what I want:
Step 1:
create a connection to sql server (I have server name and database
name and user ID and password) .
Step 2:
perform sql query statement(which I can look up in the MS query
window) based on whatever production number that use input in cell A1
for example.
Step 3:
return the query result data starting in cell A2
Step 4:
close the connection.


Please give the syntax for every steps and where should I put all the
code in work*** vba screen.

Thanks! 

.