SQL Server data to Excel 2000

[Guest]

I am trying to build a query to our SQL Server database to bring back specific information.

I do not want to slap everything into cells, just some of it. I thought I could build an “on the fly†MS Query, grab what I need and put into cells, then close the Query

Two problems (1) dim XXX as ADO.Connection -- I get User-defined type not define
(2) Tried recording building a MS Query and nothing was recorde

 

[John Michl]

Wonder if you are having the same problem I'm having (see my message from
earlier today.) I recorded a macro to pull the data the first time and then
tried to modify to do some groupings. I'm able to use count in the select
statement but not average or sum. I recall someone once saying that the
Microsoft Query and SQL is not as clean as one would think. Something
about the values in the SQL database being converted to text on the way
over. (I could be wrong here.)

- John

I am trying to build a query to our SQL Server database to bring back specific information.

I do not want to slap everything into cells, just some of it. I thought I

could build an "on the fly" MS Query, grab what I need and put into cells,
then close the Query.

 

[onedaywhen]

Wonder if you are having the same problem I'm having (see my message from
earlier today.)
- John

If you mean the one about aliases, then no.

Try 'ADODB' in place of 'ADO'. This declaration relies on you setting
a reference to Microsoft ActiveX Data Objects, know to the VBE by the
class name 'ADODB' (look in the Object Browser).

--

 

[MacroAlan]

Here is the solution:

Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim iField As Long

Set oConn = New ADODB.Connection
oConn.CursorLocation = adUseClient
oConn.Open "PROVIDER=MSDASQL;driver={SQL
Server};server=DATABASE; database=SalesCommisions;"
Set oRS = New ADODB.Recordset


strSQL = "SELECT LastMonth.JobNo, LastMonth.YearMonth,
LastMonth.JTDcosts," _
& "LastMonth.JTDsales, LastMonth.Balance, LastMonth.Commisson" _
& " FROM SalesCommisions.dbo.LastMonth LastMonth" _
& " WHERE (LastMonth.YearMonth='" & strYearMonth & "')" _
& " ORDER BY LastMonth.JobNo"

oRS.Open strSQL, oConn, adOpenStatic, adLockOptimistic


If Not oRS.EOF Then
For iField = 1 To oRS.Fields.Count
Cells(1, iField).Value = oRS.Fields(iField - 1).Name
Next
range("AB1").CopyFromRecordset oRS
End If
lastSQLrow = Application.CountA(ActiveSheet.range("AB:AB"))
oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing

MacroAlan