Solved: Fit Table columns

[Zack Barresse]

Yes, new to Access and still in the learning process (and probably will be for quite some time!).

When I'm looking at a table I want to view the entire column, but it's headers are not showing all the way. I can double click the seperator between the individual columns to [auto] fit the column, but how do I do this for all columns at once?

If there is a non-VBA solution, I'd prefer that. I don't know if there is a native way of doing this or not. Thanks for any help. :)

[matthewspatrick]

Zack,

Double-clicking the field separator in the headers should resize the columns. Make sure to save the table on close, though!

Patrick

[Norie]

firefytr

There is no way to do this that I know of in Access.

When you double click the seperators you are actually fitting the column to the width of the longest piece of data currently displayed.

Generally in Access you should display data via a form rather than a table.

[Zack Barresse]

I realize about autofitting a single column. Thanks guys. :yes

I also realize that generally data will be viewed in a form, I'm just looking for a quick way to view all the column headers, as I'm trying to import data from Excel and I need the column headers to match, which they don't right now.

[Zack Barresse]

One thing I tried was doing a MSQuery from within Excel to get the columns into Excel, then I could match them up and then import form within Access.. but it shows my database as not having any tables.. :(

[XLGibbs]

Make sure MS Query is set to look at all tables and views, by changing the options in the Table selection screen. I am surprised that it would show no tables at all though....that seems odd!

I use MS Query all the time though, so if you need any tip-aroos...be sure to shout.

[Zack Barresse]

Yup, option set to show all tables. My first query .. and I blow it up! :D

[XLGibbs]

FYI, the easiest way to do get data from excel to Access is to use "Link Tables" and set up a link to the excel sheet. That link could then be used in queries. In the event of needing to update a table in Access based on the excel data, either make table queries or Append queries can be set up as needed.

It is at least an easier option than using a SQL insert into statement, which if the columns did not match would just to have to be identified explicitly in the Insert statement..

File>Import >Link Tables
Change file type in browser to excel..browse to excel file.

Sheets show up as Sheet1$
Named ranges show up as Sheet1$NamedRange, or others would identify the $A$1:$A$2 references of used ranges...kind of trial and error to know what to look for...

Insert Into {Table Name} ({columns}}
Select {Columns}
From {Source}

If the column names don't match, but they are identified, the insert into can still be done.

Insert Into Totals (Date,Count, Dollars)
Select MyDate, TotalCount, NewDollars
From ThisMonth

would be an example....the columns get applied in order (1st select from goes to first Insert into column etc)

Hope that helps.

[Norie]

firefytr

The captions in the table view do not necessarily match the actual field names.

The easiest way to output the field names would probably be to write a bit of code to loop through the tables TableDef Fields collection.

[Zack Barresse]

Thanks for all the suggestions! I really appreciate it.

I'll try working through this and let you know how it went. :)

[TonyJollans]

The thread seems to have moved on but to answer your original question, Zack, try selecting all the columns and doing Format > Column Widths > Best Fit.

[TonyJollans]

Actually I just went and tried (Access 2003) and double clicking the separator resizes all selected columns, so just select them and double click any separator.

[Zack Barresse]

Ah! That it does Tony! Thanks!! :yes

I was clicking on the upper right space where the column and row headers meet. In Excel this selects all cells. In Access it selects all the records but will not select the column headers, which is what I didn't realize.


Now as far as getting the data all into Access, I have done that. Now I have two seperate tables I'm trying to append to each other and I'm working on my first Append Query. :)

Thanks everyone for the help!!

[OBP]

Zack, shame on you for not asking me. I post here as well you know.