Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB6.0 ADO question
Most of the following code was obtained from Microsoft. (Unsure what I've
changed.) However, it does not work correctly. I'm hoping someone can tell me why. It actually works until the "GetObjectContext.SetComplete" line us unremarked. It also bothers me that neither the recordset nor the connection are closed/terminated nicely. Public Function ListAll() As ADODB.Recordset Dim strSQL As String Dim rs As ADODB.Recordset Dim conn As ADODB.Connection On Error GoTo ErrorHandler 'Form the SQL SELECT statement strSQL = "SELECT CategoryId, Name, ModifiedDate, CreatedDate " & _ "FROM Categories ORDER BY Name" set rs = new ADODB.Recordset rs.CursorLocation = adUseClient 'Execute the SQL command to the database, using the recordset Open method. rs.Open strSQL, "FILEDSN=" & fileDSN, adOpenKeyset, adlLockReadOnly, adCmdText GetObjectContext.SetComplete Set ListAll=rs Exit Function ErrorHandler: If Not rs Is Nothing Then Set rs=Nothing End If 'Roll back MTS transaction set. Err.Raise Err.Number, SetErrSource(modName, "ListAll"), Err.Description GetObjectContext.SetAbort Err.Raise Err.Number, SetErrSource(modName, "ListAll"), Err.Description End Function Thanks in advance, Christmas May |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB6.0 ADO question
Hi Christmas May
You can close like this before Exit Sub/Function and the error handler 'close connection conn.Close Set ocnn = Nothing Set rs = Nothing Exit Sub/Function I don't have server but if you would like a sample to test that connects to Access's Northwind that does work post back and I'll post you one. It will work on server you will need to change connection and cat # ect. Good Luck TK "Christmas May" wrote: Most of the following code was obtained from Microsoft. (Unsure what I've changed.) However, it does not work correctly. I'm hoping someone can tell me why. It actually works until the "GetObjectContext.SetComplete" line us unremarked. It also bothers me that neither the recordset nor the connection are closed/terminated nicely. Public Function ListAll() As ADODB.Recordset Dim strSQL As String Dim rs As ADODB.Recordset Dim conn As ADODB.Connection On Error GoTo ErrorHandler 'Form the SQL SELECT statement strSQL = "SELECT CategoryId, Name, ModifiedDate, CreatedDate " & _ "FROM Categories ORDER BY Name" set rs = new ADODB.Recordset rs.CursorLocation = adUseClient 'Execute the SQL command to the database, using the recordset Open method. rs.Open strSQL, "FILEDSN=" & fileDSN, adOpenKeyset, adlLockReadOnly, adCmdText GetObjectContext.SetComplete Set ListAll=rs Exit Function ErrorHandler: If Not rs Is Nothing Then Set rs=Nothing End If 'Roll back MTS transaction set. Err.Raise Err.Number, SetErrSource(modName, "ListAll"), Err.Description GetObjectContext.SetAbort Err.Raise Err.Number, SetErrSource(modName, "ListAll"), Err.Description End Function Thanks in advance, Christmas May |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB6.0 ADO question
"Christmas May" wrote:
It also bothers me that neither the recordset nor the connection are closed/terminated nicely. You can close like this before Exit Sub/Function and the error handler 'close connection conn.Close Set ocnn = Nothing Set rs = Nothing TK's right: you *can* explicitly close and release your objects if it bothers you. But you shouldn't let it worry you if you don't. My experience of ADO is that if you let an active/open connection go out of scope the connection gets closed and released without any problems. That said, it's probably a good idea to close a connection when you are finished with it; for this reason I usually use disconnected recordsets. Setting to Nothing is a different matter. Unless you have a specific reason (e.g. reusing the variable and testing it for Nothing) there is no need to set the local variables to Nothing. VB's garbage collector will take care of them when they do out of scope. If you happened to use a With conn in your code it would create an 'invisible' Connection variable which you would be unable to explicitly set to Nothing. Would that bother you? Again, it shouldn't. I recently came across the following in Matt Curland's book, Advanced VB (p110): "[DAO provides] another example of poor teardown code. DAO has Close methods that must be called in the correct order, and the objects must be released in the correct order as well (Recordset before Database, for example). This single poor object model behavior has led to the misconception that VB leaks memory unless you explicitly set all the local variables to nothing at the end of a function. This is a completely false notion in a well-designed object model. VB can clear the variables faster at the End Sub line than you can from code, and it checks the variables even if you explicitly release your references. Any effort you make is duplicated." As I said before, my experience is that ADO *is* a well-designed object model meaning it doesn't matter in which order the Close methods are called or that they are explicitly called at all. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
Question | Charts and Charting in Excel |