Solved

Max # of columns and record length

Posted on 2004-04-21
6
4,279 Views
Last Modified: 2010-08-05
In access xp, or 2000, How many columns are allowed in 1 table,
And in this talbe with the max number of columns, what is the largest record length possible. (Sum of fields types lengths)
0
Comment
Question by:adwiseman
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10880308
255 Columns or fields
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 10880327
to be exact

Microsoft Access database specifications
Access database

Attribute Maximum
Microsoft Access database (.mdb) file size 2 gigabytes minus the space needed for system objects.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set to True) 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255

Table

Attribute Maximum
Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 2048; the actual number may be less because of tables opened internally by Microsoft Access
Table size 2 gigabyte minus the space needed for the system objects
Number of characters in a Text field 255
Number of characters in a Memo field 65,535 when entering data through the user interface;
1 gigabyte of character storage when entering data programmatically
Size of an OLE Object field 1 gigabyte
Number of indexes in a table 32
Number of fields in an index 10
Number of characters in a validation message 255
Number of characters in a validation rule 2,048
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object fields) 2,000
Number of characters in a field property setting 255
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10880475
Well done Rey.
I would caution you, adwiseman, that before you build a table of 255 columns, SERIOUSLY consider if that table HAS to be that wide.  You may be better off normalizing where you can.  I only say this because it's pretty rare to even approach 100 columns.

Walt
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 14

Author Comment

by:adwiseman
ID: 10880514
I'm not actualy doing this, it was just an outer-boundry question.  

Great stuff  capricorn1
0
 
LVL 19

Expert Comment

by:david251
ID: 10880556
I was able to get 778147 Characters in in 12 memo fiels before access started to complain.

-David251
0
 

Expert Comment

by:calculus87
ID: 11101757
I actually would like there to be more than just 255 Columns or fields, because at work I use well of three hundred in some cases.  So I have to have 2 database open and blah it is a pain.
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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

792 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