how to query database created by sqlserver?

[xq]

i have connected the database which is created by sql server, and problem is i don't know how to excute sql and retrieve result from it, this code doesn't work, something wrong with recordset, what should i do? thanks a lot for any help!

Sub QueryFN()

.....

Dim X As String
Dim dealsConnection As Connection
Dim rs As Recordset


X = "ODBC;filedsn=c:\Program Files\Common Files\ODBC\Data Sources\QuantumIreland;DBQ=QuantumIreland;DSN=deals;UId=sa;PWD=sa"
Set dealsConnection = _
wrkODBC.OpenConnection("deals", , False, X)

Dim SQLStmt As String
SQLStmt = " ... "

Set rs = dealsConnection.OpenRecordset(SQLStmt, dbOpenSnapshot)

With ThisWorkbook.Sheets("Sheet1&quot
With .Cells(2, 1)
.CurrentRegion.Clear
numberOfRows.CopyFromRecordset (rs)
End With
End With

.....
End Sub

 

[MargeJones]

Use the ADODB Reference (Microsoft ActiveX Data Objects X.X Library) in VB. Also, you must set up your ODBC connection on the client. Then try the following code:

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.ConnectionString = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=QuantumIreland;Data Source=deals"
cn.Open

rs.Open "Select statement", cn, OpenDynamic

 

[xlbo]

Or you could try MSQuery but I'm no expert in SQL. I do have another point tho which is that xq, I have noticed that you have asked a fair number of questions over the last week or more but you are still a visitor. You should really register, then you can thank people properly by awarding stars as well as being able to search the FAQs. There isn't really a downside to registering, you get 1 email per week which just provides updates and a link to your chosen forums.
Think about it
Rgds
Geoff