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!


--
canix
------------------------------------------------------------------------
canix's Profile: http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=394289

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


--
canix
------------------------------------------------------------------------
canix's Profile: http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=394289

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]


canix wrote:
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