[AccessD] Splitting 2002 data makes reports lock up

Greggs greggs at msn.com
Mon Dec 8 20:28:21 CST 2003


The original problem was in 2002.  I manually fixed that one.  I thought I
would try your code on another database but it is in 2K.  Sorry for the
confusion.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Monday, December 08, 2003 7:37 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Splitting 2002 data makes reports lock up


Um, I just looked at your message again.  You say 2k but your subject
says 2002.  Which one is it?  The constants are declare din the code and
2000 definitely has subdatasheets.

Charlotte Foust

-----Original Message-----
From: Greggs [mailto:greggs at msn.com]
Sent: Monday, December 08, 2003 5:30 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Splitting 2002 data makes reports lock up


Charlotte,

I get a compile error in 2K for....

If tdf.Properties(PROPNAME) = "[Auto]" Or _
                        Err = PROP_NOT_FOUND Then

Says "Constant Expression Required"

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Monday, December 08, 2003 11:40 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Splitting 2002 data makes reports lock up


It has to be turned off in the properties of the individual table. There
actually is some code in the help file, if you can find it, but here's
my routine.  This saves having to open each table in design view and
fiddle with it.  If you're using security on your tables, you'll need to
open a workspace using a login and password that has permission to make
design changes to the tables and then open the database using the
secured workspace.

Charlotte Foust

Public Function TurnOffSubdatasheets(strDBFile As String, _
                                    ParamArray TblName() As Variant) As
Boolean ' created by Charlotte Foust ' loops through tables in database
and turns off subdatasheets where the ' subdatasheet name property is
set to [Auto]

    On Error Resume Next

    Dim intLoop As Integer
    Dim intChanged As Integer
    Dim strTblName As String
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim prp As DAO.Property

    Const PROPNAME As String = "SubDataSheetName"
    Const PROPTYPE As Integer = dbText
    Const PROPVAL As String = "[NONE]"
    Const PROP_NOT_FOUND As Integer = 3270

    TurnOffSubdatasheets = True

    'If table name or names passed,
    'initialize the strTblName variable
    If Not IsMissing(TblName()) Then
        strTblName = TblName(0)
    End If

    Set dbs = DBEngine.Workspaces(0).OpenDatabase(strDBFile, True)

    If strTblName = "" Then
        'If no table names passed then
        'loop through the tabledefs collection
        For Each tdf In dbs.TableDefs
            'skip the system tables
            If Not (tdf.Attributes And dbSystemObject) Then
                If tdf.Properties(PROPNAME) = "[Auto]" Or _
                        Err = PROP_NOT_FOUND Then

                    If Err.Number = PROP_NOT_FOUND Then
                        Err.Clear
                        Set prp = tdf.CreateProperty(PROPNAME)
                        prp.Type = PROPTYPE
                        prp.Value = PROPVAL
                        tdf.Properties.Append prp
                    ElseIf Err.Number = 0 Then
                        tdf.Properties(PROPNAME).Value = PROPVAL
                    Else
                        TurnOffSubdatasheets = False
                        MsgBox "Error: " & Err.Number & " on Table " _
                                & tdf.Name & "."
                        dbs.Close
                        Exit Function
                    End If
                End If
            End If
        Next

    Else
        For intLoop = 0 To UBound(TblName)
            'loop through the passed table names
            'and set the subdatasheet name property
            strTblName = TblName(intLoop)
            Set tdf = dbs.TableDefs(strTblName)
            If Not (tdf.Attributes And dbSystemObject) Then
                If tdf.Properties(PROPNAME) = "[Auto]" Or _
                        Err = PROP_NOT_FOUND Then
                    If Err.Number = PROP_NOT_FOUND Then
                        Err.Clear
                        Set prp = tdf.CreateProperty(PROPNAME)
                        prp.Type = PROPTYPE
                        prp.Value = PROPVAL
                        tdf.Properties.Append prp
                    ElseIf Err.Number = 0 Then
                        tdf.Properties(PROPNAME).Value = PROPVAL
                    Else
                        TurnOffSubdatasheets = False
                        MsgBox "Error: " & Err.Number & " on Table " _
                                & tdf.Name & "."
                        dbs.Close
                        Exit Function
                    End If
                End If
            End If
        Next intLoop
    End If
Proc_exit:
    On Error Resume Next
    Set prp = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
End Function 'TurnOffSubdatasheets(ParamArray TblName() As Variant)


-----Original Message-----
From: Greggs [mailto:greggs at msn.com]
Sent: Monday, December 08, 2003 9:08 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Splitting 2002 data makes reports lock up


yeah

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mark Boyd
Sent: Monday, December 08, 2003 10:39 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Splitting 2002 data makes reports lock up


Charlotte -
Where do you turn off subdatasheets?
I'm curious if this will resolve a linked issue I'm having.

Thanks,
Mark Boyd
Sr. Systems Analyst
McBee Associates, Inc


-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: Monday, December 08, 2003 11:28 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Splitting 2002 data makes reports lock up

Did you remember to turn off subdatasheets on all those tables after you
split the database?  You probably need to do it on both front and back
end.  That should make a difference in performance with linked tables.

Charlotte Foust

-----Original Message-----
From: Greggs [mailto:greggs at msn.com]
Sent: Monday, December 08, 2003 7:38 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Splitting 2002 data makes reports lock up


I took a database designed by another individual and split it into a FE
and a BE.  Essentially I took all the tables except switchboard, moved
them to a new database and linked them back in.  Now some of the larger
reports lock up the system.  These reports take seconds to run in the
old database even though they are a couple hundred pages.  The symptoms
are the same on all systems. Any ideas?

Gregg Steinbrenner


_______________________________________________
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


_______________________________________________
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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list