"Microsoft Access can't change the data type." mes

[Gates Is Antichrist]

As I tried to save changed table properties (reducing text fields from 255 to 3), I received this messagebox message, followed by "There isn't enough disk space or memory."

Well, I do believe I disagree with that little part at the end, but regardless, what do I do now?

I would like to be able to modify table properties without VBA or native SQL. Given the error message, is doing a new "Make table" my next move? And if so, how do you restrict a make-table field to be 3 characters, when -- for all he knows -- the source is 255 characters?

Finally: need I even care about that 255? If I browse the table, it doesn't SEEM to have 251 blank spades in the actual data. Tell me I'm just unjustifiably paranoid

Again, I want to stick to the standard Access operations - not to into editing the SQL. Thanks.

 

[dk]

Re: "Microsoft Access can't change the data type."

As I tried to save changed table properties (reducing text fields from 255 to 3), I received this messagebox message, followed by "There isn't enough disk space or memory."

Well, I do believe I disagree with that little part at the end, but regardless, what do I do now?

I would like to be able to modify table properties without VBA or native SQL. Given the error message, is doing a new "Make table" my next move? And if so, how do you restrict a make-table field to be 3 characters, when -- for all he knows -- the source is 255 characters?

Finally: need I even care about that 255? If I browse the table, it doesn't SEEM to have 251 blank spades in the actual data. Tell me I'm just unjustifiably paranoid

Again, I want to stick to the standard Access operations - not to into editing the SQL. Thanks.

Hello,

One method might be to copy your table (whilst in the database window). When you click Paste you're given the option of pasting the structure or the structure and data - choose Structure Only. Then change the field size in your newly created table and copy and paste the records from the old one. Bit manual but it should work.

According to Access help you should minimise the size of fields. If you only need 3, then set it to 3. If you keep the field size at 255 the size of the MDB will not increase, Access will only use what it needs for each and it won't set aside space for all 255 characters. Personally, I've found little performance to be gained by reducing field size compared to other optimisation methods (but there will be some, no matter how small).

Finally, if you want to specify a field length in a make table query just use something like this:-

CREATE TABLE MyTable
(
FirstField CHAR(3),
SecondField CHAR(255),
ThirdField INTEGER
)

 

[Gates Is Antichrist]

Re: "Microsoft Access can't change the data type."

Thanks, dk - copy structure (and tweak) was the best solution I received on the outside. However he suggested to then do an append query. That is a fairly painless route, but a copy and paste is even easier.

I always thought that you couldn't do that, but you spurred me to look again. What do you know, there is a "paste append" choice on tables - goody!

 

[Gates Is Antichrist]

Re: "Microsoft Access can't change the data type."

That is a fairly painless route, but a copy and paste is even easier.

I'll correct myself on that. This was the easiest append query in history.

Since both source and dest. had identical field names, all I had to do was the .* field, and each field was handled. Whodathunk that Microsoft would implement something so well?!