473,326 Members | 2,192 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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
8 4961

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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Paul Malcomson | last post by:
Hi. I'm having terrible trouble with a form that displays several parent/child relationships at one time. It is a sales force hierarchy - Sales force, district, territory, sales rep are the...
1
by: astro | last post by:
I'm trying to get a subdataSheet embedded on a form which will be displayed in datasheet view. I setup the 'record source' for the form to a query on a table. I go into the query properties and...
2
by: James | last post by:
Hi I have set up a subform in datasheet mode containing a second subform also in datasheet mode in order to be able to open it as a subdatasheet. In my application I make the first subform...
2
by: karen scheu via AccessMonster.com | last post by:
I am having trouble lately opening my local tables. It is taking a a few seconds to open the table that has no records in it. I read that there is a bug with the AutoCorrect feature and so I set...
1
by: Jim M | last post by:
After learning that the 'subdatasheet name' property set to "" can adversely effect database speed, I wanted to send a small code snippent to my end users to reset the SubdatasheetName Property so...
0
by: ray | last post by:
Hi all, I have seen and tried some code posted here to reset the Subdatasheet property of Tables to NONE. It works fine in DAO, but I am using an ADP. So that means that the code won't work,...
0
by: burningthemidniteoil | last post by:
I have access 2002. I have a query that diplays a subdatasheet ( +/- subtrees are shown on the record), but when i make a datasheet form out of that query , it does not display the plus (+) and...
1
by: Fred Zuckerman | last post by:
I read in this group, that setting the subdatasheet property to for linked tables is recommended. In my database I have 4 linked tables. They are all ODBC links to views on a SQL server. I change...
1
by: Simon | last post by:
Dear reader, By default the "Subdatasheet name" of a table is set to . If I change the setting to and save the changes the table property Subdatasheet name is still set to .
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.