Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Reply
 
Thread Tools Display Modes
  #1  
Old 11-28-2017, 06:54 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default Importing Query into Excel using ADODB

I am trying to import an Access query into Excel using VBA, but with a parameter. For example, the Access query I am trying to import asks the user for a valuation year, such as 2017.

I have already found some code, using Microsoft ActiveX Data Objects (ADODB). My understanding is that ADO is the technique that MS is currently supporting for doing imports, even though there are other older methods, such as DAO (Data Access Objects). I believe Microsoft is no longer adding anything to the DAO library, and it does not even exist in Excel 2016 64 bit, which is what I am using. The latest DAO dll is several years old.

The ADO code below works to import queries that have no parameters. It uses 6 variables that have been set up in the rest of the code, but this subroutine does most of the actual importing work. I would like to add a prompt and a parameter field on the sheet in Excel where I am importing the parameter query.

Regarding the code below where there are no parameters, I believe I need to add something to the query string to use the prompt and parameter, and some additional code to use the parameter, but a Google search didnít turn up anything useful.

I think the additional code needs to be added to this command to bring in the parameter when I open the query:
With rs
Set .ActiveConnection = cn
.Open strQry
End With

Has anyone done this before, and perhaps can point me in the right direction?

VBA subroutine to import Access query with No Parameters:
Private Sub PullQueryDataNoParam(SrcPathfile, SrcQry, TgtTab, Col2Start, TgtCol, Row2Start)
'Purpose: Pull query results from Access into Excel, using ActiveX Data Objects interface
Dim strDb, strQry As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim i As Integer
Dim Cell4Title, Cell2Start As String

Cell4Title = Col2Start + CStr(Row2Start) 'the first row pasted is the column headings
Cell2Start = Col2Start & CStr(Row2Start + 1) 'the query results are pasted in the next row

Sheets(TgtTab).Select 'select tab in Excel WB for results to be imported
strDb = SrcPathfile 'path and filename of Access DB
strQry = "SELECT * FROM " & SrcQry 'code for select query

Set cn = New ADODB.Connection 'create a new ADO connection to Access DB
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDb & ";"

Set rs = New ADODB.Recordset

'open the query in Access
With rs
Set .ActiveConnection = cn
.Open strQry
End With

'copy field names in Access as a title row in Excel (bold font), then copy the query results in the next row
With Sheets(TgtTab)
For i = TgtCol To rs.Fields.Count + TgtCol - 1
.Cells(Row2Start, i).Value = rs.Fields(i - TgtCol).Name 'fields is a 0 based collection
Next i
.Range(Cell4Title).Resize(ColumnSize:=rs.Fields.Co unt).Font.Bold = True 'title row is bolded and resized to query
.Range(Cell2Start).CopyFromRecordset rs
End With

rs.Close 'close ADODB.RecordSet
cn.Close 'close ADODB.Connection

End Sub 'PullQueryData(a,b,c,d,e,f)
Reply With Quote
  #2  
Old 11-29-2017, 09:32 AM
SlowMotionWalter's Avatar
SlowMotionWalter SlowMotionWalter is offline
Member
CAS
 
Join Date: Jun 2013
Posts: 10,811
Default

I do it when assigning the value to strQry. This involves housing the SQL in Excel and not Access.

strQry = "SELECT * FROM " & SrcQry & " WHERE [tblName].[fieldName] = " & Worksheets("wsName").Cells(1,5).Value

I don't know how to pass parameters to Access directly. I've gotten around that by using the Excel parameters to create a table in Access then using that table in the query. That was only for 1 parameter, though.
__________________
Quote:
Originally Posted by Kangaz wit Attitude View Post
Force of habit, 2pac's been typing "88" a LOT since Trump got elected.
Spoiler:
Quote:
Originally Posted by JMO View Post
You ought to see the bush. It's impressive.
Quote:
Originally Posted by Pikachu
10:53 pm: some ppl dont take advices well
Quote:
Originally Posted by mayo fan
9:45 pm: ao fan would be hot covered in mayo!
Quote:
Originally Posted by Snikelfritz
if you'd like I can come visit and dress up like a girl and get in some fights
Quote:
Originally Posted by Kaner3339 View Post
i think everyone needs to do this type of thing to get a dose of reality and straighten people up. it's kinda like going to the mountains and becoming a monk except it's with hundreds of potatoes and a lot of stoners with tattoos in a kitchen
Reply With Quote
  #3  
Old 11-29-2017, 10:50 AM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Quote:
Originally Posted by SlowMotionWalter View Post
I do it when assigning the value to strQry. This involves housing the SQL in Excel and not Access.

