DoCmd.TransferSpreadsheet does not export all fields

[Claude Colaco]

I used the DoCmd.TransferSpreadsheet method to export from an Access 2003 table to Excel. The table had around 440 fields but only 230 (column iv) got exported.

Does anybody know whether there is a limitation on the number of fields that can be exported.

Thanks
Claude

 

[boblarson]

How can you have 440 fields when a table can only have 255 of them? That is news to me.

 

[boblarson]

Are you sure you're not talking ROWS?

 

[Claude Colaco]

Thanks Bob,

Yes the access table does have 440 fields. I did not create the table manually but by selecting into a table from a query.

Claude

 

[boblarson]

Okay, we must be not connecting on terminology. A FIELD is a column and Access CANNOT have more then 255 of them - that is a physical reality. So, I have to assume you are meaning something totally different. A query can only have 255 fields (columns) too.

So, what is the 440? I would be thinking that you mean records, which is a row made up of fields.

 

[Claude Colaco]

Surprising as it may sound I am referring to columns and not rows. And this is a real life situation for a client who is dealing in retirement plans.

After running the query the access table does get created with all the fields but it is only when exporting to Excel that the spreadsheet had only 230 columns. Am treading in dangerous waters in creating the Access table with so many fields?

Claude

 

[boblarson]

I'm sorry but I would need to actually see it to believe it. There are no known cases that I know of where anyone could actually get more than 255 fields into a table. If you want, you can make a copy and then delete all of the data from the copy and then upload it here (after running TOOLS > DATABASE UTILITIES > COMPACT AND REPAIR and then zipping the file with WinZip, or something similar).

 

[Claude Colaco]

Thanks Bob but I can't upload the table. But believe me the query with over 430 columns runs fine in Access. Perhaps you can give this as an exercise to somebody. Create a normal query and then keep repeating the fields in the output fields. There is no problems because Access will give Aliases to the fields as Expr1, Expr2, etc. Of course i did not do this but actually gave the titles that the client wanted.

Claude

 

[boblarson]

Well, nobody is going to be able to help you, including Microsoft, as I don't know what has occurred or anything, but there is no logical way that it should ever have worked and continue to work.

The specs all say that 255 fields is the limit for a table and a query, and the experience of those here on the forum has born that out because there have been many people with problems as they get close to 255 fields.

So, unless we can actually see the table (and I don't see why you can't make a copy and delete all data and delete all other objects within the database) just so we can see this for ourselves and maybe even find out from Microsoft how this could have occurred, I don't see how anyone can help. We can't replicate your issue. So, that's where it's at currently.

 

[Mike375]

Bob,

One of my tables has 252 fields and in Design you can only see three rows remaining.

Copy and paste 10 fields and the message about 255 comes up.

I just made a query on the table and added four calculated fields (did as MakeTable and Select] and either case the message was Too Many Fields defined. Cut one out so it was at 255 and query ran.

In case someone is not aware, if you add fields it is not a case of getting to 256 an Access starts beeping....there are only 255 rows where to you can enter fields.

Does anyone know why the 255 is also a text field limit for characters as well as query grid criteria, expressions in macros. Perhaps the 255 characters limit for text fields carrys over to the query grid and macros, but VBA does not have the limit.

 

[Claude Colaco]

Thanks once again Bob. Can you direct me to the Access specs for the number of fields. I tried looking for them but did not find the limit for the number of fields. This will help remove the skeptical look from the client's eyes

Claude

 

[boblarson]

If you go to the place in Access for the help file, you can type in Specification and it will popup several options including the Access Specifications. Look under the TABLE heading for the specs for tables.

 

[Claude Colaco]

Thanks for the pointer Bob
The Access specification ( for Access Projects) for table columns is actually 1024. So the problem seems to be only with the DoCmd.TransferSpreadsheet method

Thanks
Claude

 

[Mike375]

Microsoft Access project specifications
Access project

Attribute Maximum
Number of objects in a Microsoft Access project (.adp) (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.) 32,768
Modules (including forms and reports with the HasModule property set to True) 1,000
Number of characters in an object name 64
Number of columns in a table 250 (Microsoft SQL Server 6.5)
1024 (Microsoft SQL Server 7.0 and 2000)

 

