[AccessD] Persistent Connection question

[Dan Waters]

This is a procedure I wrote a while back to quickly set all tables to have
None in the Subdatasheet property.

'--------------------------------------------
Private Sub ChangeTableProperties()

    Dim tdf As TableDef
    Dim prp As DAO.Property
    Dim dbs As DAO.Database
    Dim stg As String
    Dim blnPropertyExists As Boolean
    Dim stgPropertyName As String
    Dim intPropertyType As Integer
    Dim varPropertyValue As Variant
    Dim intCount As Integer
    Dim intTableCount As Integer
    Dim var As Variant

    stgPropertyName = "SubDatasheetName"
    intPropertyType = dbText
    varPropertyValue = "[None]"

    Set dbs = CurDB

    intTableCount = dbs.TableDefs.Count
    var = SysCmd(acSysCmdInitMeter, "Setting " & stgPropertyName & "
Property", intTableCount)

    intCount = 0
    For Each tdf In dbs.TableDefs
        intCount = intCount + 1
        var = SysCmd(acSysCmdUpdateMeter, intCount)
        stg = tdf.Name
        If Left$(stg, 4) <> "MSys" Then
            For Each prp In tdf.Properties
                If prp.Name = stgPropertyName Then
                    blnPropertyExists = True
                    Exit For
                Else
                    blnPropertyExists = False
                End If
            Next
            If blnPropertyExists = False Then
                Set prp = tdf.CreateProperty(stgPropertyName,
intPropertyType, varPropertyValue)
                tdf.Properties.Append prp
            End If
        End If
    Next tdf

    dbs.TableDefs.Refresh

    var = SysCmd(acSysCmdRemoveMeter)
    MsgBox "Done!"

    Set prp = Nothing
    Set tdf = Nothing
    Set dbs = Nothing

    Exit Sub

EH:
    MsgBox Err.Number & ": " & Err.Description

End Sub

'--------------------------------------------

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Janet Erbach
Sent: Monday, April 06, 2015 3:05 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Persistent Connection question

The users certainly do notice a speed difference with the persistent
connection, that's for sure.  That, combined with changing the main tables
in the app to have 'none' for the subdatasheet name, have made a huge
difference.

But this flaky behavior when running the app from the menu has eaten up
almost all of my time for the last 3 weeks and it seems it was all because
of that line of code when the form opened.  I wonder if it was because the
variable was NOT a static one...

Anyway, things have settled down now that I've bound the form to the backend
by way of keeping the connection persistent.



On Sat, Apr 4, 2015 at 11:17 PM, Jim Lawrence <accessd at shaw.ca> wrote:

> Brilliant.
>
> Jim
>
> ----- Original Message -----
> From: "Janet Erbach" <jerbach.db at gmail.com>
> To: "Database Advisors" <accessd at databaseadvisors.com>
> Sent: Friday, April 3, 2015 1:40:52 PM
> Subject: [AccessD] Persistent Connection question
>
> Hello all -
>
> I've finally discovered what's been causing extremely flaky behavior 
> in a database I've been wrestling with for the past 3 weeks.  It was 
> one line of code in the menu form that opens with the app:
>
> Dim dbsAlwaysOpen As DAO.Database
> Set dbsAlwaysOpen =
> OpenDatabase("S:\MAINT\ToolingTech\dbToolingTechData.accdb", False)
>
> Some forms were not populating consistently, records were sometimes 
> being dropped during select and insert queries, other forms needed to 
> be manually refreshed repeatedly in order to display all records, things
like that.
> The app worked fine when opened in design view - not when run from the 
> menu.
>
> This morning I bound the menu form to a small, 'fake' table in the 
> backend and everything seems back to normal now.
>
> Why would this method of establishing a persistent connection cause 
> issues?  I found code like this on a couple of websites when I was 
> pursuing serious speed issues with the app.
>
> Thanks!
>
> Janet Erbach
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com