Link to home
Create AccountLog in
Avatar of St_Rand
St_Rand

asked on

Recordset is disconnected, but a connection to MS Access MDB still persists

I am trying to create a disconnected recordset against an MS Access database.  I have set all the properties as instructed via documentation and other posts, yet the .LDB file persists - indication an active connection to the database.  How can I have a truly disconnected recordset against an MS Access database.  I am not interested in copying a recordset by opening a recordset then sequentially copying the records to another recordset that I created from scatch - this is much too slow.  Here is some code I have tried:
  1.             Dim mrsGrid as New ADODB.Recordset
  2.            
  3.             myADOconnection.open
  4.              With mrsGrid
  5.                         .CursorLocation = adUseClient
  6.                         .CursorType = adOpenForwardOnly
  7.                        .Open strSQL, myADOconnection, adOpenStatic, adLockBatchOptimistic
  8.                       .MoveLast
  9.              End With
  10.            Set mrsGrid.ActiveConnection = Nothing
  11.            myADOconnection.Close
  12.            mrsGrid.Close

Yet, even though the connection is closed the .ldb file still is there?  Things to note:
1. before I run this code there is no connection to the mdb file (no ldb file exists).
2.  Upon stepping past line 3 a ldb file is created (as expected)
3.  Upon stepping past line 11, ldb file still exists - yet connection is close (and reports being closed properly - i.e., if I check at this point with a break and in the command window, I get adStateClosed for myADOconnection).  
4.  Not until I execute line 12 does the ldb close and go away?
So, how can I REALLY have a disconnected recordset.  You may be asking why is this necessary?  Because Access databases corrupt any chance they get and this is very bad for customers.  
Avatar of Markus Fischer
Markus Fischer
Flag of Switzerland image

From the sample provided (you close the connection and the object mrsGrid), the database should no longer be opened. However, from your comments, it appears you continue using the recordset object. If that is the case, the database will remain open. I'm afraid you will have to copy all information you need if you want a totally disconnected recordset. You seem to say that it's way too slow. Have you tried it?

I might be wrong, but I don't think you can entirely close the connection of an active recordset. Even with the dbOpenStatic argument, which does produce a snapshot (a static recordset).

(°v°)
Avatar of kdc415
kdc415

You can work around that by dumping the recordset into an array using .GetRows

kdc
Avatar of St_Rand

ASKER

harfang - I am sorry, my example is misleading, I do not actually close the recordset (mrsGrid) - I only provided that line to demonstrate that by closing the recordset, the connection to the database is completely stopped.   So, yes, I continue using the recordset  the real code is as follows:

  1.             Dim mrsGrid as New ADODB.Recordset
  2.            
  3.             myADOconnection.open
  4.              With mrsGrid
  5.                         .CursorLocation = adUseClient
  6.                         .CursorType = adOpenForwardOnly
  7.                        .Open strSQL, myADOconnection, adOpenStatic, adLockBatchOptimistic
  8.                       .MoveLast
  9.              End With
  10.            Set mrsGrid.ActiveConnection = Nothing
  11.            myADOconnection.Close

Yes, I have copied the recordset by using the followng method:

Public Sub CopyRecordSet(rsFrom As ADOR.Recordset, rsTo As ADOR.Recordset, _
    Optional ByVal StructureOnly As Boolean = False, _
    Optional ByVal StartCol As Long = 0 _
    )
    'This routine copies the data and structure from one Recordset object to another
    'without using any connection properties.  If StructureOnly is specified as 1
    'then the routine only copies the structure.  This is a sequential process and will be
    'markedly slow for large recordsets.
    Dim lngFlds As Long
   
    On Error GoTo ErrorEH
   
    If Not rsTo Is Nothing Then
        If rsTo.State = adStateOpen Then
            rsTo.Close
        End If
        Set rsTo = Nothing
    End If
    Set rsTo = New ADOR.Recordset
   
    With rsTo
        For lngFlds = StartCol To rsFrom.Fields.Count - 1
            .Fields.Append rsFrom.Fields(lngFlds).Name, _
                rsFrom.Fields(lngFlds).Type, _
                rsFrom.Fields(lngFlds).DefinedSize, _
                adFldIsNullable
        Next
        .Open
        If Not (rsFrom.EOF And rsFrom.BOF) Then
            rsFrom.MoveFirst
            Do While Not rsFrom.EOF
                .AddNew
                For lngFlds = StartCol To rsFrom.Fields.Count - 1
                    .Fields(rsFrom.Fields(lngFlds).Name).Value = rsFrom.Fields(lngFlds).Value
                Next
                .Update
                rsFrom.MoveNext
            Loop
        End If
    End With
