ODBC Export Error

[Chirag Mistry]

We regularly create Room Data Sheets for projects and I had tested a pilot using Revit + Access mentioned here (http://forums.augi.com/showthread.php?t=47623)
I am currently developing the template for this and suddenly while trying to export to access am getting the error "ODBC failed - Too many fields".
I read little bit about it online - an access table cannot have more than 255 fields, but the problem with exporting to ODBC from revit is that it likes to export everything and you cannot control the fields.

Any suggestions ?
Thanks

[hand471037]

Any suggestions ?

Is there an inbetween step you could make? Exporting to SQL, or Excel even, and then having your already-built Access database pull just what it needs from that?

Can Access pull data from text CSV files?

Another option would be to look into the API, and just directly export the data you're after rather than using ODBC.

[dhurtubise]

Did you tried your export with a smaller project first ?

[ITABWODI]

I've been doing the same thing for RDS reports from Revit and have run into the same ODBC Export error - too many fields. In the short term, I have found that if I delete some tables from the Access database, the export goes off without a hitch. It appears to happen when I hit a certain number of shared parameters that I've added to the project to export to the Access file. I know for a fact that none of my tables are >255 fields, so it must be some other limit I'm hitting. Please post back if you find the solution and I'll do the same.

I've experimented with exporting to SQL Express (the free version), and it does not have the same restriction on number of fields that Access does. You can then use linked tables in Access to connect to SQL Server. One caution - SQL Server has slightly different field name requirements than Access does, so you may end up needing to adjust your queries and reports if you go that way. Also, that does add another step in the process and another server to worry about. I do like the idea of using the API to read the data directly out of the Revit file, especially if it could potentially be a two-way street. My programming is pretty rusty, though; and exporting to Access has been pretty easy so far.

[cphubb]

The advantage to SQL 2000 or 2005 is to add a trigger to the db and create a unique id for all tables being exported. That way you can export all your Revit projects into a single database and use Access to query adding that unique id and get project specific information. Works great and reduces the step of creating a new ODBC file for each project.