Query from ms sql server question

[canix]

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 worksheet, 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 sheet.
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 worksheet vba screen.

Thanks!

 

[Jake Marx]

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]

Thanks, I'll try it

 

[canix]

I tried it but got error at .CommandText ....Please help....

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 * " & _ I got
problem when it get to here"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

 

[Jake Marx]

Hi canix,

CommandType and CommandText should have dots (.) in front of them, since
you're inside the With block. See if that fixes the problem.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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