I have an Excel workbook with multiple sheets, each with its own Microsoft
Query. I have created a User Form to accept variables UsrID & Psw, which I
then plug into the connection string for each query within a For Each . . .
Next loop, as follows:

For Each Sht in .Sheets(Array(etc. . . ))
Sht.Activate
Range("A1").Select
With .Selection.QueryTable
.Connection = "ODBC;DSN=servername;UID=" _
& UsrID _
& ";Pwd=" _
& Psw _
& ";"
.Refresh.BackgroundQuery:=False
End With
Next Sht

The problem is, if the UsrID/Psw combination is not valid, each connection
attempt forces prompts for data source, ID & password.

Is there a way to validate UsrID & Psw before reaching this For Each . . .
Next loop, so that, if they are not valid, I will stay within a Do . . .
Until loop & reshow the User Form for re-entry?

I tried .DisplayAlerts = False before the .Refresh to see if there is an
error to trap.
It returns Run-time error '1004': General ODBC Error.
Is it sufficient to trap this error on the first refresh & have the User
Form accessed conditionally within this loop?
It seems too non-specific an error to assume this is the only reason it will
be thrown on the refresh???
--
Will

It's sort of funky that each user has his own database username and
password.

If you're using SQLServer why don't you use ActiveDirectory
Authentication?

Or why don't you have your database administrator set up an account
for all users.

That would make it so he wouldn't have to administrate permissions for
each user.

Also, your connection string looks funky. Have you considered getting
the format from a udl file?

The ODBC connection is to an IBM federated database. We were originally using
Kerberos authentication with it, where this would not be an issue, but,
unfortunately, that software has a few bugs that they have not seemed to work
out yet. So, for now, we have that software authenticating against
ActiveDirectory. As long as the user ID & password authenticate, there is no
problem making the connections & running the queries.
If you miskey the ID/password entry, as it loops thru each sheet & hits the
Refresh, you have to select the data source & enter the ID & password, over &
over & over. This is what I'm avoiding by having the user enter the user ID &
password one time, then plugging those into the connection string for each
query refresh.
The connection string itself works just fine, as long as the ID & password
are valid.
I want to authenticate them before I reach any of the queries. How can I
authenticate against ActiveDirectory from VBA?
--
Will


" wrote:

It's sort of funky that each user has his own database username and
password.

If you're using SQLServer why don't you use ActiveDirectory
Authentication?

Or why don't you have your database administrator set up an account
for all users.

That would make it so he wouldn't have to administrate permissions for
each user.

Also, your connection string looks funky. Have you considered getting
the format from a udl file?

Sorry. I couldn't get it.

This is as far as I got.

I can't figure out how to tell if you have a valid ADODB
connection ....


Sub t()
Dim cb As Boolean
cb = TestConnect("bobnet", "bobnet2005")
End Sub

Function TestConnect(username As String, password As String) As
Boolean
Dim m_connection As ADODB.Connection
Dim dbsource As String
dbsource = "Provider=IBMDADB2.1;Data
Source=SOMESCHEMA;Location=somemachine:9100"
username = "someusername"
password = "somepassword"
Set m_connection = CreateObject("ADODB.Connection")
m_connection.CommandTimeout = 2000
m_connection.Open dbsource, username, password
For Each proLoop In m_connection.Properties
MsgBox proLoop.Name & " " & proLoop.Attributes
Next proLoop
m_connection.Close
TestConnect = True
End Function

I found what I need, thanks to Walter Wang. It's in the 6th post at this link:

http://www.microsoft.com/communities...&cr=&sloc=&p=1

Thanks to you & to Peter for your efforts.
--
Will


" wrote:

Sorry. I couldn't get it.

This is as far as I got.

I can't figure out how to tell if you have a valid ADODB
connection ....


Sub t()
Dim cb As Boolean
cb = TestConnect("bobnet", "bobnet2005")
End Sub

Function TestConnect(username As String, password As String) As
Boolean
Dim m_connection As ADODB.Connection
Dim dbsource As String
dbsource = "Provider=IBMDADB2.1;Data
Source=SOMESCHEMA;Location=somemachine:9100"
username = "someusername"
password = "somepassword"
Set m_connection = CreateObject("ADODB.Connection")
m_connection.CommandTimeout = 2000
m_connection.Open dbsource, username, password
For Each proLoop In m_connection.Properties
MsgBox proLoop.Name & " " & proLoop.Attributes
Next proLoop
m_connection.Close
TestConnect = True
End Function

Assuming that your ODBC connection is through MS Query, all you have to do is
insert the connection string into the query file prior to calling it.

Perhaps something like...

XLODBC
1
DSN=yourdsn;UID=yourusername;PWD=yourpassword;

then commence the SELECT statement.

The only real issue here is that the user credentials are not secure, you
could have your code open the DQY file (as a text file) prior to calling the
query and write the connection string into it, then run the query and then
open it once more and remove it again, but you will still have to have a
means to gathering this information so that the VBA will know what to insert.

If you want to go to greater lengths, you could place the user name and
password in the registry and have the code dig it up so that it is not
immediately obvious, but ultimately still not burglar proof of course, but
then that's Excel!

Hope you have a win.

--
Regards - Peter


"wpiet" wrote:

I have an Excel workbook with multiple sheets, each with its own Microsoft
Query. I have created a User Form to accept variables UsrID & Psw, which I
then plug into the connection string for each query within a For Each . . .
Next loop, as follows:

For Each Sht in .Sheets(Array(etc. . . ))
Sht.Activate
Range("A1").Select
With .Selection.QueryTable
.Connection = "ODBC;DSN=servername;UID=" _
& UsrID _
& ";Pwd=" _
& Psw _
& ";"
.Refresh.BackgroundQuery:=False
End With
Next Sht

The problem is, if the UsrID/Psw combination is not valid, each connection
attempt forces prompts for data source, ID & password.

Is there a way to validate UsrID & Psw before reaching this For Each . . .
Next loop, so that, if they are not valid, I will stay within a Do . . .
Until loop & reshow the User Form for re-entry?

I tried .DisplayAlerts = False before the .Refresh to see if there is an
error to trap.
It returns Run-time error '1004': General ODBC Error.
Is it sufficient to trap this error on the first refresh & have the User
Form accessed conditionally within this loop?
It seems too non-specific an error to assume this is the only reason it will
be thrown on the refresh???
--
Will

Sorry Will, I hadn't read the last paragraph of your post.

I thought you were trying to avoid a login prompt for ODBC, still never
mind, seeing as how you are already prompting for user credentials, why not
stick them into the registry and then next time the user runs this little
gem, it will dig it out for them and only get upset if they have changed or
are otherwise missing, in which case you manage it through error handling.

You can run as many queries as you like assuming the data source will be the
same, otherwise offer a list and let the user choose.

Hope I got closer this time.
--
Regards - Peter


"Peter" wrote:

Assuming that your ODBC connection is through MS Query, all you have to do is
insert the connection string into the query file prior to calling it.

Perhaps something like...

XLODBC
1
DSN=yourdsn;UID=yourusername;PWD=yourpassword;

then commence the SELECT statement.

The only real issue here is that the user credentials are not secure, you
could have your code open the DQY file (as a text file) prior to calling the
query and write the connection string into it, then run the query and then
open it once more and remove it again, but you will still have to have a
means to gathering this information so that the VBA will know what to insert.

If you want to go to greater lengths, you could place the user name and
password in the registry and have the code dig it up so that it is not
immediately obvious, but ultimately still not burglar proof of course, but
then that's Excel!

Hope you have a win.

--
Regards - Peter


"wpiet" wrote:

I have an Excel workbook with multiple sheets, each with its own Microsoft
Query. I have created a User Form to accept variables UsrID & Psw, which I
then plug into the connection string for each query within a For Each . . .
Next loop, as follows:

For Each Sht in .Sheets(Array(etc. . . ))
Sht.Activate
Range("A1").Select
With .Selection.QueryTable
.Connection = "ODBC;DSN=servername;UID=" _
& UsrID _
& ";Pwd=" _
& Psw _
& ";"
.Refresh.BackgroundQuery:=False
End With
Next Sht

The problem is, if the UsrID/Psw combination is not valid, each connection
attempt forces prompts for data source, ID & password.

Is there a way to validate UsrID & Psw before reaching this For Each . . .
Next loop, so that, if they are not valid, I will stay within a Do . . .
Until loop & reshow the User Form for re-entry?

I tried .DisplayAlerts = False before the .Refresh to see if there is an
error to trap.
It returns Run-time error '1004': General ODBC Error.
Is it sufficient to trap this error on the first refresh & have the User
Form accessed conditionally within this loop?
It seems too non-specific an error to assume this is the only reason it will
be thrown on the refresh???
--
Will