Changing Connection Strings

[Matt]

Hello Everyone

I've got a workbook with a number of queries in it
I'm trying to update the database they connect to with
some code, as follows.

For some reason the connection strings are not being
updated. I presume there's something wrong with

With QT.Connection = NewConnectionString

Thanks in advance.

Matt

Sub UpdateConnectionStrings()
'This lists all the queries in a workbook by name
Dim WS As Worksheet
Dim QT As QueryTable
Dim iCount As Integer
For Each WS In ThisWorkbook.Worksheets
For Each QT In WS.QueryTables
' MsgBox "Worksheet Name: " & WS.Name
' MsgBox "Query Name: " & QT.Name
Debug.Print QT.Name & " " & QT.Connection
With QT.Connection = NewConnectionString

End With
Debug.Print QT.Name & " " & NewConnectionString
Debug.Print QT.Name & " " & QT.Connection

Next
Next
End Sub


Function NewConnectionString()

Dim strSQLDatabaseName, strSQLName, strSQLDatabase,
strLoginID, strPassword As String
Dim intFindDot As Integer

'Poplulate Login Strings
strLoginID = "SUN"
strPassword = "SUNSYS"
strSQLDatabaseName = "dbo.SUNDB426"

NewConnectionString = _
"ODBC;DSN=" & strSQLDatabaseName & ";UID=" & strLoginID
& ";PWD=" & strPassword & ";APP=SQL " & _
";AutoTranslate=No,"

End Function

 

[Bob Phillips]

Why not just use

With NewConnectionString

End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Matt]

Hi Bob / Everyone

When I do that I get the same effect
i.e. Debug.Print QT.Name & " " & NewConnectionString
returns the same information before and after

 

[Bob Phillips]

because we don't change QT in this case?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Matt]

Hi Bob / Everyone

Exactly!
Any ideas on some code that WILL change connection string
for the queries?

Regards

Matt

 

[Grant Reid]

Hi

Have a look at http://www.dicks-clicks.com/excel/ExternalData5.htm Check
out the hyperlink "Change the Database Location". This might be of some
help.

Cheers - Grant

 

[Matt]

Hi Grant

Indeed it does

Thank you very much indeed

Regards

Matt

-----Original Message-----
Hi

Have a look at http://www.dicks-

clicks.com/excel/ExternalData5.htm Check