Results 1 to 3 of 3

reference to MS ActiveX Data Objects 2.8 Lib

Threaded View

  1. #1
    Forum Contributor
    Join Date
    09-28-2007
    Location
    New York, NY
    MS-Off Ver
    2007
    Posts
    120

    reference to MS ActiveX Data Objects 2.8 Lib

    I've written some code that requires the MS ActiveX Data Objects 2.8 Library. I've exported the book as an excel addin (.xla) and installed it on another computer. Apparently, that computer doesn't have this library and the code won't run. It gets hung up on:

    Dim cnPubs As ADODB.Connection
    Both computers have Microsoft DAO 3.6 Object Library. I don't know if its even the same thing. But when I un-reference the 2.8 and reference the 3.6, the code still won't work. It gets hung up on the same spot. Is there a way I can encapsule the 2.8 library with my xla so it works on any computer? or is there a way I can re-write my code to work with 3.6? Or are these 2 libraries not even related to each other? Also, I'm using Office 2007 on both computers if that matters, but I'd like my code to work with old versions also.

    Below is my entire code. It just queries a SQL server.

    Thx

    Sub GetData()
    Dim sPort As String
    Dim sEffDate As Date
    Dim aCols As Variant
    Dim sCols As String
    
    
    ActiveWorkbook.ActiveSheet.Cells.Clear
    sPort = InputBox("Portfolio: ", , "INA")
    dEffDate = InputBox("Date: ", , Date)
    aCols = Array("Portfolio", _
    "Ticker", _
    "Name", _
    "[Purchase Date]", _
    "Shares", _
    "Cost", _
    "Weight", _
    "Reports")
    
    sCols = Join(aCols, ",")
    
    ' Create a connection object.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection
    
    ' Provide the connection string.
    Dim strConn As String
    
    'Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    
    'Connect to the Pubs database on the local server.
    strConn = strConn & "DATA SOURCE=192.168.3.5\SQLEXPRESS;INITIAL CATALOG=Models;"
    
    'Use an integrated login.
    strConn = strConn & " UID=NAME; PWD=PASS"
    
    'Now open the connection.
    cnPubs.Open strConn
    
    
    ' Create a recordset object.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
    
    With rsPubs
        ' Assign the Connection object.
        .ActiveConnection = cnPubs
        ' Extract the required records.
        .Open "SELECT " & _
        sCols & " FROM HoldingsTBL WHERE Date='" & _
        CStr(Format(dEffDate, "yyyy-mm-dd")) & "' AND Portfolio='" & _
        sPort & "'"
        ' Copy the records into cell A1 on Sheet1.
        ActiveWorkbook.ActiveSheet.Range(Cells(1, 1), Cells(1, UBound(aCols))) = aCols
        ActiveWorkbook.ActiveSheet.Range("A2").CopyFromRecordset rsPubs
        .Close
    End With
    
    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing
    
    End Sub
    Last edited by MCCCLXXXV; 05-15-2009 at 09:18 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1