Appending >16 Fields

[Isolec]

I use an append query to move data from one table to another in access 2007. The table from which the data comes has many fields that i do not append. Up until now, i have always had less than 16 fields per record. However, i now have 20. What is the best way of approaching this. I tend to use the inbuilt tools rather than use VBA or SQL. Any thoughts?

 

[CJ_London]

Create an append query - limit is 255 fields

 

[Isolec]

Hi CJ London, I am using an append query but in access 2007 i seem to be limitted to 16 fields!!

 

[Minty]

I regularly add about 40 + fields using append queries for new part number updates, what is stopping you adding more fields? Are you getting an error?

 

[Isolec]

Hi Minty. I just can't get more than 16 fields to appear in the append query and i was led to believe that access 2007 had a maximum of 16? Is 16 a default and can i increase it somewhere? Thanks

 

[Minty]

I do a lot of development in ac2003 and it doesn't have that limit...

Try creating a normal select query with your 20 fields , then change it to a append - see if that works?

Edit:

Your problem is only with the design view.
If you simply drag and drop the extra fields down to you query grid the extra fields will be added.
AC 2010 only displays 16 field columns, but you aren't restricted just to those.

 

[Isolec]

I think i must have a total mental block. Of course that works just fine. Many thanks

 

[ConnorGiles]

I guess it depends on your term of fields Isolec,

16 fields in my terms are records, If you meant 16 field length (as I assume CJ thought you meant) then follow his answer.

If you meant otherwise, please elaborate on what you mean by 16 fields per record. (which is now changed to 20)

 

[ConnorGiles]

well, I left my comment in the box and didn't press send until just

Seems I'm too late.

Glad its sorted

 

[Isolec]

Probably didnt expain the problem too well. I was appending several hundred records, each with 16 or 20 fields. Initially the append query only shows 16 fields, but does allow you to drop and add further fields as i have now discovered! Thanks