2 questions about Access' main window

[nford]

1. When I open Access 2007 on a wide screen monitor and open a table in the main window, it opens across each table the entire width of the screen, even if the table just has 2-3 columns. Then I have to manually drag the edge over. Repeat for every table -- every time I load a database, even though when I close it asks if I want to save the layout. I spent 12 years with this in Access 97, so I have a total of about 1000 man-hours dragging tables down to size. I was hoping it was fixed in Access 2007. Any way around this?



2. On the left of the screen is a list of "All Tables". I right-click on "All Tables" and say to sort (the list, I would assume) by name, but it doesn't. I would prefer to drag the tables into an order of my own choosing. Any way to sort or drag them?

I've searched Help for these problems, but no help found.

[NTC]

well the answer to 1. is to use forms. tables aren't really the place to expect custom-izable layout; obviously you are comfortable in tables and this advice will seem like a hassle.....

not sure on 2. as my jobs are all for businesses in a split environment so the BE is just tables holding data and I don't spend alot of time there.....my focus is deveoping the FE features....

[nford]

I'm developing a program in VB6 which uses an Access database. I want all of the tables open so I can manually double-check what my program is doing when I test-run it.

[RuralGuy]

I can select "Remove Automatic Sorts" and then drag my tables into any order I want. Have you applied all of the Office 2007 updates and HotFixes?

[carlmdobbs]

I'm developing a program in VB6 which uses an Access database. I want all of the tables open so I can manually double-check what my program is doing when I test-run it.

Hello. There are many ways to interpret what you are asking for. I can help you out but I'd need to ask questions.

Anything can be done with VBA. I know exactly how to make the tables visible for you.

You can email me at carlmdobbs@gmail.com or contact me through this forum.
Carl Dobbs
The Dobbs Computer Institute

[carlmdobbs]

1. When I open Access 2007 on a wide screen monitor and open a table in the main window, it opens across each table the entire width of the screen, even if the table just has 2-3 columns. Then I have to manually drag the edge over. Repeat for every table -- every time I load a database, even though when I close it asks if I want to save the layout. I spent 12 years with this in Access 97, so I have a total of about 1000 man-hours dragging tables down to size. I was hoping it was fixed in Access 2007. Any way around this?

2. On the left of the screen is a list of "All Tables". I right-click on "All Tables" and say to sort (the list, I would assume) by name, but it doesn't. I would prefer to drag the tables into an order of my own choosing. Any way to sort or drag them?

I've searched Help for these problems, but no help found.

The only way to handle this is to make a form for each table then open up the forms, which then can be manipulated through code.
Now this may seem troublesome to you do do this with each table. I can make a form for you with a single button on it which will look throughout hour databaase and select all of your tables then make a form out of them. You'll have the option of making the forms automatically be shown in table view or in any other view. I can show you how to customize this form to enable you to make each displayed table a predetermined width. It would count the number of columns of each table and automatically resize the form to fit.
Remember that with the proper training in VBA and knowledge of MS Access, you can do anything you can imagine.
Write me and we'll get started. I am here to serve you freely.

Now, to sort the tables in the order you want them? Name them tbl10Mytablename, tbl20MyOthertablename etc. They will sort according to the number. Have another table you want in beteen 10 and 20? Name it tbl10aMyTableNme or, alternately, tbl11MyTableName. Then sort them alphegetically.
Here is another tip. You can name all queries, tables and forms having a connection with each other with the same prefix. The form "frmsales" can be named "frm1aSales" while its recordsource is "qry1aSales" and the rowsource of its listbox would be "qry1acboRowsource". The report based upon the form can be called "rpt1aReportOne" (with a more discriptive name, of course.) Remember that nomenclature is very important and can work to serve you. Tie important elements of your database together so they automatically line up the way you want them to and you can easily know the name of every element of the project by its name.

Carl Dobbs
The Dobbs Computer Institute

[nford]

I can select "Remove Automatic Sorts" and then drag my tables into any order I want. Have you applied all of the Office 2007 updates and HotFixes?

I have not manually downloaded any updates that I recall. I don't know if Access 2007 had done any automatically.

When I select "Remove Automatic Sorts" in the "All Tables" list, not only am I not able to drag the tables around to a different order in the list, but if I click "Sort By Name", "Sort By Creation Date", etc., nothing happens.

That's a pretty major bug to have gone undetected and required a patch, but I guess anything is possible. I thought it was more likely that I was overlooking something.

BTW: Under "All Tables", each table is in a separate box of its own name. For example, the table "Words" looks like this:

Words
[icon] Words : Table

I can click and drag the line "Words : Table", but not outside the "Words" box, so that basically does nothing (for me).

[nford]

The only way to handle this is to make a form for each table then open up the forms, which then can be manipulated through code.
Now this may seem troublesome to you do do this with each table. I can make a form for you with a single button on it which will look throughout hour databaase and select all of your tables then make a form out of them. You'll have the option of making the forms automatically be shown in table view or in any other view. I can show you how to customize this form to enable you to make each displayed table a predetermined width. It would count the number of columns of each table and automatically resize the form to fit.
Remember that with the proper training in VBA and knowledge of MS Access, you can do anything you can imagine.
Write me and we'll get started. I am here to serve you freely.

Now, to sort the tables in the order you want them? Name them tbl10Mytablename, tbl20MyOthertablename etc. They will sort according to the number. Have another table you want in beteen 10 and 20? Name it tbl10aMyTableNme or, alternately, tbl11MyTableName. Then sort them alphegetically.
Here is another tip. You can name all queries, tables and forms having a connection with each other with the same prefix. The form "frmsales" can be named "frm1aSales" while its recordsource is "qry1aSales" and the rowsource of its listbox would be "qry1acboRowsource". The report based upon the form can be called "rpt1aReportOne" (with a more discriptive name, of course.) Remember that nomenclature is very important and can work to serve you. Tie important elements of your database together so they automatically line up the way you want them to and you can easily know the name of every element of the project by its name.

Carl Dobbs
The Dobbs Computer Institute

I appreciate your time and effort, but my problem is that in the Access 2007 main window (as indicated in the Subject line here), there is a column on the left side with the heading "All Tables". I have 17 tables listed there, each in its own little box. I cannot change the order of those table boxes, whether I change the "Sort By" or turn off sorting and try to drag them.

This is a relatively minor thing, but it is a nuisance not being able to sort them.

The major problem is that when I open a table inside Access 2007's main window, it spreads across the width of my 24" widescreen monitor, even though each table has only 2-3 narrow columns in it, so I have to drag the right side of each table's box down to size... every, single time I start Access.

Why MS would have designed it this way is a mystery. I see nothing to be gained by opening tables to maximum width every time, especially when it would be trivial to remember the table dimensions and locations each time. But why after all this time they STILL do it this way is an even bigger mystery. Maybe it doesn't bother anyone but me.

For the record, I have been programming in VB since VB 1.0, but the problems are not related to VB, just to the main Access window.

[carlmdobbs]

You have it set to "tables and related views" if each is in its own box. Choose from the menu: Group by Object then select Tables. Only tables will show in a list. You can then right click and sort by name.

The tables will always give you problems as you said when opening tables. That is why you need to create a FORM and put the FORM intable view. Only then will it enable you to manipulate the size. This is why we have to go through the step mentioned in the first missive. I create a button which reads all of your tables and automatically makes forms for them and puts the forms in table view which means it looks like a table but gives you the funtionality and properties of a form, which then can be manipulated with VBA code to obey your demands for dimensions.

As an expert in VBA I can teach you how to do this.
Carl Dobbs
The Dobbs Computer Institute

[RuralGuy]

IIRC SP2 put a bug in regarding that area of the Navigation Pane. Here is the latest HotFix whick corrects that sone of the issues caused by SP2. My version is:
Access 2007 (12.0.6423.1000) SP2 MSO (12.0.6425.1000)
Edit: OK...here's the link - http://support.microsoft.com/kb/950488

[nford]

IIRC SP2 put a bug in regarding that area of the Navigation Pane. Here is the latest HotFix whick corrects that sone of the issues caused by SP2. My version is:
Access 2007 (12.0.6423.1000) SP2 MSO (12.0.6425.1000)
Edit: OK...here's the link - http://support.microsoft.com/kb/950488

I downloaded that fix, but it would not install. It says that the expected version of the program was not found. I looked in the usual places in Access for the version number (e.g.: the Help window), but couldn't find it.

[RuralGuy]

This link should help:
http://www.btabdevelopment.com/main/...6/Default.aspx

[nford]

This link should help:
http://www.btabdevelopment.com/main/...6/Default.aspx

Thanks. I had to laugh. Who but MS would make one jump through so many hoops just to get the version number. Turns out that I have the same version you have.

But I think that I've figured out why you and I differed. Under "All Tables" is a box for each of 17 tables within my database. Each of my tables has one file in it, which is the table itself, except a table at the bottom named "Unrelated Objects", which has 4 items: Links and Query1-3. When I right-click on All Tables and say to sort by date created, "Links" moves to the end of that box, but when I say sort by name, it moves to the top of that box. The 17 boxes containing the 17 tables don't ever move, which is what I'm trying to sort.

I'm guessing now that it just can't be done.

I've also found all kinds of customization options, but none which let you set what to me is a very obvious treatment -- when a table is opened, only make its window as wide as the columns in it. On the off chance you want to add or expand a column, you can always make the table's window wider, but usually you don't want to, which is why the way Access opens tables (for data view) is nonsensical to me.

[RuralGuy]

Left click the All Access Objects at the top ov the Nav pane and select "Object Type" and "All Access Objects" then try your sort again while only looking at the Tables.

[nford]

Left click the All Access Objects at the top ov the Nav pane and select "Object Type" and "All Access Objects" then try your sort again while only looking at the Tables.

Sorry, but I don't see "All Access Objects", and I don't know what the "Nav pane" is. Here is a screen shot:
http://www.aeyec.com/access problem.jpg

The column I'm trying to sort is on the far left, "All Tables".

I've also opened a table to show how ridiculously wide Access makes it when opening it, rather than remember its last size and location like 99.9% of other programs do. It's even worse than it looks because I'm only interested in the first 3 columns.