W
wpiet
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???
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???