Problem With Table With Many Columns

Question

I have a table with 104 columns and am using Access 2010. A form has the select query imbedded in the record souce, enumerating all the columns.  I have Zoomed and cut and pasted the query into a query object and have put the query name in the record source. However when I subsequently open the form, Access stops running and comes up with one of those gray screens and tells me it is checking the problem, and the form can never be opened again without the failure. Compacting and Repaing does no good. What is the problem and is there a work around (short of not doing the project or breaking up the tables)? Also, this has failed on other machines built by other people (a corporation), so it looks like there is yet another Access bugaboo. Thanks.

Answer 1

It's not a case of improperly designed tables (yet!). These are data points to be collected for a survey/data analysis application,

Actually, based on this description it IS an improper design. A survey database requires a very specific structure. I am suspecting that you have a field for each data point to be collected. And that IS improper design. 

The proper design for such a database would look like this:

tblQuestion

QuestionID (PK Autonumber)

Question

tblRespondent

RespondentID (PK Autonumber)

FirstName

Lastname

Other info about respondent

tblResponse

ResponseID (PK Autonumber)

RespondentID (FK)

ResponseDate

tblAnswer

AnswerID (PK Autonumber)

ResponseID (FK)

QuestionID (FK)

Answer

Bottom line is you should should identify each data point as a RECORD in a table, by identifying the question being answered and the survey the answer is from. What it sounds like you have is a record for each survey and a field for each answer.

Answer 2

It's not a case of improperly designed tables (yet!). These are data points to be collected for a survey/data analysis application, however I saw this when I took over the application to finish it. They may have to be broken up. What I would really like is to hear form someone who tried this and either it failed for them too, or they had a work around.

I am running Sp1.

Answer 3

I have a table with 104 columns and am using Access 2010. A form has the select query imbedded in the record souce, enumerating all the columns.

While this doesn't deal with the problem I would take another look at this table. Having 104 fields in a table is usually excessive and indicates a denormalized structure. 

If you can show us the structure or give more info about what the table does we can advise further.

Answer 4

Can you post the SQL Statement of the query itself, so we can take a look at it.

Secondly, any table with 104 columns is sounding off alarm bells to me, and any other experienced developer.  This is, 99% sure, an improperly normalized database.  The proper solution to truly address the problem will most certainly involve restructuring your tables and then your queries, forms, report.  A database is just like a house, if the fondation is shotty, then everything afterwards will reflect it as well.  If your tables structure is not done properly, then everything created afterwards (queries, forms, reports) will all suffer from the poor design.

I would also ask if your Office installation is up-to-date (SP1)?

Lastly, "this has failed on other machines built by other people (a corporation)".  I'm not sure I follow your statement.  but if you mean you've have professional developers look at it and they could get it to work, that isn't necessarily an indication of a problem with MS Access.  If another professional developer didn't address the issue of having 104 columns in a table, doesn't address fundamental table structure problems, then it is doubtful that they are competant enough to help with the rest.  Like in any field, not all professionals are created equal.

Tom, also has a good point.  For some info on decompiling a database, see: http://www.devhut.net/2012/04/16/ms-access-decompile-a-database/

Answer 5

You may have to decompile the file. Search the internet for "Access /decompile"