All:

I have an Excel-based validation application that queries an Oracle db
and exports the recordsets as text files that are imported into Access.
Once imported, the app runs several Access queries that join the newly
imported text files to linked DB2 tables. This, in turn, invokes the
DB2 Security model that prompts for a UserID and password.

The Oracle queries take a variety of different times and I'm looking
for a way to notify the user that the DB2 security model has been
invoked and they need to need to enter their userid and password to
complete the process.

Ideally, I'm looking for a way to manipulate the Excel icon in the
System Tray (API?) similar to how the Outlook icon flashes when you
start to send an e-mail from Excel, but I'm open to other ideas/Alerts.
Here's the code that calls the Access queries. Thanks for any help...

Sub AccessQuery(queryname As String, targetsheet As Worksheet,
outputcell As Range, Optional crng As Range, Optional Header As
Boolean)
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Time1 As Double

Application.StatusBar = "Query Start Time: " & Format(qTime,
"mm/dd/yyyy h:mm.ss AM/PM;@") & _
" - Running Access Query #: " & AccessQryCounter + 1 & ". Please
wait..."

'// Clear old data
Call ClearOldData(targetsheet, crng)

'// Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
dblocation & ";" & "Jet OLEDB:Database Password=Play123;"
Time1 = Now

'// Open the recordset---DB2 Security model invoked here.
rst.Open queryname, cnn, adOpenForwardOnly, adLockReadOnly,
adCmdStoredProc

If Header = True Then
'// Collect the field names and place them in the worksheet.
For i = 1 To rst.Fields.Count
targetsheet.Cells(outputcell.Row, outputcell.Column - 1 +
i).Value = rst.Fields(i - 1).Name
Next
'// Copy the recordset
targetsheet.Cells(outputcell.Row + 1,
outputcell.Column).CopyFromRecordset rst
Call HdrFormat(targetsheet, outputcell)
Else
'// Copy the recordset without headers
targetsheet.Cells(outputcell.Row + 1,
outputcell.Column).CopyFromRecordset rst
End If

'// Close the recordset and connection
rst.Close
cnn.Close
Call WriteLog(queryname, Time1, Now)

AccessQryCounter = AccessQryCounter + 1
End Sub

Hello Zachary,

Here are the API calls you will need to Flash the Application Title.
The macro Flash is a VBA routine. It should serve as model for you in
creating a suitable function for your needs.


Code:
--------------------

Public Declare Function GetActiveWindow _
Lib "User32.dll" () As Long

Public Declare Function Sleep _
Lib "Kernel32.dll" _
(ByVal dwMillisecs As Long) As Long

Declare Function FlashWindow _
Lib "User32.dll" _
(ByVal hWnd As Long, _
ByVal fInvert As Boolean) As Boolean

'The return value is the state of the window
'(active or inactive) prior to the call

Public Sub Flash()

Dim I As Integer
Dim Status As Long

For I = 1 To 7
Status = FlashWindow(GetActiveWindow(), Not Status)
Sleep (250)
Next I

End Sub

--------------------

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=482351

Leith:

Thanks very much. That's just what I needed.

Zachary