End Sub

But this proves to be very slow when you need to produce very long lists, such as filling data grid where it is possible to need to display 1000 or more rows as my customer's applications demand.

Further, I guess I really don't understand what a "disconnected" recordset is if a connection still is opened against the MDB file.  And (as anyone who works with Access in networked environments knows) only opens the door for database corruption.  

I hope this states the problem a little more clearly.  Thanks for your response.
Avatar of St_Rand

ASKER

kdc415

I will try out your suggestion to see if I can work with the result.
In my experience, an open static recordset does not create data corruption. The worst that can happen is that the locking file remains open, and itself locked by the operating system. But the data is safe. You are really using the options adOpenStatic, adLockReadOnly (there is no locking under the optimistic mode for a static recordset).

The advantage is that you do not need to pull all the data from the recordset over the network. I don't know of a faster method than the one you have used, and I doubt you can really improve performance by using an array (which has other limitations anyway).

(°v°)
Avatar of St_Rand

ASKER

But, I am still confused as to either what a "disconnected" recordset truly is OR why the a connection persists even though I have closed my connection object AND set the "ActiveConnection" property to Nothing?  Am I missing something there?
By the way - I appreciate everyone's comments.

kdc415 - I have used the GetRows and it is interesting.  I ultimately need the data in a recordset object as my application uses a 3rd party control (GridEx) to display the data and this control does not take an array as a data source (I think).  Even if it does, the GetRows seems to only return the data and not the Field Names, types, etc...  

This is all interesting because the real problem I am trying to solve is Access database corruption across a network.  I would move to SQL Express, but that poses a whole other set of problems regarding Intellectual property and exposure of the mdf files.  Which I'd be happy to discuss, but that is a long winded topic.
> the real problem I am trying to solve is Access database corruption across a network

