Access 2003 linked database not carrying over child table.

[JRSellers]

Recently upgraded to Office 2003, and useing access 2003 with my access 2000
database file. The information is all visible in the original database, but
if i make another database file with it linked to it. The child table <+> is
not there. So you can see the <+> in the original db file, but not in a
linked. I dont understand why, since linked tables are merely a "shortcut to"
the database.
I have 2 tables linked to each other, then create links to the table via
another db. but the links dont show the child menu, but the orignal file does.
Any help would be much appreciated.

 

[strive4peace]

Hi JR,

Subdatasheet is a property of the table ... go to the design of the
table and turn on the Properties window (View, Properties from the menu)

Try setting Subdatasheet Name to [Auto] or specifically choose a table

here is a link I found with Google:

http://www.vb123.com/Toolshed/00_access/subdatasheet.htm


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

 

[Tom Wickerath]

Hi JR,

The + signs that you describe are known as Subdatasheets, a *feature* that
was introduced with Access 2000. The display of Subdatasheets does not appear
to be supported for linked tables. I suspect that you would have gotten the
same result if you had tested using Access 2000.

In order to have a subdatasheet display, you need to define a Link Child
field and a Link Master field. This is done automatically for you, if you
create a one-to-many relationship between two tables in the Tools >
Relationships view. However, relationships do not exist between linked tables
in a front-end application. They can only be created and enforced in the same
database. You can also define Subdatasheets when a table is open in normal
preview mode, by using Insert > Subdatasheet... However, notice that this
option is grayed out (ie. unavailable) for linked tables.

Here is a good reason to wean yourself of Subdatasheets:
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
__________________________________________

 

[strive4peace]

Thanks, Tom, I was looking for that link...

Using 2003, I can see the Subdatasheet with linked tables if the BE
tables are set up for it. But I never use that feature... usually turn
it off.

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

 

[JRSellers]

Hi JR,

The + signs that you describe are known as Subdatasheets, a *feature* that
was introduced with Access 2000. The display of Subdatasheets does not appear
to be supported for linked tables. I suspect that you would have gotten the
same result if you had tested using Access 2000.

In order to have a subdatasheet display, you need to define a Link Child
field and a Link Master field. This is done automatically for you, if you
create a one-to-many relationship between two tables in the Tools >
Relationships view. However, relationships do not exist between linked tables
in a front-end application. They can only be created and enforced in the same
database. You can also define Subdatasheets when a table is open in normal
preview mode, by using Insert > Subdatasheet... However, notice that this
option is grayed out (ie. unavailable) for linked tables.

Here is a good reason to wean yourself of Subdatasheets:
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
__________________________________________

 

[Tom Wickerath]

Hi Crystal,

Using 2003, I can see the Subdatasheet with linked tables if the BE
tables are set up for it.

I'm not sure how you are accomplishing this. I tried to do this, before
posting my initial reply, by linking to the Customers and Orders tables in a
clean (untouched) copy of Northwind. The subdatasheet displays fine when the
Customers table is opened from within the Northwind application. It does not
display when I open a linked table that points to this same Customers table.


Tom Wickerath
Microsoft Access MVP

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

 

[strive4peace]

Hi Tom,

did you re-set the link after changing it in the BE?

.... it does work... but NW is an old database... maybe it has gremlins
The BE format (and FE) I tested on is A2K (even though I am using
2003) -- maybe that makes a difference

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

 

[Tom Wickerath]

I didn't need to reset the link, because I created it from scratch to the
untouched copy of Northwind (which already includes defined subdatasheets). I
used the copy of Northwind that comes with Access 2003.

Interesting. I just created a brand new database using Access 2000. I linked
to my clean copy of Northwind, and sure enough, I do see the subdatasheets!
I then created a brand new database in Access 2003 and linked to the same
clean copy of Northwind. I do not see the subdatasheets. Finally, I created a
brand new database using Access 2002 and linked as before. I do see the
subdatasheets. So, the OP appears to have a valid complaint with Access 2003
not showing subdatasheets in linked tables. Can you try using Access 2003 to
see if you can get the subdatasheets with the linked tables?


Tom Wickerath
Microsoft Access MVP

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

 

[JRSellers]

Sorry thing cleared my box and posted.
I inhereted that database, and it was working until a few days ago. The
subsheets were showing up for 2003 for awhile then it stopped. A friend of
mine also uses the same database but uses 2000. Even if you create a new
database it shows it as a 2000 database file. Unless you do the conversion,
or so ive encountered. I guess whoever started this nightmare like subsheets
heh. However, i made copies of the original Db file and the orignal db link,
and tried to intergrate them into a 2k3 enviroment, but still the subsheets
didnt show. And yeah it is wierd that i can see the subsheets in 2002<xp> but
not 2003? ha!
Im not sure what Crystal meant by BE tables. Im a techie but my acronym
library is only so big My brain hurts.

 

