Geeks With Blogs
Neil Smith [blog]code til ur fingers bleed

Aaaarrrghh, been asked to help out a colleague with a VB6 app which talks to MS Access using ADO Data Controls (ADODC) which bind to datagrids, the software has been increasingly unstable lately as the exe has grown in size, database corruptions are far more frequent than they used to be.  The first thing I noticed was how the ldb file was increasing in size by 64 bytes, ie a new connection, for every ADODC instance.  I have written lots of fairly large and stable vb6 exes over the years, mostly using DAO where one connection does all the work, 64 bytes per instance of the app.  Given that the maximum number of connections is 256 though in practice things tend to go badly wrong after about 200, I knew we had to get these ADODCs using the same connection.  To cut a long story short, here’s how you can get all your ADODC controls sharing the same connection..

 

Public Sub ADODCOpen(adodcany As Adodc, Optional spSQL As String, Optional dgany As DataGrid)

Dim ipRes As Integer

 

On Error GoTo ADODCOErr:

 

    Set rsG1 = New ADODB.RecordSet

    If Trim$(spSQL) = "" Then

        spSQL = adodcany.RecordSource

    End If

    rsG1.Open spSQL, frmMain!adoprefs.RecordSet.ActiveConnection, adOpenStatic, adLockOptimistic

    Set adodcany.RecordSet = rsG1

    If Not dgany Is Nothing Then

        Set dgany.DataSource = adodcany

    End If

ADODCExit:

    Exit Sub

 

ADODCOErr:

    ipRes = MsgBox("ADODCOpen RTE." & String$(2, vbCrLf) & Err & " - " & Error(Err), vbAbortRetryIgnore + vbCritical, App.ProductName)

    If ipRes = vbRetry Then

        Resume

    ElseIf ipRes = vbIgnore Then

        Resume Next

    Else

        Resume ADODCExit

    End If

End Sub

 

Instead of using…

 

adodcAny.Refresh ‘ Will always create new database connection

 

use…

 

ADODCOpen adodcAny ‘ Shares connection frmMain!adoprefs which is opened on startup.

 

Bingo, for the whole app, ldb increases by just 64 bytes and corruptions /automation errors / unspecified errors / too many connections errors are now a rare occurrence again.

Posted on Monday, October 9, 2006 8:16 AM | Back to top


Comments on this post: VB6->Access Corruptions

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Neil Smith | Powered by: GeeksWithBlogs.net