I have always used the same solution to solve that: ensure that all computers accessing the database have the same version of Access installed (same main version, same SP, same Jet SP if that's relevant), and avoid *all* design changes over the network (I design only on a local version, linked to a local backend if there is one).

Have you been able to narrow down the circumstances that generate corruption? And is it data corruption of VB corruption?

(°v°)
ASKER CERTIFIED SOLUTION
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks for stepping in, Leigh. As you see, this one is a bit above my head -- (^v°)
Bah!  As if that's even possible!
Perhaps just a bit to the side of where your head happens to be - but never above I'm very sure. :-)

(My pleasure - none the less).
Avatar of St_Rand

ASKER

Great comments! Thank you everyone.

Leigh - I will try to answer your questions / remarks.

"I presume that you've haulted your code somehow? " - Yes, I halt the code with a breakpoint.  I watch the ldb file form upon opening the connection.  Just for kicks, I close the connection before opening the recordset to ensure it closes and removes the ldb file (it does, this is pretty quick).  Then I re-open the connection and step through the code.  The ldb file never removes UNTIL I close the recordset (as in the original post - which I removed for clarity).  Again, as soon as I actually close the recordset, the ldb is removed.   So, I've narrowed it down that even though the "ActiveConnection" is set to Nothing,  the actual connection is closed, as long as the recordset created by that connection is open, the ldb file persists.

"Have you examined the recordset's connection object thereafter - to confirm that it is Nothing?" - I believe so, since it is not updateable (by the way I have tried all locktypes just to be sure).  But I am open to any testing suggestions with that.  I have also tried a MOVEFIRST/MOVELAST combination when not using forward only recordsets with the thinking that I am ensuring the entire recordset is loaded (as I have read of pecularities regarding this) before I set the "ActiveConnection" to Nothing.

Next up:  The connection code:

    With myADOconnection
         strConnect = "Provider=MSDataShape.1;"
         strConnect = strConnect & "Extended Properties=""Jet OLEDB:Compact "
         strConnect = strConnect & "Without Replica Repair=True"";"
         strConnect = strConnect & "Persist Security Info=True;"
         strConnect = strConnect & "User Id=Triton;Password=Tk~$7793gn@;"
         strConnect = strConnect & "Data Source="
         strConnect = strConnect & gstrPath & "\Triton.mdb;"
         strConnect = strConnect & "Jet OLEDB:System Database="
         strConnect = strConnect & strMDWPath & ";"
         strConnect = strConnect & "Data Provider=MICROSOFT.JET.OLEDB.4.0"                    
        .ConnectionString = strConnect
        .CursorLocation = adUseClient
        .Mode = adModeShareDenyNone + adModeReadWrite
        .Open
    End With

I may be marking myself as an idiot, but I am not familiar with the term "ULS"

"Such SQL Server security is the purview of the OS Server security AFAIK.  But is stronger than anything Jet can muster."  Even though I believe this, I have yet to find anyway around the Access workgroup security (MDW).  

Oh, and another side note - my application is written in VB6.  Access is used only to store the data.  Access itself is not ever required to be installed on any of my customers' computers - not even the runtime.  All data access is handled through ADO.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Ha - before I'd had a chance to play further with this... there it is.
Yep - the truth was hidden in the connection object.  It was the only thing that wasn't explicit and hence why I asked about it.
Well found article Markus.

The workaround already mentioned in this thread is an always handy ability of ADO (created recordsets) though it is always going to have overhead consequences to create and then fill it based on another recordset's data.
However the workaround suggested in the article is different (and reassuring that it works - I'd be deeply troubled if the connection bug could re-produce *then*).
Persisting a recordset to file (something I've played and manipulated a bit) allows you to effectively create a new recordset based on that data when you load it next.
The connection object is never specified - hence no residual connection is possible.

So where you currently have your recordset code you'd not include any of the creation of a new recordset, instead add to be the following

Dim mrsGrid as New ADODB.Recordset
             
             myADOconnection.open
             With mrsGrid
                        .CursorLocation = adUseClient
                        .Open strSQL, myADOconnection, adOpenStatic, adLockBatchOptimistic
                        Set .ActiveConnection = Nothing
                        .Save CurrentProject.path & "\TempRst.xml", adPersistXML
                        .Close
                        .Open CurrentProject.path & "\TempRst.xml"
                        Kill CurrentProject.path & "\TempRst.xml"
              End With
            myADOconnection.Close

I've always found it pretty quick to persist and load a rst - should be noticably quicker than a built recordset.
As Markus says though - do you need a shaped recordset?
I don't *think* the datagrid requires it.
(And yes - I'd understood that you were talking about from VB6 - I've tested from there before and again for you now ;-)
Avatar of St_Rand

ASKER

Hi Leigh and Markus - Halelujah!  A quick test proves the bug out with the MS DataShape provider.   I do need that but only in a small section of my program, so I can easily work around that.  I can't tell you how long I've been looking for this solution, I can't believe I missed it.  

This is the first time I've posted a question to EE, though I do use the resource often.  I'll bet this will be a help to all those people out there who have questions about copying recordsets.  Anyway, you both have been a big help.  Since I don't want to offend anyone please let me know how or if you guys would like to split the points.  Technically, Markus' answer is the solution I am looking for, but I can see that Leigh has put some good effort in this and his solution will be of use as well.  

Thanks again, this is really exciting for me!
And thanks to you for your feedback and your enthusiasm!

Please be certain that however you choose to close this question, we will not be offended. If you want to award my internet search with an assist, I'd be honored, but I do feel the answer came from Leigh's comments: assuring that, yes, it should be possible (which prompted me to try it out), and then inquiring about your connection string. If you wish to do so, use [Accept multiple answers], award most points to the main answer, and the remaining points to any other helpful comment.

I'll go play with Leigh's code now, and perhaps find some background information on shaped recordsets: this was a leaning experience.

Success with your project!
(°v°)
Markus see email coming shortly - shaped fun and such.