ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Changing Connection Strings (https://www.excelbanter.com/excel-programming/298933-changing-connection-strings.html)

matt
Changing Connection Strings
 
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[_6_]
Changing Connection Strings
 
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" wrote in message
...
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

matt
Changing Connection Strings
 
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

With NewConnectionString

End With

Regards

Matt

-----Original Message-----
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" wrote in
message
...
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[_6_]
Changing Connection Strings
 
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" wrote in message
...
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

With NewConnectionString

End With

Regards

Matt

-----Original Message-----
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" wrote in
message
...
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


.

matt
Changing Connection Strings
 
Hi Bob / Everyone

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

Regards

Matt


-----Original Message-----
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" wrote in
message
...
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

With NewConnectionString

End With

Regards

Matt

-----Original Message-----
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" wrote in
message
...
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


.



.

Grant Reid
Changing Connection Strings
 
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
Changing Connection Strings
 
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
out the hyperlink "Change the Database Location". This
might be of some
help.

Cheers - Grant


.


All times are GMT +1. The time now is 01:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com