By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,395 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,395 IT Pros & Developers. It's quick & easy.

Subdatasheet property

P: n/a
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
Share this Question
Share on Google+
8 Replies


P: n/a

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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.