Limits to "ALTER TABLE...ADD COLUMN..."?

geoB

Registered User.
Local time
Today, 16:41
Joined
Oct 10, 2008
Messages
68
In ACC2007:

I have an application where one of the tables can have a variable structure. I have been successful up til now using DROP COLUMN for all the fields, then using ADD COLUMN to create the new structure. (There are procedures elsewhere that deal with the changed structure. The application needs to combine results from external sources where the fields in one source need to map to the records in another source.) Today I got an error 2190, too many fields after all fields had been dropped and the first new field was being added. The db is set to compact on close.

Any insight into why this occurs?

Thanks.

George
 
In ACC2007:

I have an application where one of the tables can have a variable structure. I have been successful up til now using DROP COLUMN for all the fields, then using ADD COLUMN to create the new structure. (There are procedures elsewhere that deal with the changed structure. The application needs to combine results from external sources where the fields in one source need to map to the records in another source.) Today I got an error 2190, too many fields after all fields had been dropped and the first new field was being added. The db is set to compact on close.

Any insight into why this occurs?

Thanks.

George

If I remember correctly, the is a limited number of edit you can do for an object. There are ways to reset the counter.

In your case, I would recommend dropping the table and recreating it ever time. Not just add/drop fields.
 
Thanks for your reply.

I would recommend dropping the table and recreating it
And here I was manipulating the table definition in an effort to eliminate the number of table deletions and creations I had been doing!

No way around either limitation, then, I guess?

g
 
Dropping the table and recreating will also eventually run into object indexing limits.
You could just edit the properties of the existing fields.

Otherwise make the table in a disposable database. I often recommend not using temporary tables inside the front end or back end of the database but use what I call a side end. Recreate the side end as required. Never need to worry about compacting it then either.

However I do wonder if you could achieve your goal using a Cross Tab query.
 
A 'side' db is an interesting idea - I'll see what I can do with it.

Modifying table properties isn't feasible because the number of fields may vary. I also have no experience with Cross Tab queries so I'll save that for another day.

But thanks for the good idea.

g
 
I totally agree with Galaxiom's excellent recommedation. The best solution would be to use a "scratch" back end for temp tables.

if you would like to see an example, let me know.
 
Boyd,

I'd very much like to see an example. All the wheels I try to invent seem to have sharp corners.

George
 
Boyd,

Many thanks for the link. I'll add it to my future reference bag of tricks.

g
 

Users who are viewing this thread

Back
Top Bottom