Quick Question from a newbie - Access 2003

[Duran]

Hello,

You know how you can expand the views on databases, then table and then again to view the columns in sql server? Is there a way of doing something similar in Access 2003?

I have to write querys in it, and being used to SQL's SSMS, I am finding it somewhat frustrating when it comes to looking at the columns. I'm not that familiar with the database at this time, so it would really help.

Kind Regards,
D.

I plug you in, dim the lights,
Electric Barbarella !

 

[Remou]

Have you looked at the query design window? There is an icon to view tables, which can then be added to the design grid where you can see the fields (fields in Access, rather than columns). You can build pass through queries and data definition queries in this window, too. When you have the query set up, you can switch to SQL view. The SQL can be a bit messy, but don't tweak it in SQL Design View, it will just go back to the old way when you turn your back.

 

[JoeAtWork]

I think he's referring to the tree structure on the left side of SQL Management Studio, where you can basically browse everything (linked servers, databases, tables, right down to the columns with their data types) - while on the right side you can work on the design of the view ("query" in Access lingo).

Unless it's in Access2007, there's nothing built-in like that for Access. You are pretty much stuck with just seeing tables and their field names in the Query Designer. The Query Designer is pretty much the equivalent of the design window for views SSMS, in that you only see the tables you actually want to include in your view.

I've never really felt the need for the "Enterprise" view of the database in Access. Access databases tend to be small enough that I just know which tables I need to bring into the designer.

As a side note, something I've wished that they would do with Access is to be able to make multiple "diagrams" like you can in SSMS. There's only the single "Relationships" window - sometimes you want to work with just a subset of tables.

 

[Duran]

Joe at work,

Yes, thats what I was talking about, I know what you mean by them being small enough, but I have only just started here so I am not familiar with it enough to know what table has what I need when it come to writing queries. HHMM, maybe I can get my boss to spring for a copy of Access 2007?

Thank you both for taking the time to answer.

Regards,
D.

I plug you in, dim the lights,
Electric Barbarella !

 

[Remou]

You can create a form to list the tables and fields.

[tt]Create a form

Add a listbox called lstTables:

Row Source: SELECT Name, Type FROM MSysObjects WHERE Type IN (1,5,6) AND Left(Name,1)<>"~"
Column Count: 2
Column Widths: 3cm; 1cm 'Choose suitable widths

On Click event:
Private Sub lstTables_Click()
Me.lstFields.RowSource = Me.lstTables
End Sub

You may wish to exclude system objects, in which case set the Row Source to:

SELECT Name, Type FROM MSysObjects WHERE Type IN (1,5,6) AND Left(Name,1)<>"~" AND Left(Name,4)<>"MSys"

Add second listbox called lstFields:

Row Source Type: Field List[/tt]

As a next step, you could use the form to build an outline query.


===============================
Types:
1 Table
5 Query 'Note that queries named ~SQL are used by the system
6 Attached Table
-32768 Form
-32764 Report
-32761 Module

 

[JoeAtWork]

You misread me about Access2007, I don't know if it has any such browsing feature (I haven't used this version yet).

Remou brings up a good point that somebody somewhere has probably created code to create a similar tree-view listing of the entire database. And it wouldn't be that hard to create one yourself.

 

[PHV]

6 Attached Table
In fact linked tables are of type 4 or 6.