Update Connection Strings.... SPT Queries

[lameid]

I am wrapping up an assignment and it is probable that the SQL Backend will be rehomed. Access 2003 MDB and SQL 2008...
I was chugging along when my code didn't compile and I found out that the connection string of a querydef object is read only therefore there is no refreshlinks method like tabledefs.

I think my workaround is to replace each effected SPT query by deleting the old one and creating a new one... Outside of Name, Connect and SQL properties is there any other property I should be on the lookout for?

For what it's worth my work in process... obviously I haven't touched the querydef piece for real.

Sub CorrectConnectConstant(strServer As String, strUser As String, strPassword As String)
    Const SQLDbName As String = "<DB Name Goes Here>"
    Const ConnectionDeclareConnection As String = "<Actual constant name goes here>"
    Dim mdl As Module
    Dim strConnect As String
    Dim tbl As DAO.TableDef
    Dim tbls As DAO.TableDefs
    Dim db As DAO.Database
    Dim qrys As DAO.QueryDefs
    Dim qry As DAO.QueryDef
    
    Set mdl = Modules("bas Connecting String")
    mdl.ReplaceLine 8, "Public Const " & ConnectionDeclareConnection & " As String = ""Provider=sqloledb;Data Source= " & strServer & ";Initial Catalog=" & SQLDbName & " ;User Id=" & strUser & ";Password=" & strPassword & ";"
    
    Set db = CurrentDb()
    Set tbls = db.TableDefs
    
    For Each tbl In tbls
        If InStr(1, tbl.Connect, SQLDbName) <> 0 Then
            tbl.Connect = ParameterCorrect(ParameterCorrect(ParameterCorrect(tbl.Connect, "Server=", strServer), "UID=", strUser), "PWD=", strPassword)
            tbl.RefreshLink
        End If
    Next tbl
    
    Set tbl = Nothing
    Set tbls = Nothing
    
    Set qrys = db.QueryDefs
    For Each qry In qrys
        If InStr(1, qry.Connect, SQLDbName) <> 0 Then
            'Oops for queries this is not updateable
            'qry.Connect = ParameterCorrect(ParameterCorrect(ParameterCorrect(qry.Connect, "Server=", strServer), "UID=", strUser), "PWD=", strPassword)
            'qry.RefreshLink
        End If
    Next qry
    Set qry = Nothing
    Set qrys = Nothing
 
    Set db = Nothing
    
End Sub

Function ParameterCorrect(strOrigConnect As String, strReplaceParameter, strReplaceValue) As String
    Dim lngStartPos  As Long
    Dim lngEndingPos As Long
    Dim strReplace As String
    
    lngStartPos = InStr(1, strOrigConnect, strReplaceParameter)
    lngEndingPos = InStr(lngStartPos, strOrigConnect, ";")
    lngStartPos = lngStartPos + Len(strReplaceParameter) - 1
    strReplace = Mid(strOrigConnect, lngStartPos, lngEndingPos - lngStartPos)
    ParameterCorrect = Replace(strOrigConnect, strReplace, strReplaceValue)
End Function

 

[lameid]

Ok so there was a mistake in my parameter correct function and apparently, connect can be written... So help lied I guess...

Sub CorrectConnectConstant(strServer As String, strUser As String, strPassword As String)
    Const SQLDbName As String = "<DB Name Goes Here>"    Const ConnectionDeclareConnection As String = "<Actual constant name goes here>"
    Dim mdl As Module
    Dim strConnect As String
    Dim tbl As DAO.TableDef
    Dim tbls As DAO.TableDefs
    Dim db As DAO.Database
    Dim qrys As DAO.QueryDefs
    Dim qry As DAO.QueryDef
    
    'Replace constant line in module that is the connection string used throghout database
    Set mdl = Modules("bas Connecting String")
    mdl.ReplaceLine 8, "Public Const " & _
    ConnectionDeclareConnection & " As String = ""Provider=sqloledb;Data Source= " & _ 
    strServer & ";Initial Catalog=" & SQLDbName & " ;User Id=" & strUser & ";Password=" & strPassword & ";"
    
    Set db = CurrentDb()
    Set tbls = db.TableDefs
    
    For Each tbl In tbls
        If InStr(1, tbl.Connect, SQLDbName) <> 0 Then
            tbl.Connect = ParameterCorrect(ParameterCorrect(ParameterCorrect(tbl.Connect, "Server=", strServer), "UID=", strUser), "PWD=", strPassword)
            tbl.RefreshLink
        End If
    Next tbl
    
    Set tbl = Nothing
    Set tbls = Nothing
    
    Set qrys = db.QueryDefs
    For Each qry In qrys
        If InStr(1, qry.Connect, SQLDbName) <> 0 Then
            qry.Connect = ParameterCorrect(ParameterCorrect(ParameterCorrect(qry.Connect, "Server=", strServer), "UID=", strUser), "PWD=", strPassword)
        End If
    Next qry
    Set qry = Nothing
    Set qrys = Nothing
    Set db = Nothing
    
End Sub

Function ParameterCorrect(strOrigConnect As String, strReplaceParameter, strReplaceValue) As String
    Dim lngStartPos  As Long
    Dim lngEndingPos As Long
    Dim strReplace As String
    
    lngStartPos = InStr(1, strOrigConnect, strReplaceParameter)
    lngEndingPos = InStr(lngStartPos, strOrigConnect, ";")
    lngStartPos = lngStartPos + Len(strReplaceParameter)
    strReplace = Mid(strOrigConnect, lngStartPos, lngEndingPos - lngStartPos)
    ParameterCorrect = Replace(strOrigConnect, strReplace, strReplaceValue)
End Function