Autofit Columns in Table

[mp3909]

is there a way to autofit the columns of a table in Access using VBA?

[Minty]

No. Why would you need to do this ? A table is for holding data, not displaying or working with it.
That's what you use forms for.

On a data sheet form you can use

me.<fieldName>.ColumnWidth = -2

to autofit that column.

[mp3909]

Hi Minty,

I am afraid I have to say I disagree with you there.
Say you have a table with 37 Columns and 1000 records. And you need to look at least the first 10 columns before you decided to amend values in the other columns.
You are telling me that it is wrong/inefficient to look at a table where everything is on one screen and where you can scroll up and down to find records that look odd? You are saying you would use a form to do this? Ok, well how will you spot odd values using a form?

[Minty]

Disagree all you want. Access tables aren't spreadsheets. They are data storage tables.

For occasional background "fiddling" as the database admin I'm sure we have all delved straight into a table, to look at things, but if you need to do it vaguely regularly, build a datasheet form with just the columns that matter. Make it so you can only edit the fields that should be changed. If you deleted a FK field by mistake whilst in the table how would you ever tie it back to the main record?
You can also build in filters and search boxes to narrow down your search without risk of accidentally deleting your important data, by leaning on the delete key.

If you ever switch over to an enterprise level DBMS such as SQL Server it's actually pretty difficult to do what you are currently doing.

[JoeM]

mp3909,

Minty isn't making things up here. It is widely accepted that best practices say that users should NEVER have direct access to the tables (too much chance for danger and to bypass important checks, as Minty pointed out).
All access to data should be controlled via forms.

Note if your main hang-up is that you want it to have a similar structure as what is seen in the table, take a look at creating a Datasheet view for your form.
See here for pcitures of different layouts you can do for your form: http://www.excel-networks.com/docs/m...20Examples.pdf

[Micron]

FWIW, agree with last 2 opinions but that's not the only reason for me posting here.

When you're looking at a table and double click to expand a field width, it's only as good as what's in the current view anyway. Move down a page, and any record where a field exceeds the new width isn't wide enough again. Thus even if you could do so via vba, it would seem to be dependent on the current view. Ever filtered or sorted a table and saved it before closing? You just applied a view that may not be wanted next time, so there's another reason not to play in them too much.