I am using NotesSQL 3.02i to retrieve data from a notes database.
The data is retrieved into a spreadsheet using the following VBA code using ADO:
Public Function SQLDataRetriever(DSN As String, UID As String, PWD As String, _
SQL As String, ByVal targetRange As Range, Optional getColumnHeaders = False)
'This function uses ADO to retrieve data from a database
' and save it in a user defined position.
'DSN = Name of System DSN
'UID, PWD = User ID and Password of database user
'SQL = SQL to be executed
'targetRange = Upper left cell of area to place results in
'getColumnHeaders = Print database column headers, true or false
Dim objConn, objRec 'ADODB Objects
Dim Counter As Integer 'Counters
Set targetRange = targetRange.Cells(1, 1) 'Fixed to ignore multiple cells in selection
On Error GoTo Failure
Set objConn = CreateObject("ADODB.Connection") 'Set Connection Object
Set objRec = CreateObject("ADODB.Recordset") 'Set Recordset Object
'Open Connection
On Error GoTo connectionFailure
objConn.Open "DSN=" & DSN & ";UID=" & UID & ";PWD=" & PWD
'Execute SQL and save result in recordset
On Error GoTo SQLFailure
Set objRec = objConn.Execute(SQL)
On Error GoTo Failure
'Start output
'targetRange.Activate
'If titles are requested
If getColumnHeaders Then
For Counter = 0 To objRec.Fields.Count - 1
targetRange.Offset(0, Counter).Value = objRec.Fields(Counter).Name
Next
Set targetRange = targetRange.Offset(1, 0)
End If
'Dump Results
If Not objRec.EOF Then
objRec.MoveFirst
targetRange.CopyFromRecordset objRec
End If
'Close Connections
objRec.Close
Set objRec = Nothing
objConn.Close
Set objConn = Nothing
Exit Function
'Error Functions
connectionFailure: 'Error Catch at connection
Die ("Could not connect to database." & vbNewLine & vbTab & _
"1. Make sure that your machine is online" & vbNewLine & vbTab & _
"2. Make sure you are using the correct DSN (" & DSN & ")" & vbNewLine & vbTab & _
"3. Make sure username (" & UID & ") and pass (****) are correct." & vbNewLine & vbNewLine & _
"The error description is:" & vbNewLine & vbTab & _
Err.Description)
Exit Function
SQLFailure: 'Error Catch at SQL execution
Die ("Program failed executing SQL." & vbNewLine & _
"Make sure the syntax is correct." & vbNewLine & _
"The error description is:" & vbNewLine & vbTab & _
Err.Description)
Exit Function
Failure: 'Error Catch at everything else
Die ("Undefined Error." & vbNewLine & _
"The error description is:" & vbNewLine & vbTab & _
Err.Description)
Exit Function
End Function
All data is retrieved as expected BUT when the recordset is closed notessql will crash (taking lotus notes with it).
I have tried to introduce a waiting period after calling DoEvents but before closing anything to allow notessql to finish whatever it is doing after data retrieval to no avail.
I also tried not closing anything, however the crash still occurs when the function exits (probably because the recordset and conn set to nothing when they fall out of scope).
Obviously this is pure cosmetics HOWEVER it sure does make my handywork look VERY ugly - and people are not happy.
What can I do to fix this problem?
Best regards
Andreas Warberg