Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access 'nested' tables?

Posted on 2015-01-14
3
363 Views
Last Modified: 2015-01-14
Hi,

I am in the process of converting an access database and code to SQL and while copying tables into SQL I stumbled over something weird.

I know access reasonably well, but had not seen this before, please help.

On the enclosed picture there's a table called PO_Total which is a linked table to another access database.  However when I open it there a 'drop-down' + next to each row with more data... where is that coming from, and where is that defined?

Thank you in advance, please ask any questions if this does not make sense...

~J
delete.png
0
Comment
Question by:prosit
3 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 40549913
That is called a "subdatasheet"
Whenever you create explicit one to many relationships between two tables, these drop-downs will appear.
...clicking any of them will expand them and display the "child" records for the selected "Parent" record.

You can turn them off with code like this:
http://www.rogersaccesslibrary.com/forum/set-subdatasheet-to-none-in-all-tables_topic614.html

JeffCoachman
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40549926
This was a  'feature' added to Access
It is a UI feature in the tables--not a place where you usually want UI to be.
When enabled, any table that is the 'one' in a one-to-many relationship will show a subdatasheet for the 'many' side.

This is strictly a UI feature.  If can be ignored if the purpose is to migrate the data away from Access.
If the database file will continue to be used, it can become a performance issue.
Many professional developers will turn this off.
It can be done table by table through the Access UI (although I have forgotten how now)
Or by code.

the VBA code I used to identify tables that had this feature was

Function ReportSubDataSh()
On Error Resume Next
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim prp As DAO.Property
    Const conPropName = "SubdatasheetName"
    Const conPropValue = "[None]"
    
    Set db = DBEngine(0)(0)
    For Each tdf In db.TableDefs
        If (tdf.Attributes And dbSystemObject) = 0 Then
            If tdf.Connect = vbNullString And Asc(tdf.Name) <> 126 Then 'Not attached, or temp.
                    If tdf.Properties(conPropName) <> conPropValue Then
                        MsgBox tdf.Name
                    End If
            End If
        End If
    Next
    
    Set prp = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Open in new window


From there it was simple after checking that I wasn't going to hose anything up to change
MsgBox tdf.Name

to
tdf.Properties(conPropName) =  conPropValue

and turn it all off.
0
 
LVL 2

Author Closing Comment

by:prosit
ID: 40549990
Ahh, confusing me while trying to give me more than I asked for ;)

Thanks guys...

~j
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question