strQry = "SELECT * FROM " & SrcQry & " WHERE [tblName].[fieldName] = " & Worksheets("wsName").Cells(1,5).Value

I don't know how to pass parameters to Access directly. I've gotten around that by using the Excel parameters to create a table in Access then using that table in the query. That was only for 1 parameter, though.
Thanks for the tip, SlowMotionWalter. Would you be able to share any of your VBA code?
Reply With Quote
  #4  
Old 11-29-2017, 10:59 AM
act_123 act_123 is offline
Member
CAS
 
Join Date: Dec 2013
Posts: 2,087
Default

He did: Worksheets("wsName").Cells(1,5).Value

This refers to a tab named wsName and the value in Range("A5"). He is saying you could put the input in there.

Do you have powerpivot? It might be easier for you to import the data in powerpivot in Excel 2016.

I used to use ADODB (and before that DAO) a lot. However, using powerpivot is a lot cleaner and easier.
__________________
ACAS 7 8 9
Reply With Quote
  #5  
Old 11-29-2017, 11:32 AM
SlowMotionWalter's Avatar
SlowMotionWalter SlowMotionWalter is offline
Member
CAS
 
Join Date: Jun 2013
Posts: 10,811
Default

Quote:
Originally Posted by act_123 View Post
He did: Worksheets("wsName").Cells(1,5).Value

This refers to a tab named wsName and the value in Range("A5"). He is saying you could put the input in there.

Do you have powerpivot? It might be easier for you to import the data in powerpivot in Excel 2016.

I used to use ADODB (and before that DAO) a lot. However, using powerpivot is a lot cleaner and easier.
Yup. One thing to keep in mind if you do it the way I showed is that when using wildcards, you need to use SQL wildcards instead. % instead of * and _ instead of ?.

I did most of my ADODB and DAO stuff before PowerPivot. Thanks for the heads-up that it may be easier if I need to do it in the future.
__________________
Quote:
Originally Posted by Kangaz wit Attitude View Post
Force of habit, 2pac's been typing "88" a LOT since Trump got elected.
Spoiler:
Quote:
Originally Posted by JMO View Post
You ought to see the bush. It's impressive.
Quote:
Originally Posted by Pikachu
10:53 pm: some ppl dont take advices well
Quote:
Originally Posted by mayo fan
9:45 pm: ao fan would be hot covered in mayo!
Quote:
Originally Posted by Snikelfritz
if you'd like I can come visit and dress up like a girl and get in some fights
Quote:
Originally Posted by Kaner3339 View Post
i think everyone needs to do this type of thing to get a dose of reality and straighten people up. it's kinda like going to the mountains and becoming a monk except it's with hundreds of potatoes and a lot of stoners with tattoos in a kitchen
Reply With Quote
  #6  
Old 11-29-2017, 11:40 AM
act_123 act_123 is offline
Member
CAS
 
Join Date: Dec 2013
Posts: 2,087
Default

Yes PowerPivot is really nice as it doesn't bog down your spreadsheet with data. It also allows you to import mappings and creating relationships (joins acting like vlookups). You can also make additional columns with simple excel formulas.

In Pivot tables you can then use DAX to create complex logic to do various forms of summarization like running totals, creating triangles, distinct counting, etc. As well as the fact you can use the OLAP functionality that you can just reference the cube for reporting and don't actually need the pivot tables.
__________________
ACAS 7 8 9
Reply With Quote
  #7  
Old 11-29-2017, 01:11 PM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,244
Default

Here's some code that I've written:

sqlPerform is for executing sql statements without something being returned. The SQL input can be multiple statements which I split with "Chr(3)"

SQLPullRecordSet performs a single query and returns a recordset.

SQLPullData: This looks like code that I never quite finished. It'll work as long as ocbOutput is a "Name". the prefix indicates that I might have wanted to possibly return the recordset if you provided a recordset, but I just never finished the code.

Spoiler:
Code:
Sub sqlPerform(vSQL As String, Optional oConnection As Object, Optional oSQLEnvironment As eConnectionType = eDefault)
    Dim lRS As Object 'LateBinding ADODB.Recordset
    Dim lStatements() As String
    Dim lSQL As String
    Dim i As LongPtr
    Dim lCreatedConnection As Boolean
    lStatements = Split(vSQL, Chr(3))
    If oConnection Is Nothing Then
        Set oConnection = createConnection(oSQLEnvironment )
        lCreatedConnection = True
    End If
    Set lRS = CreateObject("ADODB.Recordset")
    For i = LBound(lStatements, 1) To UBound(lStatements, 1)
        lSQL = lStatements(i)
        If Len(Trim(lSQL)) > 0 Then Call lRS.Open(lSQL, oConnection)
    Next i
    Set lRS = Nothing
    If lCreatedConnection Then
        If Not oConnection Is Nothing Then oConnection.Close
        Set oConnection = Nothing
    End If
End Sub
Function SQLPullRecordSet(vSQL As String, Optional oConnection As Object, Optional oSQLEnvironment As eConnectionType = eDefault) As Object
    Dim lRecordSet As Object 'LateBinding ADODB.Recordset
    Dim lCreatedConnection As Boolean
    If oConnection Is Nothing Then
        Set oConnection = createConnection(oSQLEnvironment )
        lCreatedConnection = True
    End If
    Set lRecordSet = CreateObject("ADODB.Recordset")
    lRecordSet.CursorLocation = 3 'adUseClient 'Necessary if you want to preserve dataset after closing connection.
    If Len(Trim(vSQL)) > 0 Then Call lRecordSet.Open(vSQL, oConnection)
    Set lRecordSet.ActiveConnection = Nothing 'Necessary if you want to preserve dataset after closing connection.
    Set SQLPullRecordSet = lRecordSet
    If lCreatedConnection Then
        If Not oConnection Is Nothing Then oConnection.Close
        Set oConnection = Nothing
    End If
End Function
Sub SQLPullData(vSQL As String, Optional ByRef ocbOutput As Variant, Optional oConnection As Object, Optional oSQLEnvironment As eConnectionType = eDefault, Optional oIncludeHeaders As Boolean = True, Optional oResizeName As Boolean = True)  'LateBinding
Dim lRecordSet As Object 'LateBinding ADODB.Recordset
Dim strcon As String
Dim lField As Variant
Dim j As LongPtr
Dim lRange As Range
Dim lName As Name
Dim lHeaders As Variant
Dim lCreatedConnection As Boolean
If StrComp(TypeName(ocbOutput), "Name") = 0 Then Set lName = ocbOutput
Application.ScreenUpdating = False
If oConnection Is Nothing Then
    Set oConnection = createConnection(oSQLEnvironment )
    lCreatedConnection = True
End If
If Not oConnection Is Nothing Then
    If oConnection.State = 1 Then
        Set lRecordSet = CreateObject("ADODB.Recordset")
        lRecordSet.CursorLocation = 3 'LateBinding adUseClient
        If Not lName Is Nothing Then
            Set lRange = lName.RefersToRange
            lRange.ClearContents
        End If
        Call lRecordSet.Open(vSQL, oConnection)
        If oIncludeHeaders Then
            lHeaders = Array()
            ReDim lHeaders(1 To 1, 1 To lRecordSet.Fields.Count)
            For Each lField In lRecordSet.Fields
                j = j + 1
                lHeaders(1, j) = lField.Name
            Next lField
            lRange.Cells(1, 1).Resize(1, UBound(lHeaders, 2)).Value = lHeaders
            Call lRange.Cells(2, 1).CopyFromRecordset(lRecordSet)
        Else
            Call lRange.Cells(1, 1).CopyFromRecordset(lRecordSet)
        End If
        lRecordSet.Close
        If oResizeName And Not lName Is Nothing Then Call Resources.AutoFitName(lName)
    End If
End If
If lCreatedConnection Then
    If Not oConnection Is Nothing Then oConnection.Close
    Set oConnection = Nothing
End If
End Sub


Spoiler:
Code:
Option Explicit

Enum eConnectionType
    eDefault = 0
    eDev = 0
    eTest = 1
    eProd = 2
End Enum
Function getSQLConnectionString(ByVal vSQLEnv As eConnectionType) As String
    Dim lDevStr As String, lTestStr As String, lProdStr As String
    lDevStr = "OMITTED"
    lTestStr = "OMITTED"
    lProdStr = "OMITTED"
    Select Case vSQLEnv
        Case eConnectionType.eDev
            getSQLConnectionString = lDevStr
        Case eConnectionType.eTest
            getSQLConnectionString = lTestStr
        Case eConnectionType.eProd
            getSQLConnectionString = lProdStr
        Case Else
            MsgBox ("Invalid SQL Environment Requested.  Please check parameter.")
            Exit Function
    End Select
End Function
Function createConnection(Optional oSQLEnvironment As eConnectionType = eConnectionType.eDefault, Optional ocbADOdbError As Boolean = False) As Object 'LATE BINDING
    Dim lConnection As Object
    On Error GoTo cnOpenErr
    Set lConnection = CreateObject("ADODB.Connection")
    lConnection.ConnectionString = getSQLConnectionString(oSQLEnvironment)
    lConnection.CursorLocation = 3 'LateBinding adUseClient
    lConnection.Open
    Set createConnection = lConnection
    On Error GoTo 0
    Exit Function
