Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
Changing and Adding data through ODBC connection | Excel Discussion (Misc queries) | |||
always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
Changing OLE DB connection | Excel Discussion (Misc queries) |