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

ANDYB0ARDMAN

New Member
Joined
Jun 23, 2019
Messages
5
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
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
980
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.
 

ANDYB0ARDMAN

New Member
Joined
Jun 23, 2019
Messages
5
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
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
980
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
 

ANDYB0ARDMAN

New Member
Joined
Jun 23, 2019
Messages
5
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
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
980
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?
 

ANDYB0ARDMAN

New Member
Joined
Jun 23, 2019
Messages
5
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
 

Forum statistics

Threads
1,084,796
Messages
5,379,933
Members
401,633
Latest member
DinnerB0ne

Some videos you may like

This Week's Hot Topics

Top