[Mike375]

It looks like Excel has a limit.

I just put my big Access table across, including doing Import in Excel and there are only 3 colums left vacant.

 

[Mike375]

Claude,

I am think you data is on SQL and Excel has a limit of 256 columns

http://www.mrexcel.com/archive/General/17053.html

 

[Rich]

Bob,

One of my tables has 252 fields and in Design you can only see three rows remaining.

Copy and paste 10 fields and the message about 255 comes up.

I just made a query on the table and added four calculated fields (did as MakeTable and Select] and either case the message was Too Many Fields defined. Cut one out so it was at 255 and query ran.

In case someone is not aware, if you add fields it is not a case of getting to 256 an Access starts beeping....there are only 255 rows where to you can enter fields.

Does anyone know why the 255 is also a text field limit for characters as well as query grid criteria, expressions in macros. Perhaps the 255 characters limit for text fields carrys over to the query grid and macros, but VBA does not have the limit.

Still using Access as a spreadsheet I see

 

[boblarson]

Now, since you originally said that

...export from an Access 2003 table

that isn't the same as exporting from an ADP, because the table is not in Access. So, it would have saved a whole lot of effort and confusion if we had gotten that information to start with. An ADP is not the same as an Access mdb file, as you can see.

Look for the specifications for Excel to get the same information for it.

You might have better luck with Excel 2007 as it has a lot more column and rows available.

So, I'm hoping that this "table" of yours is a datamart and not a normal production table as it should really almost never get even close to 255 columns, let alone 440, if it is a properly designed and normalized database. There might be an exception, or two, to that rule, but it is very rare.

 

[Mike375]

Bob

I will tell you at least one area where there is an exception.

The "person" will have quite a few tables that are "many records" for him but you want the results of the last record from the "many side" to be on his own record. We do this with telemarketing and one reason is we want to be able to display on the form some of those results and which ones will vary. Subforms are a waste of time because not only will you need too many but positioning what you want on the screen has problems. Opening forms is a pain etc. Also, if someone is doing the calling at another location and phones me and says they need to see xyz on the screen I can easily get them to show a field. It might look like crap but who cares because it will be changed to something else by the next day.

In fact I have it set up so that when a call (or other activity) is done then the data from the last record from a many table is inserted into the corresponding fields on the main table. Actually I could use more than the 255.

Mike

 

[Moniker]

For those curious about the 255 limit, it's just binary math. 256 (0 through 255 = 256) is 2 ^ 8. The 1024 limit is 2 ^ 10. The reason you have these seemingly arbitrary limits is because when Access creates an object, it automatically sets aside X amount of space for that object, regardless of whether or not you ever use all that space.

Video cards work this way. 16-bit color is 2 ^ 16, and 32-bit color is... wait for it... 2 ^ 32.

Your hard drive works this way. When you format a hard drive, you can choose the smallest amount of space that it will use at a time (usually 4k, or 2 ^ 12). The smaller the segment, the more you can store, but you increase storage at the expense of speed. For example, when you create a textfile and all it says is the word "test" in it, the hard drive will still take that 4k (or whatever your segment size is) and set it aside for your textfile. Once your textfile gets larger than 4k, it adds another 4k, and so on. This should tip you off as to how fragmenting happens as well. If you have six things running at once, the hard drive is constantly setting aside space for which ever application needs it at the time. Program A get 4k, then program B needs 16k, then Program C takes 512k, then Program A needs 4k more, etc.

So I got a little off-topic. The point is that everything in your computer is binary, and making the limitations revolve around a multiple of 2 makes data storage and retrieval a ton easier, at least on the computer. Excel 2003 and earlier is limited to 2 ^ 8 columns, and 2^ 16 rows. (This, btw, is why you cannot export 440 rows. 255 is the limit, at least in 2003 and earlier.)

I'm with Bob in that having a single table with 440 rows is goofy, and I'm not agreeing with Mike's argument at all. If you cannot setup relationships and then use a single query to make it look like one big table, it may be time to get a book or ten on Access or even DB design in general. Once a table gets more than 50-60 rows, you really have to consider if your design is right. Sometimes it is, but most of the time, it's not.