View Full Version : VBA to view QT Connection String
jo15765
09-18-2012, 08:47 PM
I see you can use this code to view the location of all QueryTables in your workbook:
Public Sub QTLocation()
Dim QT As QueryTable
Dim Wks As Worksheet
For Each Wks In ActiveWorkbook.Worksheets
For Each QT In Wks.QueryTables
With QT
Debug.Print QT.Connection
End With
Next QT
Next Wks
End Sub
And for example this is the Connection string that was returned....
ODBC;DSN=MS Access Database;DBQ=C:\Test\Test22.mdb;DefaultDir=C:\Test\Test22.mdb;DriverId=25;F IL=MS Access;MaxBufferSize=2048;PageTimeout=5;
I am wanting to only change the piece beginning with the DBQ to another database...
DBQ=C:\Test\Test22.mdb;DefaultDir=C:\Test\Test22.mdb
Is it possible for me to do this via VBA somehow, if so can someone please show me?
Sub QTLocationchange_snb()
For Each sh In sheets
For Each qt In sh.QueryTables
qt.connection=split(qt.connection,"DBQ=")(0) & "DBQ=G:\OF\snb.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
Next
Next
End Sub
Bob Phillips
09-19-2012, 12:59 AM
Dim vecParts As Variant
vecParts = Split(connString, "DBQ=")
connString = vecParts(0) & "DBQ=new path;" & Right$(vecParts(1), Len(vecParts(1)) - InStr(vecParts(1), ";"))
jo15765
09-19-2012, 12:27 PM
Sub QTLocationchange_snb()
For Each sh In sheets
For Each qt In sh.QueryTables
qt.connection=split(qt.connection,"DBQ=")(0) & "DBQ=G:\OF\snb.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
Next
Next
End Sub
SNB I tried your code and it does not give any error when processing but it is not actually replacing the connection string for me :(
jo15765
09-19-2012, 12:30 PM
Dim vecParts As Variant
vecParts = Split(connString, "DBQ=")
connString = vecParts(0) & "DBQ=new path;" & Right$(vecParts(1), Len(vecParts(1)) - InStr(vecParts(1), ";"))
xld (maybe I am missing something) but I copy/pasted your code over and I get a subscript out of range message on the connString line?
did you replace 'G:\OF\snb.mdb' by the fullname of your 'new' database file ?
jo15765
09-19-2012, 03:44 PM
I replaced the code with this line:
QT.Connection = Split(QT.Connection, "DBQ=")(0) & "DBQ=C:\Test\Test321.mdb;DriverID=25;FIL=MSAccess;MaxBufferSize=2048;PageTim eout=5;"
And it runs error free, but when I right click the query and go to Edit Query, and view the SQL of the query the connection is still to the original database.
I also tested this code and same outcome...it cycles thro issue free but the connection is not changed.
Sub Macro1()
Dim qt As QueryTable
Dim wks As Worksheet
Dim strPath As String
Dim NewConnection
NewConnection= InputBox(Prompt:="Enter Cpnnection Change.", Title:="Connection Change")
strPath = "C:\Test" & NewConnection & "db_RPT.mdb"
For Each wks In ActiveWorkbook.Worksheets
For Each qt In wks.QueryTables
With qt
.Connection = Join$(Array("ODBC;DSN=MS Access Database;DBQ=", strPath, ";DriverID=25;FIL=MSAccess;MaxBufferSize=2048;PageTimeout=5;"))
.Refresh BackgroundQuery:=False
Debug.Print qt.Connection
End With
Next qt
Next wks
Set qt = Nothing
Set wks = Nothing
End Sub
Bob Phillips
09-20-2012, 02:58 AM
xld (maybe I am missing something) but I copy/pasted your code over and I get a subscript out of range message on the connString line?
Did you setup the variable connstring?
jo15765
09-20-2012, 05:01 AM
Did you setup the variable connstring?
Are you meaning adding in the piece where it says DBQ=NewPath?
If so I added a variable caled NewPath and set that to be the location I wanted to change it to.
Aflatoon
09-20-2012, 05:34 AM
Are the old connections getting output to the immediate window, or is nothing being output? Which version of Excel too?
jo15765
09-20-2012, 06:04 AM
The Debug.Print qt.Connections shows the new connection strings (the ones I want it changed to.)
Excel 2000
Aflatoon
09-20-2012, 06:34 AM
If you output the .Commandtext as well what does it look like? (does it include mention of the file path?)
Like Microsoft, I don't really support anything that old. :)
jo15765
09-20-2012, 07:02 AM
The qt.Connection shows the new connection string, but the command text is still showing the old database string.
jo15765
09-24-2012, 08:38 AM
If you output the .Commandtext as well what does it look like? (does it include mention of the file path?)
Like Microsoft, I don't really support anything that old. :)
Aflatoon thank you for pointing out that I needed the qt.CommandText changed as well as the qt.ConnectionString. Which learning that brought me to this handy article that helped me realize what coding to use.
http://www.dicks-clicks.com/excel/ExternalData5.htm
Sub ChangeConn()
Dim qt As QueryTable
Dim Wsh As Worksheet
Dim OldLoc As String, OldPath As String
Dim NewLoc As String, NewPath As String
Dim LastSlash As Long
Const Ext As String = ".mdb"
OldLoc = "C:\Program Files\Microsoft Office\Office\Samples\Northwind"
NewLoc = "C:\NewFolder\Northwind"
LastSlash = InStrRev(OldLoc, "\", , vbTextCompare)
OldPath = Left(OldLoc, LastSlash - 1)
LastSlash = InStrRev(NewLoc, "\", , vbTextCompare)
NewPath = Left(NewLoc, LastSlash - 1)
For Each Wsh In ThisWorkbook.Worksheets
For Each qt In Wsh.QueryTables
qt.Connection = Replace(qt.Connection, OldLoc & Ext, NewLoc & Ext)
qt.CommandText = Replace(qt.CommandText, OldLoc, NewLoc)
qt.Connection = Replace(qt.Connection, OldPath, NewPath)
qt.Refresh
Next qt
Next Wsh
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.