MS Access Column number Limination - Too Many Fields Defined in a Microsoft Access Database Table

Ever received the "Too Many Fields Defined" Error message when saving your database table?

Microsoft Access keeps an internal count of the total number of fields in a database table and has a limit of 255 fields per table. Each time that you change a property for a field, the column count is incremented by 1. Also, whenever you delete a field, the column count isn't decreased, but remains the same. When you delete a field, Microsoft Access does NOT reset this counter. So it's possible for you to have less than 255 fields and still get this error message.

Problem: If you are programmatically adding and removing columns in a table then the above problem can occur. This is one of the best example of bad database design. This error is more commonly encountered in a database that hasn't been correctly normalized and has excessively large tables with many, many fields. In this case, the better solution would be to redesign the database so that it is normalized to third normal form.

Solution for reset the internal field counter

1) Solution – Using Ms Office 2007 - Click Office Button-> Manage and then click Compact and Repair Database

2) Solution – Programmatically

How can I reset the initial value of a counter (AutoNumber) field programmatically with VC++ #include "afxdao.h" #include "shlwapi.h" bool CompactDatabaseDAO( const CString& strSourcePath,  const CString& strDestinationPath) {       bool bSuccess = true;       try       {             AfxDaoInit();             CDaoWorkspace::CompactDatabase(strSourcePath,strDestinationPath);             AfxDaoTerm();       }       catch(CDaoException)       {             bSuccess = false;       }       return bSuccess; } bool CompactDatabase( const CString& strDatabasePath) {       bool bSuccess = false;       CString strDatabaseFileName = ::PathFindFileName( strDatabasePath);       TCHAR chTempFolderPath[MAX_PATH];       if( FALSE != ::GetTempPath(MAX_PATH, chTempFolderPath))       {                 TCHAR chTempFileame[MAX_PATH];             if( FALSE != ::GetTempFileName( chTempFolderPath,_T("MDB"),0,chTempFileame))             {                   ::DeleteFile( chTempFileame);                   //Call DAO or ADOX for Compact database                   if( true == CompactDatabaseDAO( strDatabasePath, chTempFileame))                   {                         if( FALSE != ::DeleteFile( strDatabasePath))                         {                               ::CopyFile( chTempFileame, strDatabasePath, FALSE);                               ::DeleteFile( chTempFileame);                         }                   }             }       }       return bSuccess; } int _tmain(int argc, _TCHAR* argv[]) {       CompactDatabase( _T("E:\\Work\\TestProjects\\Database\\Debug\\Blank_Sen.mdb"));       return 0; }

Visual C++: Compacting an Access Database via ADO Although ADO specification does not provide objects to compact Microsoft Access databases, this capability can be achieved by using the ADO extension: #import "C:\PROGRAM FILES\COMMON FILES\System\ado\MSJRO.DLL" no_namespace