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

[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.Open Schema(adSchema Tables)
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 acCmdSubdatashe etRemove
.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

[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.RunComman d
acCmdInsertSubd atasheet.
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.Open Schema(adSchema Tables)
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 acCmdSubdatashe etRemove
.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

[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.

[ray]

Yep, I used:

.OpenTable n, acViewNormal, acEdit
.RunCommand acCmdSubdatashe etRemove
.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

[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 TurnOffSubDataS heets(Optional parRemove As Boolean = True)
On Error Resume Next
Dim n As String
Dim r As ADODB.Recordset
Set r = CurrentProject. Connection.Open Schema(adSchema Tables)
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 acCmdInsertSubd atasheet
.Close acTable, n, acSaveYes
.OpenTable n, acViewNormal, acEdit
.RunCommand acCmdSubdatashe etRemove
.Close acTable, n, acSaveYes
End If
End With
.MoveNext
Wend
End With
Echo 1
End Sub

[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 TurnOffSubDataS heets()
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 = "SubDataSheetNa me"
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(p ropName).Value <propVal Then
MyDB.TableDefs( I).Properties(p ropName).Value = propVal
intChangedTable s = intChangedTable s + 1
End If

If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs( I).CreateProper ty(propName)
MyProperty.Type = propType
MyProperty.Valu e = propVal
MyDB.TableDefs( I).Properties.A ppend 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

[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

[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