[Tom Wickerath]

Hi JR,

Im not sure what Crystal meant by BE tables. Im a techie but my acronym
library is only so big My brain hurts.

BE is shorthand for Back-end database. It refers to the actual database
where the tables reside. Likewise, FE is shorthand for Front-end database.
When one splits a database using the database splitter wizard (Tools >
Database Utilities > Database Splitter), they end up with two databases: a FE
..mdb file (contains ALL objects [queries, forms, reports, macros & modules],
but only links to the tables) and a BE .mdb file (contains only the tables).


Tom Wickerath
Microsoft Access MVP

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

 

[strive4peace]

Hi Tom,

sure!

In 2003 format

no + in BE if relationship is not set and [Auto] is chosen

defined a simple relationship between the tables and now BE has +

made FE database and linked to the tables

in Table1, Subdatasheet is set to Auto --> FE doesn't get +

changed BE Table1 design
--> set Subdatasheet to a specific tablename and refreshed the link, the
FE gets a +

conclusion: don't use [Auto] in the Subdatasheet property, specify the
tablename to expand in databases that will be a BE in 2003

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

 

[Tom Wickerath]

Hi Crystal,

Very interesting. I could not get the Subdatasheet Name of Table.Orders to
"stick" (it kept reverting to [Auto]) until I disabled the Name Autocorrupt
option. (Like I said, I was using a "clean", unmodified, copy of Northwind).

Anyway, after successfully changing this setting, I am now able to see the
subdatasheet in a linked table, in Access 2003. I had to also link to the
Orders table, in addtion to the Customers table.

Thanks for figuring this little SNAFU out!


Tom Wickerath
Microsoft Access MVP

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

Hi Tom,

sure!

In 2003 format

no + in BE if relationship is not set and [Auto] is chosen

defined a simple relationship between the tables and now BE has +

made FE database and linked to the tables

in Table1, Subdatasheet is set to Auto --> FE doesn't get +

changed BE Table1 design
--> set Subdatasheet to a specific tablename and refreshed the link, the
FE gets a +

conclusion: don't use [Auto] in the Subdatasheet property, specify the
tablename to expand in databases that will be a BE in 2003

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

I didn't need to reset the link, because I created it from scratch to the
untouched copy of Northwind (which already includes defined subdatasheets). I
used the copy of Northwind that comes with Access 2003.

Interesting. I just created a brand new database using Access 2000. I linked
to my clean copy of Northwind, and sure enough, I do see the subdatasheets!
I then created a brand new database in Access 2003 and linked to the same
clean copy of Northwind. I do not see the subdatasheets. Finally, I created a
brand new database using Access 2002 and linked as before. I do see the
subdatasheets. So, the OP appears to have a valid complaint with Access 2003
not showing subdatasheets in linked tables. Can you try using Access 2003 to
see if you can get the subdatasheets with the linked tables?


Tom Wickerath
Microsoft Access MVP

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

 

[strive4peace]

Hi Tom,

you're welcome

btw, for some reson, the property reverts to [Auto]* when the table is
opened in Design if [None] had been chosen before... this was wierd!
But is does keep the tablename if one was specified.

I sent you email before I saw this message... guess you can disregard it.

* "Name Autocorrupt" ... haha, good one, Tom!

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

Hi Crystal,

Very interesting. I could not get the Subdatasheet Name of Table.Orders to
"stick" (it kept reverting to [Auto]) until I disabled the Name Autocorrupt
option. (Like I said, I was using a "clean", unmodified, copy of Northwind).

Anyway, after successfully changing this setting, I am now able to see the
subdatasheet in a linked table, in Access 2003. I had to also link to the
Orders table, in addtion to the Customers table.

Thanks for figuring this little SNAFU out!


Tom Wickerath
Microsoft Access MVP

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

Hi Tom,

sure!

In 2003 format

no + in BE if relationship is not set and [Auto] is chosen

defined a simple relationship between the tables and now BE has +

made FE database and linked to the tables

in Table1, Subdatasheet is set to Auto --> FE doesn't get +

changed BE Table1 design
--> set Subdatasheet to a specific tablename and refreshed the link, the
FE gets a +

conclusion: don't use [Auto] in the Subdatasheet property, specify the
tablename to expand in databases that will be a BE in 2003

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

I didn't need to reset the link, because I created it from scratch to the
untouched copy of Northwind (which already includes defined subdatasheets). I
used the copy of Northwind that comes with Access 2003.

