A Classic Run-Time 1004

I have a bit of VBA which updates connections in a workbook to a new server and/or database name, which you can then update all the tables with. It is useful because I have a lot of SQL linked tables, which would take a lot of time to update manually.

Before anyone says I should use PowerPivot, I'll say that I tried, and unfortunately it regularly crashed or threw up errors. This way works. Sort of...

So, the problem is that at the moment it seems to be throwing a 1004 error, though I'm sure it has been working in the past. A

Here is the code:

Sub UpdateAllConnections()
    Dim Server As String, Database As String, IntegratedSecurity As Boolean, UserId As String, Password As String
    Dim ConnectionString As String
    Dim MsgTitle As String
    Dim cn As WorkbookConnection
    
    MsgTitle = "Connection Update"

    If vbOK = MsgBox("Go grab your copy & paste brush!", vbOKCancel, MsgTitle) Then
        Server = InputBox("Database server & instance name", MsgTitle)
        If Server = "" Then GoTo Cancelled
        Database = InputBox("Database name", MsgTitle, "Tesco_Reading_phase2")
        If Database = "" Then GoTo Cancelled
        End If
                
        For Each cn In ActiveWorkbook.Connections
        
            cn.OLEDBConnection.Connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog = " & Database & ";Data Source=" & Server & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AHUNTER;Use Encryption for Data=False;Tag with column collation when possible=False;"
            
        Next cn
        
        MsgBox "Spreadsheet Updated", vbOKOnly, MsgTitle
    Exit Sub
Cancelled:
    MsgBox "Connections not updated", vbOKOnly, MsgTitle
End Sub

cn.OLEDBConnection.Connection... etc is the error line.


Any help would be very gratefully received!