locked
Excel VBA - ADO Connection to Azure Data Lake Store Issue RRS feed

  • Question

  • Hello Everyone,

    I am having some issues trying to run a very simple VBA script in Excel VBA using ADO to connect to an Access database file (.accdb) located on my company's Azure Data Lake Store (ADLS) site. We are using Excel 2016 and Access 2016. 

    I have no problem pulling the data into Excel from this Access database file located on ADLS through Power Query by going to Data -> Get Data -> From Azure -> From Azure Data Lake Store and connecting directly to the file from within Excel. Thus, I know this is not a security or account access issue. 

    Here is the VBA code that I have been trying to tweak in order to make a successful connection from Excel to the Access database file located on ADLS:

    'open a connection to an Access DB file on my company's azure data lake store
    Set cn = CreateObject("ADODB.Connection")
    StrProvider = "Provider=Microsoft.ACE.OLEDB.12.0;Password=my_ADLS_password;User ID=my_ADLS_account;"
    StrSource = "Data Source=my_access_DB_file_path_on_ADLS;"
    cn.Open StrProvider & StrSource

    For security reasons, I cannot provide my ADLS account information in the code above.

    I really just need help in determining the appropriate VBA ADO code syntax to make this connection work. I have spent a lot of time researching online about this specific issue with no luck.

    I do NOT want to simply automate the creation of a Power Query using VBA to pull data into Excel from the source. I know this is an option but it doesn't suite my needs. I want to use ADO to establish this connection on the fly and hopefully have the potential to read/write data from/to the database using SQL. In other words, I plan on not only being able to run SELECT SQL statements on the data source, but also INSERT, UPDATE, and DELETE statements as well.  

    Any help would be greatly appreciated. Thanks!

    -Justin

    Thursday, May 3, 2018 4:04 PM

All replies

  • Hello Justin CR,

    What's the result if you try to run the VBA code? Will you get an error? If so, please share us the error message.

    I do not have Azure Data Lake Store to reproduce your issue. I test the code using a local access database and it works well. I would suggest you do the test using a local access database to make sure you could connect to a local source and then try to connect the database in Azure Data Lake Store.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 4, 2018 1:53 AM
  • Hi Terry,

    When I run the below code on the Access DB file stored locally (not on ADLS), it works perfectly:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    'open a connection to the source DB
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    StrProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
    StrSource = "Data Source=" & Range("Source_DB_Path").Value & ";"
    cn.Open StrProvider & StrSource 

    The code above is much simpler in design because it doesn't require me to connect to ADLS, which is essentially a server that requires a user ID and password to access. The ADLS server, along with my ADLS user ID and password, would have to somehow exist in the required connection string. Here is what my code looks like if I try to include the Access DB file path to the file on ADLS and my ADLS account info (hidden for security reasons):

    Set cn = CreateObject("ADODB.Connection")
    cString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                   "Password=hidden;" & _
                   "User ID=hidden@hidden.com;" & _
                   "Data Source=adl://hidden.azuredatalakestore.net/DEV/Codes/AccessDB/LRPSFSourceDBTEST.accdb;"
    cn.Open cString 

    When I run this code, I get an error that says "Not a valid account name or password". This error doesn't make any sense because it is the correct ADLS account information to login to ADLS and access the file. I think the syntax of the entire connection string is wrong to begin with. I am hoping someone knows what the structure of this connection string should be. I would think the code syntax would be similar to connecting to say, Google Drive, and accessing data from an Access DB file on there via ADO.  

    Friday, May 4, 2018 7:13 PM
  • Hello Justin CR,

    >>Password=my_ADLS_password;User ID=my_ADLS_account;

    Sorry for my careless for your original post, I note that you are trying to pass your ADLS account and password. I think it should not be ADLS account and password.  

    Will you be asked to enter your ADLS ID and password while pulling data into Excel via Power Query? It seems that your access database is not protected. Try to remove the user id and password in cString, could you open the connect? If not, what's the error message?

    Besides, have you tried to record a macro of pulling data via Power Query? What's the connecting string of the connect?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 7, 2018 8:55 AM
  • Hi Terry,

    Yes, I was asked to provide my ADLS ID and password when pulling data into Excel via Power Query. Also, yes, my Access DB file itself is NOT protected. I tried removing the user ID and password from the code  (see below):

    Set cn = CreateObject("ADODB.Connection")
    cString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=adl://hidden.azuredatalakestore.net/DEV/Codes/AccessDB/LRPSFSourceDBTEST.accdb;"
    cn.Open cString 

    When I run this code, I get the error message "Not a valid file name". 

    I have tried recording a macro of pulling data via Power Query. Here is what the code looks like if it helps: 

        ActiveWorkbook.Queries.Add Name:="Query1", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = DataLake.Contents(""adl://hidden.azuredatalakestore.net/DEV/Codes/AccessDB/LRPSFSourceDBTEST.accdb"")," & Chr(13) & "" & Chr(10) 

    & "    Content = Source{[Name=""""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Imported Access"" = Access.Database(Content)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 

       #""Imported Access"""
        ActiveWorkbook.Queries.Add Name:="_FCST_INPUT (2)", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = DataLake.Contents(""adl://hidden.azuredatalakestore.net/DEV/Codes/AccessDB/LRPSFSourceDBTEST.accdb"")," & Chr(13) & "" & Chr(10) 

    & "    Content = Source{[Name=""""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Imported Access"" = Access.Database(Content)," & Chr(13) & "" & Chr(10) & "    _FCST_INPUT1 = #""Imported 

    Access""{[Schema="""",Item=""FCST_INPUT""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    _FCST_INPUT1"
        ActiveWorkbook.Worksheets.Add
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Query1;Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [Query1]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Query1"
            .Refresh BackgroundQuery:=False
        End With
        ActiveWorkbook.Worksheets.Add
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""_FCST_INPUT (2)"";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [_FCST_INPUT (2)]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "_FCST_INPUT__2"
            .Refresh BackgroundQuery:=False
        End With

    I noticed that the Power Query code is using "OLEDB;Provider=Microsoft.Mashup.OleDb.1", so if I swap that with ACE Provider I run this code now: 

    Set cn = CreateObject("ADODB.Connection")
    cString = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;" & _
              "Data Source=adl://hidden.azuredatalakestore.net/DEV/Codes/AccessDB/LRPSFSourceDBTEST.accdb;"
    cn.Open cString

    Now I get the error, "Data source name too long". I wonder if this latest code is getting me closer. I am not sure how the path to the file is too long. I could try shortening it and see if that works.  

    EDIT AFTER TESTING NEW CODE WITH SHORTER FILE PATH: I am still getting the same error of "Data source name too long" after reducing the file path down to this: adl://hidden.azuredatalakestore.net/DEV/LRPSFSourceDBTEST.accdb
    • Edited by Justin CR Monday, May 7, 2018 5:28 PM
    Monday, May 7, 2018 5:23 PM
  • Hello Justin CR,

    I think ADO.Net could not link to a database in a protected site. It should use a specific provider. Not familiar with Azure Data Lake Store, does it provide a ADO.NET provider like SharePoint ADO.NET Provider? If not, I think you could not use ADO.NET to connect to an access database in Azure Data Lake Store.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 8, 2018 10:15 AM