PDA

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?

snb
09-19-2012, 12:38 AM
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?

snb
09-19-2012, 02:19 PM
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