Use VBA to update ODBC path of Visual FoxPro query table connection

[9tanstaafl9]

Many years ago, I found this wonderful code on mrexcel.com that let me update all the query tables just by changing the connection string. A few Excel versions later, my code is no longer working for all the queries. It works with all the tables created in older versions of Excel, but not the Excel 2013 query tables that have that blue striped look to them.

Sub ChangeDatabasePathManually()
'changes all queries in workbook

Const strPath As String = "C:\MB7\Sample Company"

  Dim qt As QueryTable
  Dim wks As Worksheet

  For Each wks In ActiveWorkbook.Worksheets
    For Each qt In wks.QueryTables
      With qt

        .Connection = Join$(Array( _
            "ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=", _
            strPath, _
            ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" _
            ), vbNullString)

      End With
    Next qt
  Next wks

  Set qt = Nothing
  Set wks = Nothing
End Sub

My tables are all Visual FoxPro tables. And yes I know that is archaic, but I have to use that driver to connect to the data I need.

When I look under Data / Connections, the OLD tables have two semicolons before SourceDB, and the new tables have only one. Other than that I can't see any difference in the connection strings within Excel.

When answering, please bear in mind that I am ok with VBA, but know next to nothing about ODBC/SQL etc.

 

[WBenhart]

The connection string for the ODBC that you see the 2 semicolons is for the user name and password.

Your Code: "ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=",

Should be: "ODBC;DSN=Visual FoxPro Tables;UID=UserID;PWD=Password;SourceDB=", _

 

[9tanstaafl9]

Thank you! I can't check it now because I am not at the office, but I will on Tuesday.

One question. Is that the correct format if there is no user id or password required on the database they are connecting to?

 

[WBenhart]

Then your code should work without the UID & PWD, just as you have it.

BTW, Microsoft office 2013 uses 64bit drivers now, MS 2010 and below uses 32bit.

 

[9tanstaafl9]

It does work, but only for the legacy tables not for any newly created tables, even though I am also choosing the Visual FoxPro driver AND making sure all the settings are the same. Those tables just remain as they were originally.

If I edit the table connections manually then I can change them successfully. The problem is that I'm going to have to do this about a thousand times.

 

[9tanstaafl9]

Actually, it is not important at all that I use THIS code. If you know of any other patch of code that can be used to change all the queries' paths in an Excel 2013 report that would work fine. Also, it is ok if the legacy tables don't work and only the new tables do. I can always run two macros to update as needed.

 

[WBenhart]

I wrote a c# program to programmatically go through a directory and change the connection strings to all the files in the directory for you. it creates a copy of the file and moves the completed one into a "Processed" directory.

I've changed my method of dynamically creating the connection strings and ODBC automatically by reading an XML file when Excel opens, so I haven't used the app in a while.

I can send you it if you want to try it out..

 

[9tanstaafl9]

I would appreciate that very much. Thank you!