What is the maximum number of columns allowed in table?

[avenuw]

Hello,

I am trying to create a temp table to import some data into. I am currently doing so by linking to an excel sheet and then copying the structure of that excel table into my new table. This is because the excel sheet has soo many columns which I donot want to sit and retype out.

However when trying to change the datatype of some of the columns in the new table, it gives me the following error:

[COLOR=red]"Too many fields defined" [/color red]

if I expand the help button, i get the following

This error can appear if:

You have exceeded the maximum number of columns allowed in a table or the maximum number of locks for a single file.

The indexed property of a field was changed from Yes (Duplicates OK) to Yes (No Duplicates) when duplicate data is already present in the table.

If a table exceeded the maximum number of columns, close the database, select Compact and Repair from the Tools - Database Utilities menu option.  This will remove column references from a table definition that were marked as deleted (deletions do not actually occur until the database is compacted and therefore they contribute to the column count).

If the maximum number of locks per file was exceeded, you can increase the number by editing a registry entry for the local computer.  However, this is not a recommended option.
Find the MaxLocksPerFile registry value using the Windows registry editor (regedit.exe) and increase the value.

The MaxLocksPerFile value is stored as part of the following key:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0

If you edit a value in the Windows registry, there is no guarantee that the change will not corrupt the registry and render Windows unusable.  Edit the registry at your own risk.  If you do, it is highly recommended you make a backup of the registry before performing any edits.
If you have changed the Indexed property of a field and duplicate data already exists in the table, you can either reset the Indexed property back to the previous setting, or remove the duplicate records from the table.

I believe I have exceeded the maximum number of columns allowed in a table. i have currently almost 160 columns, yes its not normalized- but it is a temp table to change what needs to be changed and insert into correct tables,etc. Any ideas how i can increase the maximum allowed of columns allowed in a table.

Thanks,
AV

 

[Remou]

I do not think that you have exceeded the limit for fields (columns):

Number of fields in a table: 255

--

http://office.microsoft.com/en-us/access/HP051868081033.aspx

 

[avenuw]

Your right Remou although I did not exceed 255 columns in a table, because I was updating the datatypes for the 160 columns, it looks like the internal column counter reached the limit of 255.

Makes sense because it copies the original record and the updates that I made, so it quite easily reached the limit.

I can compact and repair each time and then come back and continue my updates or use vba. I choose vba!

Thanks
AV

 

[jsteph]

AV,
Access creates temporary fields when you change a datatype, so if you were changing all 160 fields' types, then it would have to have 320 total fields. The best way is to create a new table and append the data from old to new.
--Jim

 

[lespaul]

yes its not normalized- but it is a temp table to change what needs to be changed and insert into correct tables,etc.

why not just do the corrections in a query from the linked spreadsheet and insert into the correct tables?

INSERT INTO CorrectTable1 (SELECT Field1, Field25, Field68, iif(Field32 = 'Y', True, False) FROM ExcelSpreadsheet)

INSERT INTO CorrectTable2 (SELECT Field1, Field36, Field75 FROM ExcelSpreadsheet)

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com