[AccessD] Splitting 2002 data makes reports lock up

Charlotte Foust cfoust at infostatsystems.com
Mon Dec 8 19:33:40 CST 2003


Do you have a DAO reference set?  That's DAO code.  In 2002, you should
be able to run it without a DAO reference, but you have to substitute
late binding and declare the objects as Object rather that DAO.whatever.
I do have ADO code for this, but I don't think I have it on this
machine.

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


More information about the AccessD mailing list