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