Subdatasheet property

ray

I am at the moment manually setting Subdatasheet to [None] in all the
tables in this adp.

If anybody can put me out of my misery by suggesting some automated way
to do it, I would be most grateful. I can't work out how to get ADO or
the ADOX catalog to do it, and DAO doesn't connect to projects. I am
now up to table 75 out of 425 in Systest, and I will have to do it
again in UAT and Prod. Ewwwww. This is the thirty-eighth most boring
thing I have ever had to do, and I very luckily slept through the first
thirty seven.

Ray

Oct 11 '06 #1

Lyle Fairfield

ra*@aic.net.au wrote:

I am at the moment manually setting Subdatasheet to [None] in all the
tables in this adp.

If anybody can put me out of my misery by suggesting some automated way
to do it, I would be most grateful. I can't work out how to get ADO or
the ADOX catalog to do it, and DAO doesn't connect to projects. I am
now up to table 75 out of 425 in Systest, and I will have to do it
again in UAT and Prod. Ewwwww. This is the thirty-eighth most boring
thing I have ever had to do, and I very luckily slept through the first
thirty seven.

Ray

This is VAIRY (very air) untested code but you could try it; on a small
safe adp first would be best I think ... if that's not possible hard
code in a table name or two and experiment with that first

It will probably take several seconds per table, (depending on how long
it takes to open one of your tables).

Sub temp()
On Error Resume Next
Dim n As String
Dim r As ADODB.Recordset
Set r = CurrentProject.Connection.OpenSchema(adSchemaTable s)
Echo 0
With r
While Not .EOF
With DoCmd
n = r.Fields("Table_Name").Value
If Left(n, 3) <"sys" And Left(n, 2) <"dt" Then
.OpenTable n, acViewPreview, acEdit
.RunCommand acCmdSubdatasheetRemove
.Close acTable, n, acSaveYes
End If
End With
.MoveNext
Wend
End With
Echo 1
End Sub

acViewPreview may not work ... but I am pretty sure acViewNormal does
.... however acViewPreview is way faster than acView Normal so I would
try it first

Oct 11 '06 #2

ray

Lyle,

Thanks for your code - it *kind of* works! I think the light may be
visible at the end of the tunnel if you (or some other helpful soul)
could answer another question.

The code that you have suggested will set the SubDatasheet to [None] IF
THE SUBDATASHEET IS ALREADY SET TO SOME VALUE. If the Subdatasheet Name
= [Auto], then the code won't change it to [None].

So I think all I need to know is the syntax for DoCmd.RunCommand
acCmdInsertSubdatasheet.
Then I can just insert a subdatasheet name, remove it, and Robert's
your father's brother.

I've tried it with a table name, but no good so far. Do you happen to
know the syntax for it?

Thanks very much indeed,

Ray

>
This is VAIRY (very air) untested code but you could try it; on a small
safe adp first would be best I think ... if that's not possible hard
code in a table name or two and experiment with that first

It will probably take several seconds per table, (depending on how long
it takes to open one of your tables).

Sub temp()
On Error Resume Next
Dim n As String
Dim r As ADODB.Recordset
Set r = CurrentProject.Connection.OpenSchema(adSchemaTable s)
Echo 0
With r
While Not .EOF
With DoCmd
n = r.Fields("Table_Name").Value
If Left(n, 3) <"sys" And Left(n, 2) <"dt" Then
.OpenTable n, acViewPreview, acEdit
.RunCommand acCmdSubdatasheetRemove
.Close acTable, n, acSaveYes
End If
End With
.MoveNext
Wend
End With
Echo 1
End Sub

acViewPreview may not work ... but I am pretty sure acViewNormal does
... however acViewPreview is way faster than acView Normal so I would
try it first

Oct 11 '06 #3

Lyle Fairfield

ra*@aic.net.au wrote:

Lyle,

Thanks for your code - it *kind of* works! I think the light may be
visible at the end of the tunnel if you (or some other helpful soul)
could answer another question.

The code that you have suggested will set the SubDatasheet to [None] IF
THE SUBDATASHEET IS ALREADY SET TO SOME VALUE. If the Subdatasheet Name
= [Auto], then the code won't change it to [None].

acViewPreview may not work ... but I am pretty sure acViewNormal does
... however acViewPreview is way faster than acView Normal so I would
try it first

Did you try acViewNormal? That seemed to clear everything for me, but
the SubDataSheet is so slippery it's hard to tell.

Oct 11 '06 #4

ray

Yep, I used:

.OpenTable n, acViewNormal, acEdit
.RunCommand acCmdSubdatasheetRemove
.Close acTable, n, acSaveYes

If the Subdatasheet Name was set to the name of a table, then this code
changes the Subdatasheet Name to [None].

If the Subdatasheet Name was set to [Auto], then it makes no
difference.

Darn!

Thanks - Ray

Oct 11 '06 #5

ray

Just as a matter of interest, I am now trying this, which seems
promising. It sets the Subdatasheet Name to the first table in the
list, and then removes it. The Sendkeys statements come before the
Runcommand, a suggestion I saw posted elsewhere in this newsgroup. It
looks weird but it works.

Sub TurnOffSubDataSheets(Optional parRemove As Boolean = True)
On Error Resume Next
Dim n As String
Dim r As ADODB.Recordset
Set r = CurrentProject.Connection.OpenSchema(adSchemaTable s)
Echo 0
With r
While Not .EOF
With DoCmd
n = r.Fields("Table_Name").value
If Left(n, 3) <"sys" And Left(n, 2) <"dt" Then
.OpenTable n, acViewNormal, acEdit
SendKeys "{TAB}"
SendKeys "{DOWN}"
SendKeys "{UP}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{ENTER}"
.RunCommand acCmdInsertSubdatasheet
.Close acTable, n, acSaveYes
.OpenTable n, acViewNormal, acEdit
.RunCommand acCmdSubdatasheetRemove
.Close acTable, n, acSaveYes
End If
End With
.MoveNext
Wend
End With
Echo 1
End Sub

Oct 13 '06 #6

Wayne Gillespie

On 10 Oct 2006 21:13:12 -0700, ra*@aic.net.au wrote:

>I am at the moment manually setting Subdatasheet to [None] in all the
tables in this adp.

If anybody can put me out of my misery by suggesting some automated way
to do it, I would be most grateful. I can't work out how to get ADO or
the ADOX catalog to do it, and DAO doesn't connect to projects. I am
now up to table 75 out of 425 in Systest, and I will have to do it
again in UAT and Prod. Ewwwww. This is the thirty-eighth most boring
thing I have ever had to do, and I very luckily slept through the first
thirty seven.

Ray

Function TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property

Dim propName As String
Dim propType As Integer
Dim propVal As String

Dim strS As String

Set MyDB = CurrentDb

propName = "SubDataSheetName"
propType = 10
propVal = "[NONE]"

On Error Resume Next

For I = 0 To MyDB.TableDefs.Count - 1

If (MyDB.TableDefs(I).Attributes And dbSystemObject) = 0 Then

If MyDB.TableDefs(I).Properties(propName).Value <propVal Then
MyDB.TableDefs(I).Properties(propName).Value = propVal
intChangedTables = intChangedTables + 1
End If

If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs(I).CreateProperty(propName)
MyProperty.Type = propType
MyProperty.Value = propVal
MyDB.TableDefs(I).Properties.Append MyProperty
Else
If Err.Number <0 Then
MsgBox "Error: " & Err.Number & " on Table " _
& MyDB.TableDefs(I).Name & "."
MyDB.Close
Exit Function
End If
End If

End If
Next I

MsgBox "The " & propName & _
" value for all non-system tables has been updated to " & propVal & "."

MyDB.Close

End Function

Wayne Gillespie
Gosford NSW Australia

Oct 13 '06 #7

ray

Thanks Wayne! But no go, I'm afraid. From what I can work out, an adp
can't be manipulated through DAO. Or at least, I can't get DAO code
like this to work.

How's sunny Gosford?

Ray
Berowra

Oct 17 '06 #8

Wayne Gillespie

On 17 Oct 2006 16:42:55 -0700, ra*@aic.net.au wrote:

Sorry I missed that it was an adp.

>Thanks Wayne! But no go, I'm afraid. From what I can work out, an adp
can't be manipulated through DAO. Or at least, I can't get DAO code
like this to work.

How's sunny Gosford?

Look out the window :-)

>
Ray
Berowra

Wayne Gillespie
Gosford NSW Australia

Oct 18 '06 #9