Accessing SQL Compact database .sdf file on a network share by multiple users is not supported. You should use SQL Server Express edition for this. There are also multiple posts on stackoverflow on this subject. The version 3.5 supports opening .sdf file exclusively from a network share but 4.0 does not. But no SQL CE version supports shared access of multiple network users to 1 .sdf file.
But upgrading your application to support both SQL Express and SQL Compact databases could be relatively easy task. It depends on how your application access data. For example using Entity Framework your queries could be generated depending on your actual database connection.
You can also use generic classes DbConnection, DbCommand etc. instead of SqlCeConnection, SqlCeCommand etc. - thus you can change used database type without having to maintain two separate versions of your project.
Download SQL Express 2014 with Tools SQLEXPRWT http://msdn.microsoft.com/cs-cz/evalcenter/dn434042.aspx. (You can eventually use also older versions e.g. 2008) SQL Server has a lot more SQL features and data types than SQL CE, so watch that you use only stuff that is compatible with SQL CE.
In your app.config have you can have two connection strings:
<add name="CompactDBConnection" connectionString="data source=|DataDirectory|\CE.sdf; password=xxxxxx; SSCE:Max Buffer Size=16384; temp file max size=256; ssce:autoshrink threshold=100; ssce:max database size=4091" providerName="System.Data.SqlServerCe.4.0" />
<add name="ExpressDBConnection" connectionString="Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername; Password=myPassword;" providerName="System.Data.SqlClient" />
You can chose then which one to use at the app startup
For creating DbConnection check this C# Retrieving correct DbConnection object by connection string.
Here is a small example of calling stored procedure using DbCommand instead of SqlCeCommand:
DbConnection dbConn = GetConnection(connStr);
DbProviderFactory sqlF = DbProviderFactories.GetFactory(dbConn);
using (DbCommand b2bcmd = sqlF.CreateCommand())
{
DbParameter msg = sqlF.CreateParameter();
msg.ParameterName = "@errorMessage";
msg.Direction = ParameterDirection.Output;
msg.DbType = DbType.String;
msg.Value = string.Empty;
msg.Size = 2048;
b2bcmd.Connection = dbConn;
b2bcmd.CommandType = CommandType.StoredProcedure;
b2bcmd.CommandText = "PB2BImport";
b2bcmd.Parameters.Add(msg);
b2bcmd.ExecuteNonQuery();
result = Convert.IsDBNull(msg.Value) ? "N/A" : (string)msg.Value;
}
I presume you do not use Entity framework - but it would be much easier with it.
