vba sql server connection

[zuEgg]

Hi,

i need to connect to a SQL Server db using an odbc data source and SQL
Server authentication. When i create the data source from the control
panel i set up the authentication with the SQL Server authentication
providing my id and pass.

The problems comes when i open the connection in the vba macro in
excel. it doesn't retrieve the informations about user and pass from
the data source and i don't want to put user and pass in the macro code
for security reasons...

The code of the connection setup:

cn.Open "PROVIDER=MSDASQL; DSN=data_source_name; DATABASE=db_name;
UID=; PWD=;"<---- these parameters are needed even if i've put them in
the data source definition.

Thanks for any hint!

Massimo

 

[Norman Yuan]

If you have enterd username/password when creating the DSN, you do not need
to supply them again in your VBA code's ConnectionString, the only thing you
need in your ConnectionString is the DSN:

cn.Open "MyDSN"

As long as the DSN is valid to current user (Systam DSN or an User DSN that
is for current user).

BTW, this NG is for MS Access. You may get better response from other
appropriate NGs.