cnOpenErr:
    ocbADOdbError = True
    Exit Function
End Function
Public Sub AutoFitName(vName As Name, Optional olRowOffset As LongPtr = 0, Optional olColOffset As LongPtr = 0, Optional oKeepRows As Boolean = False, Optional oKeepCols As Boolean = False)
    Dim lRange As Range
    On Error Resume Next
        Set lRange = vName.RefersToRange
    On Error GoTo 0
    If Not lRange Is Nothing Then
        Set lRange = ExpandRangeToArea(vName.RefersToRange, olRowOffset, olColOffset, IIf(oKeepRows, vName.RefersToRange.Rows.Count, 0), IIf(oKeepCols, vName.RefersToRange.Columns.Count, 0))
        If Not lRange Is Nothing Then
            If lRange.Address(External:=True) <> vName.RefersToRange.Address(External:=True) Then
                vName.RefersTo = "=" & lRange.Address(External:=True)
            End If
        End If
    End If
End Sub
Public Function ExpandRangeToArea(vRng As Range, Optional oRowOffset As LongPtr = 0, Optional oColOffset As LongPtr = 0, Optional oMaxRows As LongPtr = 0, Optional oMaxCols As LongPtr = 0) As Range
    Dim r As Range
    Dim lRows As LongPtr
    Dim lCols As LongPtr
    'Expected input: vRng should be an anchor in the spreadsheet, the placement of this anchor should be the top left cell of the grid that you want to return.
    'If the anchor is placed outside of the grid that you want to return, use the offsets to adjust it.
    'So if the anchor is one row above the data table (in the column labels), use a row offset of 1.
    'The vRng input should be a single cell, if you give it multiple cells - it'll use cells(1) to take it down to 1 cell.
    Set vRng = vRng.Areas(1).Cells(1).Offset(oRowOffset, oColOffset)
    If IsEmpty(vRng.Cells(1)) Then
        Set ExpandRangeToArea = vRng
        Exit Function
    End If
    lRows = vRng.EntireColumn.Rows.Count - vRng.Cells(1).Row + 1
    lCols = vRng.EntireRow.Columns.Count - vRng.Cells(1).Column + 1
    If lRows > oMaxRows And oMaxRows > 0 Then lRows = oMaxRows
    If lCols > oMaxCols And oMaxCols > 0 Then lCols = oMaxCols
    'The area that we want to return is the intersection of the "current region" and the whole sheet down and left of the current cell.
    Set ExpandRangeToArea = Application.Intersect(vRng.CurrentRegion, vRng.Resize(lRows, lCols))
End Function

Last edited by dumples; 11-29-2017 at 01:17 PM..
Reply With Quote
  #8  
Old 11-29-2017, 01:16 PM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,244
Default

If you google late binding vs early binding in vba, there's some good stuff out the re to read. When Developing things, it's a lot easier to use early binding because it gives you suggestions when you're typing, which is really helpful if you don't know the object model. When you're ready to put something in production, I'd really encourage flipping to late binding - that'll give you better stability.
Reply With Quote
  #9  
Old 11-29-2017, 03:14 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Quote:
Originally Posted by act_123 View Post
He did: Worksheets("wsName").Cells(1,5).Value

This refers to a tab named wsName and the value in Range("A5"). He is saying you could put the input in there.

Do you have powerpivot? It might be easier for you to import the data in powerpivot in Excel 2016.

I used to use ADODB (and before that DAO) a lot. However, using powerpivot is a lot cleaner and easier.
Thanks. That helps a lot.

Regarding Powerpivot, I am surprised that you said it was a lot cleaner and easier than a macro. I'm a pretty old-school guy, but I never thought macros would already be old school.

Seriously, though, I don't use pivot tables in Excel. I was wondering if PowerPivot has a long learning curve.

I use Excel 2016, and I was aware that PowerQuery was baked into Excel now, instead of being an add-in, like in previous versions. Is PowerPivot available in the base Excel 2016, or do I need to download an add-in?
Reply With Quote
  #10  
Old 11-29-2017, 03:25 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Quote:
Originally Posted by dumples View Post
If you google late binding vs early binding in vba, there's some good stuff out the re to read. When Developing things, it's a lot easier to use early binding because it gives you suggestions when you're typing, which is really helpful if you don't know the object model. When you're ready to put something in production, I'd really encourage flipping to late binding - that'll give you better stability.
Thanks for the tip, dumples.

I see what they were saying about declaring an object at the start. I do always use Option Explicit, so I am forced to declare variables. Will this require me to declare objects as well? I may not be understanding the concept yet, but early binding sounds like a good idea.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 03:05 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.18314 seconds with 9 queries