Data Source=ServerName (ie I am not typing my server name so you'll have to improvise) Password=password (again, not disclosing that info!).
Although this isn't the fix for my problem I think I know what caused it all.
I don't think you can change application settings (connection strings) if you have dragged/dropped databound components onto forms.
Although, I have found a perfect way to overcome my issue of (a) developing with SQL Express and (b) changing the connection strings to deploy to the live SQL Server.
Check this out: http://msdn.microsoft.com/en-gb/vbasic/cc307956.aspx
You basically go to Application Settings, press F7 to load the code editor then add an Event Property for 'MySettingsEvents' and select the 'SettingsLoaded' event handler.
Then add some code as per the sample below - this allows you to dynamically change the connection string at runtime (note the ConnectionString name must match EXACTLY what you have in the Application Settings.
This sample is for my hard-coded SQL Server, but changing it to SQL Express at runtime for Dev work. Once I have finished, simply comment out all the below and the app reverts to the live connection string.
'This class allows you to handle specific events on the settings class:
' The SettingChanging event is raised before a setting's value is changed.
' The PropertyChanged event is raised after a setting's value is changed.
' The SettingsLoaded event is raised after the setting values are loaded.
' The SettingsSaving event is raised before the setting values are saved.
Partial Friend NotInheritable Class MySettings
Public WriteOnly Property RuntimeConnectionString() As String
Set(ByVal value As String)
My.Settings("InventoryDBConnectionString") = value
Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
Me.Item("InventoryDBConnectionString") = "Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\InventoryDB.mdf"";Integrated Security=True;Connect Timeout=30;User Instance=True"