Interesting. I just created a brand new database using Access 2000. I linked
to my clean copy of Northwind, and sure enough, I do see the subdatasheets!
I then created a brand new database in Access 2003 and linked to the same
clean copy of Northwind. I do not see the subdatasheets. Finally, I created a
brand new database using Access 2002 and linked as before. I do see the
subdatasheets. So, the OP appears to have a valid complaint with Access 2003
not showing subdatasheets in linked tables. Can you try using Access 2003 to
see if you can get the subdatasheets with the linked tables?


Tom Wickerath
Microsoft Access MVP

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

:

Hi Tom,

did you re-set the link after changing it in the BE?

.... it does work... but NW is an old database... maybe it has gremlins
The BE format (and FE) I tested on is A2K (even though I am using
2003) -- maybe that makes a difference

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

 

[JRSellers]

Thanks Tom and Crystal. Im still not able to see the subsheet data in my DB
though. I have 2 tables. A Volume and Entries table. The subdata comes from
Entries Table to the Volume Table. The orginal db comes from access 2k.
Anyway, i changed the BE subsheetname to Table.Entries and the Link
child/master to changed to the field they are related by <in my case DLB Num>
Did that for each Table and changed to the appropriate subsheet name.
Then i created a new FE file and made the links to he BE for both Entries
and Volumes. I opened the design view and table properties and changed the
subsheet names from "Auto" to Tables.Entries and the link fields change to
the same as the BE file. So they all look the same as far as subsheet data
names, but it doesnt show still. I refreshed the links and even
re-established the relationships in both the FE and BE files, but its still
not showing. Did i miss a step?

Hi JR,

Im not sure what Crystal meant by BE tables. Im a techie but my acronym
library is only so big My brain hurts.

BE is shorthand for Back-end database. It refers to the actual database
where the tables reside. Likewise, FE is shorthand for Front-end database.
When one splits a database using the database splitter wizard (Tools >
Database Utilities > Database Splitter), they end up with two databases: a FE
.mdb file (contains ALL objects [queries, forms, reports, macros & modules],
but only links to the tables) and a BE .mdb file (contains only the tables).

 

[strive4peace]

Hi JR,

Since the table in the BE is defined to specifically use another table
for the subdatasheet, Access should respect that.

Open the BE database

Make sure the Name AutoCorrect option is not on

Tools, Options, General ... uncheck Track Name AutoCorrect

Open design of table and verify that the Subdatasheet property is filled
out with a specific tablename

Then, Compact/Repair BE and close it

Open the FE, try deleting the table link from the FE and then put it
back again

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

Thanks Tom and Crystal. Im still not able to see the subsheet data in my DB
though. I have 2 tables. A Volume and Entries table. The subdata comes from
Entries Table to the Volume Table. The orginal db comes from access 2k.
Anyway, i changed the BE subsheetname to Table.Entries and the Link
child/master to changed to the field they are related by <in my case DLB Num>
Did that for each Table and changed to the appropriate subsheet name.
Then i created a new FE file and made the links to he BE for both Entries
and Volumes. I opened the design view and table properties and changed the
subsheet names from "Auto" to Tables.Entries and the link fields change to
the same as the BE file. So they all look the same as far as subsheet data
names, but it doesnt show still. I refreshed the links and even
re-established the relationships in both the FE and BE files, but its still
not showing. Did i miss a step?

Hi JR,

Im not sure what Crystal meant by BE tables. Im a techie but my acronym
library is only so big My brain hurts.

BE is shorthand for Back-end database. It refers to the actual database
where the tables reside. Likewise, FE is shorthand for Front-end database.
When one splits a database using the database splitter wizard (Tools >
Database Utilities > Database Splitter), they end up with two databases: a FE
.mdb file (contains ALL objects [queries, forms, reports, macros & modules],
but only links to the tables) and a BE .mdb file (contains only the tables).

 

[Bill Mosca, MS Access MVP]

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.

--
Bill Mosca, MS Access MVP

Hi Tom,

you're welcome

btw, for some reson, the property reverts to [Auto]* when the table is
opened in Design if [None] had been chosen before... this was wierd! But
is does keep the tablename if one was specified.

I sent you email before I saw this message... guess you can disregard it.

* "Name Autocorrupt" ... haha, good one, Tom!

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

Hi Crystal,

Very interesting. I could not get the Subdatasheet Name of Table.Orders
to "stick" (it kept reverting to [Auto]) until I disabled the Name
Autocorrupt option. (Like I said, I was using a "clean", unmodified, copy
of Northwind).

Anyway, after successfully changing this setting, I am now able to see
the subdatasheet in a linked table, in Access 2003. I had to also link to
the Orders table, in addtion to the Customers table.

Thanks for figuring this little SNAFU out!


Tom Wickerath
Microsoft Access MVP

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

Hi Tom,

sure!

In 2003 format

no + in BE if relationship is not set and [Auto] is chosen

defined a simple relationship between the tables and now BE has +

made FE database and linked to the tables

in Table1, Subdatasheet is set to Auto --> FE doesn't get +

changed BE Table1 design
--> set Subdatasheet to a specific tablename and refreshed the link, the
FE gets a +

conclusion: don't use [Auto] in the Subdatasheet property, specify the
tablename to expand in databases that will be a BE in 2003

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



Tom Wickerath wrote:
I didn't need to reset the link, because I created it from scratch to
the untouched copy of Northwind (which already includes defined
subdatasheets). I used the copy of Northwind that comes with Access
2003.

Interesting. I just created a brand new database using Access 2000. I
linked to my clean copy of Northwind, and sure enough, I do see the
subdatasheets! I then created a brand new database in Access 2003 and
linked to the same clean copy of Northwind. I do not see the
subdatasheets. Finally, I created a brand new database using Access
2002 and linked as before. I do see the subdatasheets. So, the OP
appears to have a valid complaint with Access 2003 not showing
subdatasheets in linked tables. Can you try using Access 2003 to see if
you can get the subdatasheets with the linked tables?

Tom Wickerath
Microsoft Access MVP

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

:

Hi Tom,

did you re-set the link after changing it in the BE?

.... it does work... but NW is an old database... maybe it has
gremlins The BE format (and FE) I tested on is A2K (even though I
am using 2003) -- maybe that makes a difference

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

 

[JRSellers]

You are my hero! It works now. Many Many Thanks.

Thanks Tom and Crystal. Im still not able to see the subsheet data in my DB
though. I have 2 tables. A Volume and Entries table. The subdata comes from
Entries Table to the Volume Table. The orginal db comes from access 2k.
Anyway, i changed the BE subsheetname to Table.Entries and the Link
child/master to changed to the field they are related by <in my case DLB Num>
Did that for each Table and changed to the appropriate subsheet name.
Then i created a new FE file and made the links to he BE for both Entries
and Volumes. I opened the design view and table properties and changed the
subsheet names from "Auto" to Tables.Entries and the link fields change to
the same as the BE file. So they all look the same as far as subsheet data
names, but it doesnt show still. I refreshed the links and even
re-established the relationships in both the FE and BE files, but its still
not showing. Did i miss a step?

Hi JR,

Im not sure what Crystal meant by BE tables. Im a techie but my acronym
library is only so big My brain hurts.

BE is shorthand for Back-end database. It refers to the actual database
where the tables reside. Likewise, FE is shorthand for Front-end database.
When one splits a database using the database splitter wizard (Tools >
Database Utilities > Database Splitter), they end up with two databases: a FE
.mdb file (contains ALL objects [queries, forms, reports, macros & modules],
but only links to the tables) and a BE .mdb file (contains only the tables).

 

[strive4peace]

you're welcome, JR, happy to help glad you got it to work

.... you really should, as Tom said, "ween" yourself off opening Tables
directly...forms give you much more control

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

You are my hero! It works now. Many Many Thanks.

Thanks Tom and Crystal. Im still not able to see the subsheet data in my DB
though. I have 2 tables. A Volume and Entries table. The subdata comes from
Entries Table to the Volume Table. The orginal db comes from access 2k.
Anyway, i changed the BE subsheetname to Table.Entries and the Link
child/master to changed to the field they are related by <in my case DLB Num>
Did that for each Table and changed to the appropriate subsheet name.
Then i created a new FE file and made the links to he BE for both Entries
and Volumes. I opened the design view and table properties and changed the
subsheet names from "Auto" to Tables.Entries and the link fields change to
the same as the BE file. So they all look the same as far as subsheet data
names, but it doesnt show still. I refreshed the links and even
re-established the relationships in both the FE and BE files, but its still
not showing. Did i miss a step?

Hi JR,

Im not sure what Crystal meant by BE tables. Im a techie but my acronym
library is only so big My brain hurts.
BE is shorthand for Back-end database. It refers to the actual database
where the tables reside. Likewise, FE is shorthand for Front-end database.
When one splits a database using the database splitter wizard (Tools >
Database Utilities > Database Splitter), they end up with two databases: a FE
.mdb file (contains ALL objects [queries, forms, reports, macros & modules],
but only links to the tables) and a BE .mdb file (contains only the tables).

 

[strive4peace]

Hi Bill,

sounds like a useful procedure... care to post the code to reset all
table's subdatasheet property to [None]?

Thanks!

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

 

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