Updating 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

Can anyone help me with the correct code to update my
connection strings?

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

 

[Grant Reid]

Hi Matt

See my reply to your posting on the Subject "Changing Connection Strings".

Regards - Grant

 

[Matt]

Hi Grant / Everyone

My apologies for that
I didn't see the second line of postings
Thought I'd missed out first time around

Thanks for your reply

That's helped a great deal

Regards

Matt