[AccessD] Splitting 2002 data makes reports lock up

Greggs greggs at msn.com
Thu Dec 11 11:01:51 CST 2003


I opened a new database in 2K, copied everything from Public Function thru
End Function into a new Module, compiled, got an error on "Dim dbs As
DAO.Database", added the DAO reference, compiled and got the error below...

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

Says "Constant Expression Required"

Then... you won't believe this... I retraced my steps to write this email by
copying everything from module1 into module2, deleting module1, removed the
DAO reference, compiled, got the DAO error, added the reference, and.... it
compiled!  It will truly take a greater mind than mind to understand
that!!!!

Thanks for all your help Charlotte!


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


Then I don't know what the problem is.  You did copy the constant
declarations in the code, right?  I wrote this for A2k, so it should
work there.  You could try hard coding the property name into the test
to see which constant it isn't recognizing.

Charlotte Foust

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


I have the DAO 3.6 library referenced.

-----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:34 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Splitting 2002 data makes reports lock up


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
_______________________________________________
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