Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014
    Posts
    1

    Question Unanswered: DSum between access & Excel

    Hello there,

    I'm stuck on a problem and not sure where to go for help.

    I want to take an existing excel spread sheet, select a singular cell, and build a Dsum function that will look inside an access database and pull out the sum of a "cost" field based on the criteria of a seperste field in that same access table.

    Is this possible?

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    You have to build an ADO connection then copyFromRecordset

    Code:
    Public Sub CopyRST()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim DB
    Dim vProvid
    
    Set con = New ADODB.Connection
    Set rs = New ADODB.Recordset
     
     uid = "username"
     pwd = ""
     DB = "c:\file\MyDb.mdb"
     vProvid = "Microsoft.Jet.OLEDB.4.0"     '  "SQLOLEDB"
       
    With con
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("User ID").Value = uid
        .Properties("Password").Value = pwd
        
        .Open "Data Source=" & DB 
    End With
     
    Set rs = con.Execute("qsMyQuery")
    ActiveWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rs
    
    rs.Close
    con.Close
    End Sub

Posting Permissions

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