[AccessD] Updating SQL Connections

[David Emerson]

Hi Listers,

I hope you are having good holidays.

Has anyone come across the problem where you want to be able to change SQL Connection strings on distributed databases but don’t know what the string is?  Here is the situation:

A database application is sold to a client.  It has an Access FE and an SQL BE.  I want to be able to link to the SQL BE in code without having to hard code or set up the connection strings before giving the application the client.  The idea is that the connection string information can be stored in a place separate from the actual SQL database, in a place where the client can enter the details, but at the same time is safe from being discovered.

This excludes saving the connection string information in the SQL Database because the Access FE doesn’t have the connection details to be able to connect to the SQL database to get the connection string details 😊

One idea is to store the information in a text file (encrypted of course) that the Access program can open first and check if the details have been updated.  If not then the Access program asks for the details, saves them to the file, then the file is used to get the connection string details for connecting the Access FE to the SQL backend.

Can anyone think of a better way of doing this, or any problems with this approach?

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand