Use of a Connection in Program Manager 1

[gaelin]

I'm confused as to how a connection (to a SQL Server database) works for other users of an application. I've created a working connection (using a data source name, not a connection string)in program manager and generated an executable to run the application. On my PC I can access SQL Server using this connection.

I have not tested others using the connection, but I assume they could not use this connection as they don't have a DSN established on their PC.

If my assumption is correct then how do others connect?

 

[MarciaAkins]

If my assumption is correct then how do others connect?

You have to create the DSN on each machine that needs it.

Information about system DSNs is stored in two sub keys of the “hkey_local_machine” root key. The first, “software\odbc\odbcinst.ini”, has one sub key for each driver which lists the settings for that driver. There is also a single sub-key, named “ODBC Drivers”, which lists all installed drivers and could be used to generate a pick-list of available drivers

The second key, named “software\odbc\odbc.ini”, has one sub key for each DSN that has been defined. Each sub key has entries for the DSN parameters and their values. To create a new DSN, all that is needed is to create and populate the appropriate sub key. An additional sub key, named ‘ODBC Data Sources’ contains the list of defined DSNs and this is used to populate the native ODBC Manager dialog.

You can programmatically create the required registry entries on the client machines that need the DSN.

Marcia G. Akins

http://www.TightlineComputers.com

 

[gaelin]

Some of the clients who run the application use Government PCs which prohibit changes to the registry by the user, so a registry update won't work in all cases.

If I use a connection string versus a DSN, how does that work? I've read you can issue a "DSN Less" string. When I tried that a dialog opens asking me to select a DSN. What happens when other try this connection string?

 

[MarciaAkins]

If I use a connection string versus a DSN, how does that work? I've read you can issue a "DSN Less" string. When I tried that a dialog opens asking me to select a DSN. What happens when other try this connection string?

I expect that this is because you defined you connection in the dbc to use a DSN. You have to define the connection in the dbc to use a connection string, but this poses security problems because the connection string is hard-coded in the dbc.



Marcia G. Akins

http://www.TightlineComputers.com

 

[Mike Lewis]

Gaelin,

In addition to Marcia's good advice, you have a couple of other optionss:

- Forget about DSNs and also about connection objects in the DBC. Instead, use SQLSTRINGCONNECT() to send the raw connection string to the server.

- Create a "file DSN". You do that in the Control Panel ODBC applet (select the third tab in the main window). That will create a text file containing your connection settings, which you can then distribute to your users.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[gaelin]

I did experiment with connection string (a DSNLess connection) but it seems to always open a dialog asking for a name of DSN. Maybe I'm doing something wrong.

I did find the text file with the connection settings...to continue your thought, add this file to the project and have the loader place the file in the \program files\common file\odbc\data sources folder?

 

[Mike Lewis]

Gaelin,

add this file to the project and have the loader place the file in the \program files\common file\odbc\data sources folder?

Can't say for sure, off hand, if that's the correct directory, but it looks likely.

But whether or not it's in the project is not relevant. The project is only relevant within VFP. ODBC doesn't know anything about it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[gaelin]

Mike,

Thanks for help, I've decided to use the raw connection string. I discovered the reason for the dialog box I was getting was due to my failure construct the string properly. Your mention of the text file for the ODBC parameter pointed that out to me. Thanks!!

 

[Mike Lewis]

Gaelin,

Glad to hear you got the connection string sorted out. That's generally the preferred approach.

Thanks for the star.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk