Removing SubDataSheets In 2003?

[(PeteCresswell)]

To remove subdatasheets, I've been using this code:

----------------------------------------------
Public Sub SubDataSheetZap()

Dim thisDB As DAO.Database
Dim curTD As DAO.TableDef
Dim newProp As DAO.Property
Dim i As Long

Const myNone = "[None]"
Const newPropName = "SubDataSheetname"

Set thisDB = CurrentDb()

SysCmd acSysCmdInitMeter, "Zapping SubDataSheet Names...",
thisDB.TableDefs.Count
For i = 0 To thisDB.TableDefs.Count - 1
Set curTD = thisDB.TableDefs(i)
If tablePropExist(newPropName, curTD) Then
curTD.Properties(newPropName) = myNone
Else
Set newProp = curTD.CreateProperty(newPropName, dbText, myNone)
curTD.Properties.Append newProp
Set newProp = Nothing
End If
SysCmd acSysCmdUpdateMeter, i
Next i

SysCmd acSysCmdRemoveMeter
Set curTD = Nothing
Set newProp = Nothing
End Sub
----------------------------------------------

Seems to me like it always worked in MS Access versions prior to 2003.

But under 2003, it seems spotty. Sometimes I'd swear that I ran the code,
checked the table, found it had the subdatasheets removed... and then at some
later date found subdatasheets back in the table.

Does this sound possible?

 

[Arvin Meyer [MVP]]

Does this sound possible?

I would have sworn someone at the company I worked for kept changing them
back.

I've noticed the same thing. Not just in Access 2003, but 2002 as well. It's
random, and more than likely a bug. The way to deal with it is probably to
run your code at each startup.

 

[Tony Toews [MVP]]

But under 2003, it seems spotty. Sometimes I'd swear that I ran the code,
checked the table, found it had the subdatasheets removed... and then at some
later date found subdatasheets back in the table.

Does this sound possible?

Yes, I've noticed the same after I go into a table in the back end in
design view.

So now I just rerun the code whenever the FE database is opened.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tom Wickerath]

Hi Pete,

You didn't mention this, but have you disabled Name Autocorrect? The
subdatasheet property will revert back to [Auto] if Name Autocorrect is
enabled. This means that if you use the Object Dependencies feature in A2003
(which requires one to have Name Autocorrect enabled) that your subdatasheets
can revert back to [Auto].

I've not had any problem (knock on wood) with the subdatasheet property
reverting back to [Auto] after I disable Name Autocorrect, and leave it
disabled.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[(PeteCresswell)]

I've not had any problem (knock on wood) with the subdatasheet property
reverting back to [Auto] after I disable Name Autocorrect, and leave it
disabled.

Sounds like something I should add to my little routine that zaps the
subdatasheets.

Anybody got a snippet? I guess it's a DB property...

 

[David W. Fenton]

Yes, I've noticed the same after I go into a table in the back end
in design view.

So now I just rerun the code whenever the FE database is opened.

Uh, aren't there independent settings on the front end table link
and on the back end tables? That is, don't you need to run it once
on the back end and once on the front end?

 

[Tom Wickerath]

Hi Pete,

You can try this add-in created by Access MVP Alex Dybenko:

AutoCorrectOff Add-in
http://www.pointltd.com/Products/Details.asp?dlID=50


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tony Toews [MVP]]

Uh, aren't there independent settings on the front end table link
and on the back end tables? That is, don't you need to run it once
on the back end and once on the front end?

Hmm, I never knew that. I'll have to check that one out.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[(PeteCresswell)]

You can try this add-in created by Access MVP Alex Dybenko:

AutoCorrectOff Add-in
http://www.pointltd.com/Products/Details.asp?dlID=50

Thanks.

Here's what I wound up with as my revised code.

Seems to work - and it can be run over-and-over again without
tripping over existing props.

---------------------------------------------------------------
Option Compare Database
Option Explicit

'Code to remove those performance-diminishing SubDataSheets

Public Sub SubDataSheetsZap()
' PURPOSE: To get rid of those performance-decreasing SubDataSheets that
' seem to default to "True" for each table
'
' NOTES: 1) Rumor has it that this operation must be peformed on BOTH
' the front end and the back end. Something about SubDataSheets
' also being properties of links separate from the actual table

Dim thisDB As DAO.Database
Dim curTD As DAO.TableDef
Dim newProp As DAO.Property

Dim i As Long
Dim curName As String

Const myNone As String = "[None]"
Const propName_DataSheet As String = "SubDataSheetname"
Const propName_AutoCorrect_Perform As String = "Perform Name AutoCorrect"
Const propName_AutoCorrrect_Track As String = "Track Name AutoCorrect Info"

Set thisDB = CurrentDb()

SysCmd acSysCmdInitMeter, "Zapping SubDataSheet Names...",
thisDB.TableDefs.Count
' -----------------------------------------------------
' First we take care of AutoCorrect, (AKA "AutoCorrupt")
' which somehow interacts with subdatasheets.

With thisDB
If dbPropExist(propName_AutoCorrect_Perform, thisDB) Then
.Properties(propName_AutoCorrect_Perform) = False
Else
Set newProp = .CreateProperty(propName_AutoCorrect_Perform, dbLong,
False)
.Properties.Append newProp
Set newProp = Nothing
End If

If dbPropExist(propName_AutoCorrrect_Track, thisDB) Then
.Properties(propName_AutoCorrrect_Track) = False
Else
Set newProp = .CreateProperty(propName_AutoCorrrect_Track, dbLong, False)
.Properties.Append newProp
Set newProp = Nothing
End If
End With

' -----------------------------------------------------
' The we loop through the tabledefs setting subdatasheets...

For i = 0 To thisDB.TableDefs.Count - 1
Set curTD = thisDB.TableDefs(i)
With curTD
curName = .Name
If ((Left$(curName, 3) = "tbl") _
Or (Left$(curName, 4) = "tlkp") _
Or (Left$(curName, 5) = "zmtbl") _
Or (Left$(curName, 5) = "zstbl")) Then
If tablePropExist(propName_DataSheet, curTD) Then
.Properties(propName_DataSheet) = myNone
Else
Set newProp = .CreateProperty(propName_DataSheet, dbText, myNone)
.Properties.Append newProp
Set newProp = Nothing
End If
End If
End With

SysCmd acSysCmdUpdateMeter, i
Next i

SysCmd acSysCmdRemoveMeter
Set curTD = Nothing
Set newProp = Nothing
Set thisDB = Nothing
End Sub

Private Function tablePropExist(ByVal thePropName As String, ByRef theTD As
DAO.TableDef) As Boolean
' PURPOSE: To determine if a property exists in a tabldef
' ACCEPTS: - Name of property we are checking on
' - Pointer to tabledef in question
' RETURNS: True if property exists, else False

Dim myProp As DAO.Property

On Error Resume Next
Set myProp = theTD.Properties(thePropName)
On Error GoTo 0

If Not myProp Is Nothing Then
tablePropExist = True
End If
End Function

Private Function dbPropExist(ByVal thePropName As String, ByRef theDB As
DAO.Database) As Boolean
' PURPOSE: To determine if a property exists in a database
' ACCEPTS: - Name of property we are checking on
' - Pointer to database opbject in question
' RETURNS: True if property exists, else False

Dim myProp As DAO.Property

On Error Resume Next
Set myProp = theDB.Properties(thePropName)
On Error GoTo 0

If Not myProp Is Nothing Then
dbPropExist = True
End If
End Function

 

[David W. Fenton]

Hmm, I never knew that. I'll have to check that one out.

It's also one of those default properties. If it doesn't exist, it
defaults to "[Auto]". And I don't know what relationship there is
for passing through a back end property if there's no property on
the front end link. It might be that setting it to NONE in the back
end will flow through to a front end like that has no property
setting (that would otherwise give you the default).

I mucked around with this at one point and don't recall ever
reaching any decisive conclusion on it.