Access and SQL Backend - Server Name for each client unique in DSNless connection str (1 Viewer)

[beachldy]

I have an Access 2007 application I'm going to distribute with a SQL Server 2005/2008 database as the back end.

When I distribute it, the Access ACCDE file has a DSNless connection. However, the server name may vary by client, though the database will be the same. I'll also use an Application Role so the client won't have to fool with permissions.

1) The problem is, each client has a different server name, and how can that be addressed in the connection to be unique for each client?

2) For the Application role, I've heard about running "sp_setapprole" and it's something that needs to run before the actual connection? Can anyone elaborate or give an example?

Thanks for much in advance.

 

[Galaxiom]

Put the server name and path in a front end table. When the first form loads set a global variable to open a recordset on the table and read this information.

Build the connection strings using the variable.

This way the server name can be set and saved in the initialisation of the program. It is even possible to preload the table with records so that a computer could look up its name or IP address or whatever to determine the allocated server name and path.

Opening of the first form for the first time can trigger another form to prompt for the user to enter the record for their environment. Somewhere you should also provide a form that enables the location to be changed. This form would also be triggered if the connection path is not found.

It is all pretty intuitive if structured sensibly.