Results 1 to 3 of 3
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    ADO & SQL Server (VBA/Excel/2000)

    OK, I have junked all the crazy stuff I was working on last week trying to retrofit an old Access database to the new SQL Server database.

    I have seen where I can create a MS Query table from the data on the SQL Server. Can that be done
    Alan

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO & SQL Server (VBA/Excel/2000)

    Here is a good resource on ADO:

    http://www.erlandsendata.no/english/...odao/index.htm

    I didn't check for any SQL server stuff there though.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: ADO & SQL Server (VBA/Excel/2000)

    Here is the solution:
    <pre> <font color=red> 'Plan to hijack at this point and do direct import from SQL Server</font color=red>
    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</pre>

    I bring a 165 row RecordSet into my spreadsheet. Extract what I need from it and clear the space. In another step, I bring in another 30 row extract from another SQL database, use it and clear the space. The user is none the wiser.

    <font face="Comic Sans MS">You must be sure that Microsoft Active X Data Objects 2.6 is referenced in the VBE</font face=comic>. That is what stomped on me.
    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •