Access 2003 linked database not carrying over child table.

[strive4peace]

thanks, Gunny (hope you don't mind since you don't know me... your
reputation preceeds you and I have read some of your posts, they're great!)

I did a Google search on Allen's site and found this... not sure if this
is what you were thinking of...it is VERY interesting and I thank you
for the direction...

link: Database Issue Checker Utility by Allen Browne
http://allenbrowne.com/AppIssueChecker.html
"This free utility (125KB zipped) reports on potential issues with the
structure of Access databases. It makes no changes to the databases you
examine..."

It does include a check for the Subdatasheet property

after seeing what the property name was from Allen's code, I put this
little procedure together and tested it...

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Sub SetSubDatsheetNone()

'crystal 10-27-06
'strive4peace2006 at yahoo dot com

'set the Subdatasheet property to [None]
'in all user tables

Dim db As DAO.Database _
, tdf As DAO.TableDef _
, mProp As DAO.Property

Dim mCountDone As Integer _
, mCountChecked As Integer _
, mBoo As Boolean _
, mStr As String

'cheap but it works <g>
On Error Resume Next

mCountDone = 0
mCountChecked = 0
For Each tdf In CurrentDb.TableDefs
'skip Microsoft System tables
If Left(tdf.Name, 4) <> "Msys" Then

mBoo = False
mCountChecked = mCountChecked + 1
Err.Number = 0
mStr = tdf.Properties("SubdatasheetName")
If Err.Number > 0 Then
Set mProp = tdf.CreateProperty( _
"SubdatasheetName", dbText, "[None]")
tdf.Properties.Append mProp
mBoo = True
Else
'thanks, Allen!
If tdf.Properties("SubdatasheetName") <> "[None]" Then
tdf.Properties("SubdatasheetName") = "[None]"
mBoo = True
End If
End If
If mBoo = True Then
mCountDone = mCountDone + 1
End If
End If
Next tdf

Set mProp = Nothing
Set tdf = Nothing
Set db = Nothing

MsgBox mCountChecked & " tables checked" & vbCrLf & vbCrLf _
& "Reset SubdatasheetName property to [None] in " _
& mCountDone & " tables" _
, , "Reset Subdatasheet to None"

End Sub
'~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

care to post the code to reset all
table's subdatasheet property to [None]?

It's on Allen Browne's site. Sorry I don't have the URL to hand.

 

[strive4peace]

thanks, Gunny (hope you don't mind since you don't know me... your
reputation preceeds you and I have read some of your posts, they're great!)

I did a Google search on Allen's site and found this... not sure if this
is what you were thinking of...it is VERY interesting and I thank you
for the direction...

link: Database Issue Checker Utility by Allen Browne
http://allenbrowne.com/AppIssueChecker.html
"This free utility (125KB zipped) reports on potential issues with the
structure of Access databases. It makes no changes to the databases you
examine..."

It does include a check for the Subdatasheet property

after seeing what the property name was from Allen's code, I put this
little procedure together and tested it, so I thought I'd share it (this
look anything like yours, Bill?) ...

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Sub SetSubDatasheetNone()

'crystal 10-27-06
'strive4peace2006 at yahoo dot com

'set the Subdatasheet property to [None]
'in all user tables

Dim db As DAO.Database _
, tdf As DAO.TableDef _
, mProp As DAO.Property

Dim mCountDone As Integer _
, mCountChecked As Integer _
, mBoo As Boolean _
, mStr As String

'cheap but it works <g>
On Error Resume Next

mCountDone = 0
mCountChecked = 0
For Each tdf In CurrentDb.TableDefs
'skip Microsoft System tables
If Left(tdf.Name, 4) <> "Msys" Then

mBoo = False
mCountChecked = mCountChecked + 1
Err.Number = 0
mStr = tdf.Properties("SubdatasheetName")
If Err.Number > 0 Then
Set mProp = tdf.CreateProperty( _
"SubdatasheetName", dbText, "[None]")
tdf.Properties.Append mProp
mBoo = True
Else
'thanks, Allen!
If tdf.Properties("SubdatasheetName") <> "[None]" Then
tdf.Properties("SubdatasheetName") = "[None]"
mBoo = True
End If
End If
If mBoo = True Then
mCountDone = mCountDone + 1
End If
End If
Next tdf

Set mProp = Nothing
Set tdf = Nothing
Set db = Nothing

MsgBox mCountChecked & " tables checked" & vbCrLf & vbCrLf _
& "Reset SubdatasheetName property to [None] in " _
& mCountDone & " tables" _
, , "Reset Subdatasheet to None"

End Sub
'~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*

care to post the code to reset all
table's subdatasheet property to [None]?

It's on Allen Browne's site. Sorry I don't have the URL to hand.

 

[Guest]

Hi Bill,

I've seen [none] change to [auto] whenever a table is opened in design view.

Do you have Name Autocorrupt disabled?

Otherwise, when this has happened to me, I have imported all objects into a
new DB container (after disabling Name Autocorrect), and this seems to work
for me.


Tom Wickerath
Microsoft Access MVP

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

Crystal

I've seen [none] change to [auto] whenever a table is opened in design view.
Bugs the heck out of me. I finally just created a procedure to reset all the
tables' subdatasheet to none and put a button on my custom developer
toolbar. I run it right before releasing a new user version of a database.

 

[Guest]

It's posted in two places that I know of:

Problem properties (See Tables: SubdatasheetName)
http://allenbrowne.com/bug-09.html

and

BUG: Slow performance on linked tables in Access 2002 and Office Access
2003
http://support.microsoft.com/?id=275085


Tom Wickerath
Microsoft Access MVP

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

care to post the code to reset all
table's subdatasheet property to [None]?

It's on Allen Browne's site. Sorry I don't have the URL to hand.

 

[strive4peace]

Thanks, Tom!

Warm Regards,
Crystal
*
have an awesome day
*

It's posted in two places that I know of:

Problem properties (See Tables: SubdatasheetName)
http://allenbrowne.com/bug-09.html

and

BUG: Slow performance on linked tables in Access 2002 and Office Access
2003
http://support.microsoft.com/?id=275085


Tom Wickerath
Microsoft Access MVP

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

care to post the code to reset all
table's subdatasheet property to [None]?

It's on Allen Browne's site. Sorry I don't have the URL to hand.

 

[aaron.kempf]

Tom;

I dont believe that Allen Brownes site is a CREDIBLE source of
information.

I mean; he's a pre-schooler that uses MDB format.
I reccomend that everyone stop using MDB because it is an obsolete
format; and it has been obsolete for almost a decade now.

-Aaron

It's posted in two places that I know of:

Problem properties (See Tables: SubdatasheetName)
http://allenbrowne.com/bug-09.html

and

BUG: Slow performance on linked tables in Access 2002 and Office Access
2003
http://support.microsoft.com/?id=275085


Tom Wickerath
Microsoft Access MVP

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

care to post the code to reset all
table's subdatasheet property to [None]?

It's on Allen Browne's site. Sorry I don't have the URL to hand.

 

[Guest]

Dear Tom Wickerath Microsoft Access MVP,

I have been reading this, having exactly the same problem. I tried this
"auto" option, but it didn't work out. (after having set the subdatasheet
name to "BE tablename", and closing the table, it indeed went back to "auto".
How to prevent this?