Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Unanswered: DAO tech question

    Hi all.

    Clarification question:

    If you open an Access database using the OpenDatabase method and not subsequently close it, does the internal connections get consumed? This does happen with ADO ... Ex:

    private function Test()
    Dim db as database

    Set db=OpenDatabase("C:\somepath\Somedb.mdb")

    End function

    So, I'm deliberately trying to blow out the DB by using up all of the concurrent connections available within Access ...

    Any thoughts?

    - Mike
    Back to Access ... ADO is not the way to go for speed ...

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    UPDATE: No success. Anyone else who can reliably blow an access 97 database with a 3343 RTE? That is a "Unrecognized Database Format" error ... Give me a post if you can ...

    - Mike
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    UPDATE:

    Now this is interesting ... Just added an ADO routine to open and not close an ADO connection to the db which ran for 5000 times and the db did not blow up ... Hmmmm ... Looks like that 255 concurrent connections limit is strarting to look broken assuming that when each connection is lost to the connection variable going out of scope that the BE connection remains open (and consumed) ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - so I did get what you are trying to do afterall.
    Quote Originally Posted by M Owen
    assuming that when each connection is lost to the connection variable going out of scope that the BE connection remains open (and consumed) ...
    Out of curiosity - why are you assuming this? Don't the cleaning up routines handle that (inlcuding invoking .Close)? I thought all the explicit .Close and Set x = Nothing was just belt and braces good practice.
    Last edited by pootle flump; 07-13-06 at 11:45.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Mike

    Played around.

    SQL Server has a handy sproc - sp_who2. This shows all connections to the SQL Server and what they are doing.

    Code:
    Dim aConn_Persist As ADODB.Connection
    Const CON_STRING As String = "Driver={SQL Server};Server=(Local);Database=Board;Trusted_Connection=yes"
     
    Sub OpenPersistADO()
     
        Call ClosePersisADO
     
        Set aConn_Persist = New ADODB.Connection
     
        aConn_Persist.Open CON_STRING
     
    End Sub
     
    Sub ClosePersisADO()
     
        If Not aConn_Persist Is Nothing Then
     
            If aConn_Persist.State <> adStateClosed Then
     
                aConn_Persist.Close
     
            End If
     
            Set aConn_Persist = Nothing
     
        End If
     
    End Sub
     
    Sub OpenShortScopeADO()
     
        Dim aConn_Scope As New ADODB.Connection
        aConn_Scope.Open CON_STRING
     
    End Sub
    If I execute OpenPersistADO I can see myself connected to SQL Server.
    If I execute ClosePersisADO I no longer see myself connected to SQL Server.
    If I execute OpenShortScopeADO I don't see myself connected to SQL Server (although I can if I put a breakpoint on Exit Sub and check before the sub finishes).

    The connection dies as the object goes out of scope.

    Any use?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Ah - so did get what you are trying to do afterall.
    Out of curiosity - why are you assuming this? Don't the cleaning up routines handle that (inlcuding invoking .Close)? I thought all the explicit .Close and Set x = Nothing was just belt and braces good practice.
    Dan, Noooooooo ... I'm looking to BLOW THE DAMN THING UP. I'm frustrated because it's not cooperating with me ...

    I'm not calling the Close method.

    I'm not cleaning up after the New with setting back to Nothing.

    The damn thing just will not bomb out on me ... When I don't want the thing to hose, MS does ... When I want it to, the little bastard won't ... Arrrrrgh!
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol

    Sounds to me like MS didn't foresee you wanting to keep that connection open and thinks it is "helping" by closing it for you.

    What about.... a class... with a collection.... and an "AddConnection" method.....accepting a connection as parameter..... if in your loop you invoked that method and your class slam dunked the connection into the collection..... each one would persist and you could blow up your db.... maybe
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Lol

    Sounds to me like MS didn't foresee you wanting to keep that connection open and thinks it is "helping" by closing it for you.

    What about.... a class... with a collection.... and an "AddConnection" method.....accepting a connection as parameter..... if in your loop you invoked that method and your class slam dunked the connection into the collection..... each one would persist and you could blow up your db.... maybe
    Ahhhh ... An array of connections (or collection if you will) that get assigned and remain open ... It just might work ... Thanks Dan
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    UPDATE:

    Well I got it to blow. Using the array idea I got it to blow with an error of 3048: Can't open more db's. So, got an error but not the right one ... Still on the hunt for 3343 RTE ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coo - mind posting your code? I couldn't get mine to work. I think I was passing copies of the pointer but not copies of the object (my collection had 20+ objects but only one "connection" ).

    Quote Originally Posted by MOwen
    So, got an error but not the right one ... Still on the hunt for 3343 RTE ...
    Oh dear - an error but the wrong one.

    I presume you are trying to recreate some client's error hmm? Or are you trying to nail DAO once and for all?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Coo - mind posting your code? I couldn't get mine to work. I think I was passing copies of the pointer but not copies of the object (my collection had 20+ objects but only one "connection" ).

    Oh dear - an error but the wrong one.

    I presume you are trying to recreate some client's error hmm? Or are you trying to nail DAO once and for all?
    Dan: Client error. Trying to dup then trap ... Here's my code:
    Code:
    Option Compare Database
    Option Explicit
    Dim dbarray(5000) As Database
    Dim Ndx As Integer
    'Dim db As Database
    
    Private Function BlowIt()
    '    Dim db1 As Database
        
    '    Set db1 = OpenDatabase("c:\Mike Misc\flatSecxsec.mdb")
        Set dbarray(Ndx) = OpenDatabase("c:\Mike Misc\flatSecxsec.mdb")
    
     '   db1.Execute "CREATE TABLE Nothing (Test TEXT(50));"
     '   db1.Execute "DROP TABLE Nothing;"
        
    End Function
    
    Private Sub ADOButton_Click()
        Dim ADOcn As ADOdb.Connection
        
        Set ADOcn = New ADOdb.Connection
        'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;
        ADOcn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\Mike Misc\flatSecxsec.mdb", , adCmdText
        
    End Sub
    
    Private Sub Exitbutton_Click()
        Dim i As Integer
        
    '    Set db = OpenDatabase("c:\Mike Misc\flatSecxsec.mdb")
        
        For i = 1 To 5000
            BlowIt
            'ADOButton_Click
            Ndx = i
            TxtLabel.Caption = "Iteration: " & i
            DoEvents
            If i = 254 Then Exit For
        Next
        
        'DoCmd.Close
        
    End Sub
    It's just a simple form with a button to do the dirty work ...
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ta for t'code - I see now why yours worked and mine pooped out.

    Hmmm - bit of a generic error message that - I don't envy you tracking that down.

    Dunno if you have something like this anyway - I wrote an error logging routine for a client db when I really couldn't get my head round the errors they were getting. More than happy to post if you like. It logs to text since my problem was also about connecting to the BE. I just called it from the error trap of all the "suspect" procs.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Ta for t'code - I see now why yours worked and mine pooped out.

    Hmmm - bit of a generic error message that - I don't envy you tracking that down.

    Dunno if you have something like this anyway - I wrote an error logging routine for a client db when I really couldn't get my head round the errors they were getting. More than happy to post if you like. It logs to text since my problem was also about connecting to the BE. I just called it from the error trap of all the "suspect" procs.
    Some history: I've run across the 3343 RTE numerous times where there was no obvious corruption of any records (at least none that the C & R reported in the MSysCompactError table) which led me to think that IF the DB had connections consumed and not released on the BE that that would also gen the 3343 ... This is what I saw ... The DB corrupted when all the connections were consumed for the next poor sucker trying to get in the db ... Everyone who was currently in it was as happy as a clam and didn't know anything was wrong ... I'm trying to dup that scenario since as far as I can tell, there is no actual physical corruption of any tables and this error seems to be popping up sporadically ...
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmm. So to confirm:
    BE and FE are 97? All clients use 97 only with no other Access\ runtime installations? All clients use DAO 3.51? There is no corruption of the BE? No corruption of the FE either?

    I haven't used a copy of A97 for about 5 years either so end of the road for me anyway I'm afraid. I wonder if Wayne would know some undocumented gotcha about this error.....

    Good luck
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Hmm. So to confirm:
    BE and FE are 97? All clients use 97 only with no other Access\ runtime installations? All clients use DAO 3.51? There is no corruption of the BE? No corruption of the FE either?

    I haven't used a copy of A97 for about 5 years either so end of the road for me anyway I'm afraid. I wonder if Wayne would know some undocumented gotcha about this error.....

    Good luck
    Yep. Actually BE is A97 ... FE is VB6 and yes, talking DAO 3.51 here ... Same driver and objects in VB6 ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •