How to import data from MS-Access to excel power query using sql statement and vba?

1
votes

In the company that I work, there is a folder with many access database files that are generated by a system. They have all the same structure but different data. I'm trying to build an Excel tool to compare and analyze some of them using power query (connected to workbook datamodel and use DAX as well). People who will use the system are not very familiar with vba, power queries (me neither), or SQL statements. So, what I thought to do is to code a vba function to allow people to select the files they want to analyze and then, update the workbook power queries with this data. The power queries source data should be SQL statements.

I'm using the "Formula" propertie to make all the changes needed (actually, new data source file, append a column with the filename, and append the result for the other data source files (if more than one is selected). The following code works if I'm importing a table but I'm not able to do it using a SQL statement. I've tried to change some Access.Database parameters like using Query="Select XXX" instead of Item="TableXXX" but it didn't work. I've found some information about using ODBC but I wouldn't like to work with ODBC because, it seems to me, that the tool can result in many connections to handle (open and delete) whenever the user decides to change the data source.

Following there is an example with only the "Formula" propertie for two data source files. I didn't write the loop to build the string strSource because I don't have problems with it. I suppose the problem is to use an SQL statement in Access.Database. Thanks for the help. 

strSource = "let
    Fonte = Access.Database(File.Contents("PathName\File01Name.mdb")),
    _TbEx_1 = Fonte{[Schema="",Item="TableExample"]}[Data],
    _TbEx_C1 = Table.AddColumn(_TbEx_1, "Case", each "File01Name", type text),
    Fonte_2 = Access.Database(File.Contents("PathName\File02Name.mdb")),
    _TbEx_2 = Fonte_2{[Schema="",Item="TableExample"]}[Data],
    _TbEx_C2 = Table.AddColumn(_TbEx_2, "Case", each "File02Name", type text),
    _TbExComb = Table.Combine({_TbEx_C1 , _TbEx_C2})
in
    _TbExComb"

ActiveWorkbook.Queries("PwQry01").Formula = strSource
ThisWorkbook.Connections("Qry-PwtQry01").Refresh

Searching on internet, I've found a way of using SQL statement, but the problem becames to combine the connections of different files on the same power query. Following the code that I've found, but I suppose the "Formula" propertie is closer the final answer.

cnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        "PathName\File01Name.mdb;Mode=Read"

    ThisWorkbook.Connections.Add2 _
        Name:="MyConnect", _
        Description:="Testing connection", _
        ConnectionString:=cnString, _
        CommandText:="SELECT * FROM TableExample", _
        lCmdtype:=XlCmdType.xlCmdSql, _
        CreateModelConnection:=True, _
        ImportRelationships:=True

Being naive, what I'd like to see is something like:

strSource = "let
    Fonte = Access.Database(File.Contents("PathName\File01Name.mdb")),
    _TbEx_1 = Fonte{[Schema="",Query="SELECT XXXXX"]}[Data],
    _TbEx_C1 = Table.AddColumn(_TbEx_1, "Case", each "File01Name", type text),
    Fonte_2 = Access.Database(File.Contents("PathName\File02Name.mdb")),
    _TbEx_2 = Fonte_2{[Schema="",Query="SELECT XXXX"]}[Data],
    _TbEx_C2 = Table.AddColumn(_TbEx_2, "Case", each "File02Name", type text),
    _TbExComb = Table.Combine({_TbEx_C1 , _TbEx_C2})
in
    _TbExComb"

ActiveWorkbook.Queries("PwQry01").Formula = strSource
ThisWorkbook.Connections("Qry-PwtQry01").Refresh
2
excelms-accessselectpowerquery
What is it you need to do with a SQL statement against the Access database? As far as I can tell, you can't write SQL statements in PQ directly against an Access database, but many of the actions you can do with PQ functions are converted to SQL when they are processed. – Wedge
@Wedge, , we already have dozens of "selects" done to prepare the data the way users want to see. I want to use this SQL statements instead of import full tables – Cristiane Salgado
I do not think this is possible with PQ. If you use PQ functions it can probably convert to SQL behind the scenes so the full tables are not loaded, but you cannot use the existing SQL statements you have. You would have to use an ODBC connection to do this with PQ, or an ADO connection in VBA. – Wedge
@Wedge, thanks for your help. I'm going to try to import tables and do the work in DataModel or use ODBC. If I have some progress, I let written here. – Cristiane Salgado

2 Answers

0
votes

It would be much easier to "concatenate" all the data using Access to a single mdb file and use this to do the rest of the work.

0
votes

I followed @Wedge suggestion and used ODBC approach. To make it work, I've created an ODBC with no ms-access file associated on Windows ODBC Data source. In Excel, the user selects the files he wants to analyze and this information is strPath() variable. With a loop, all files are included in Formula property. Variable tpPQSQLMap is a customized type (I believe could be a dictionary) with strPQNames to the query name and strPQSQL to the SQL statement. Following the final code

For pq = 1 To ThisWorkbook.Queries.Count
    strSource = ""
    strSrcComb = ""
    For k = 1 To UBound(strPath)
        strSource = strSource + "SourceC" + CStr(k) + "=Odbc.Query(""dbq=" + strPath(k) + ";dsn=MyODBC"", """ + tpPQSQLMap(pq).strPQSQLs + """), " + vbCrLf
        strSrcComb = strSrcComb + "SourceC" + CStr(k) + ","
    Next k
    strSrcComb = Left(strSrcComb, Len(strSrcComb) - 1)
    strSource = "let " + vbCrLf + strSource + "Final = Table.Combine({" + strSrcComb + "}) " + vbCrLf + "in " + vbCrLf + "Final "


    ThisWorkbook.Queries(pq).Formula = strSource
    ThisWorkbook.Connections("Query - " + tpPQSQLMap(pq).strPQNames).Refresh
Next pq