Results 1 to 7 of 7

Thread: Connectionstring not being set properly by code - query data source not changing
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Connectionstring not being set properly by code - query data source not changing

    Hi
    I have two excel files stored in Dropbox - one a database that is not opened by users and the other a tool that runs two query tables from the database and is supposed to refresh the query tables on auto_open by setting the connectionstring to the users filepath to find the data source - database file.
    Using Query tables is new for me so I'm struggling a bit!
    Here's where I am up to at the moment - this runs and then throws an error on the last line.
    When I go into query properties I see the data source has not changed.

    Code:
    Sheets("Table-Data").Select
        Range("a1").Select
        
        Dim username As String
        username = Environ("username")
        
        Dim q As WorkbookQuery
        For Each q In ThisWorkbook.Queries
            Connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" & Environ("UserName") & "\Dropbox\Delphy - Reports\Crop Chemical Database;Extended Properties=Excel 12.0 Macro;HDR=YES;"
            
        Next
    ActiveWorkbook.connections("Query - Data (13)").refresh
    I've scoured the forums and see references to ADODB.Connection etc but the syntax doesn't work for me.

    I'm at a loss and getting frustrated....I don't code very often nowadays and I just need to get this done - everything else works fine.....

    All help gratefully received.

    Thanks Andy

  2. #2
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    894
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Connectionstring not being set properly by code - query data source not changing

    You're currently assigning a text value to the variable called Connectionstring. But you do not use that string to update any property of q.
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Connectionstring not being set properly by code - query data source not changing

    Quote Originally Posted by jkpieterse View Post
    You're currently assigning a text value to the variable called Connectionstring. But you do not use that string to update any property of q.
    Thanks - ok - I dont know how to update the proerty of q - how do ou do hat pease?

    Andy

  4. #4
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    894
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Connectionstring not being set properly by code - query data source not changing

    Given that you are trying to update Querytables and not tables you need a different object than ThisWOrkbook.QUeries:
    Code:
    Sub UpdateQueryTables()
        Dim Qt As QueryTable
        Dim Sh As Worksheet
        Dim ListObj As ListObject
        For Each Sh In Worksheets
            'For connections which ar ein tables:
            For Each ListObj In Sh.ListObjects
                If ListObj.SourceType = xlSrcExternal Then
                    With ListObj.QueryTable
                        .Connection = "Your connection string goes here"
                    End With
                End If
            Next
            'For older connections not in a table:
            For Each Qt In Sh.QueryTables
                Qt.Connection = "Your connection string goes here"
            Next
        Next
    End Sub
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Connectionstring not being set properly by code - query data source not changing

    Jan - evening

    Thanks so much for this - I'd just worked out I was refreshing connections and not query tables....was about to pack in for day!

    I've copied your code in and it runs - but still got an issue though - not changing the query table data source so still get error message on refresh.

    Code:
        Dim username As String
        username = Environ("username")
        
        Dim Qt As QueryTable
        Dim Sh As Worksheet
        Dim ListObj As ListObject
        For Each Sh In Worksheets
            'For connections which ar ein tables:
            For Each ListObj In Sh.ListObjects
                If ListObj.SourceType = xlSrcExternal Then
                    With ListObj.QueryTable
                        .Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" & Environ("UserName") & "\Dropbox\Delphy - Reports\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"
                    End With
                End If
            Next
            'For older connections not in a table:
            For Each Qt In Sh.QueryTables
                Qt.Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" & Environ("UserName") & "\Dropbox\Delphy - Reports\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"
            Next
        Next
    ActiveWorkbook.RefreshAll
    The table is still referencing another location in computers C: Drive rather than the one in the connection string - I've set it up this way to replicate the need for a username driven location.

    Any thoughts what i is still an issue?

    I really appreciate your time with this.

    thanks...........Andy

  6. #6
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    894
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Connectionstring not being set properly by code - query data source not changing

    Have you actually tried changing the connection manually through the Excel user interface? I see you're using a dropbox folder, maybe Excel doesn't like that?
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  7. #7
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Connectionstring not being set properly by code - query data source not changing

    Jan - hi

    Thanks for coming back to me - sorry for delayed response - was away yesterday.
    I've tried the code just working between C: and D: drive on my PC and it still doesnt change the query table data source so evaluates as error again - says cannot find c: drive (where table is pointing now).

    Manually changing is no issue - either within C, D or Dropbox - that caught me out as it worked for me but then wouldnt work for another user with different user name in file location path....

    Code is now:
    Code:
        Dim username As String
        username = Environ("username")
        
        Dim Qt As QueryTable
        Dim Sh As Worksheet
        Dim ListObj As ListObject
        For Each Sh In Worksheets
            'For connections which are in tables:
            For Each ListObj In Sh.ListObjects
                If ListObj.SourceType = xlSrcExternal Then
                    With ListObj.QueryTable
        '                .Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" & Environ("UserName") & "\Dropbox\Delphy - Reports\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"
                        .Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"
                    End With
                    
                End If
            Next
            'For older connections not in a table:
            For Each Qt In Sh.QueryTables
            '    Qt.Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" & Environ("UserName") & "\Dropbox\Delphy - Reports\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"
                Qt.Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"
    
    
            Next
        Next
    
    
        ActiveWorkbook.RefreshAll
    As you can see I deactivated the dropbox string and replaced with local drive string.

    There would seem to be no reason why this cannot work but....!

    Help very much appreciated.

    regards..........Andy

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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