Access / SQL Server replication

[cwarner]

Hi,

I want to replicate my Access front-end, SQL-server back-end application, so that I can distribute to other locations. The app is stand-alone for each user; different data. Right now my Access app works with SQL server DEVELOPER edition, and my question is, how do I configure my Access app to talk to the SQL server in other locations, once replicated? My server name is "cwsqlserver" in my connection string; how will I know what other people's connection string data sources (server name) are? Do I force the naming of the server, and if so, how?

Thanks!
Christy.

 

[MoLaker]

The name of the SQL Server is the name of the server within the domain. So, it is not likely you will "force" anything there.

I also suspect distributing an application that uses SQL Server for a back-end is a much larger project than you were expecting. True, it is done all the time. But, it requires you to have your client create a DSN or input information which will create a DSN or DSN-less connection to SQL Server. Then your client or your application must create the tables in SQL Server. Your application will have to do this within the confines of the security permissions given to your client by his/her SQL Server administrator, which is not always what you might expect.

Personally, I recommend you distribute your application as an Access front-end and Access back-end with a process to convert the back-end to SQL Server if the client wants to and has the capability.

 

[cwarner]

MoLaker,

Having an Access back-end is not an option
for larger clientelle.

 

[MoLaker]

To re-address your original question concerning the SQL Server name, your connection string will have to use the client's server name. That means you will have to build in a method for the user to configure your application by entering their server name as well as any other connection information that might be required such as username and password. You cannot control the authorization method your client uses for SQL Server. You might want to consider using a DSN rather than a coded connection string, but even that would require a password and ID if SQL Server Authentication is used.

Good luck on your endeavor.