Bypassing 255 field limit in table and forms by splitting out large table into two?

[joe789]

Hey Folks,

I got a complicated issue here ... this has to do with tables and forms. I am using MS Access front end linked to SQL Server back end for a gigantic survey for a non-profit. The survey is so large that it goes well over the 255 field limit per table/form with Access. In order to make this work, because removing fields is not an option at all, I split the large table into two smaller ones to get away from the field size limitation for both the tables and forms. The table that holds all of the primary info has a primary key that is set by SQL that works fine - int auto number ID unique non null with retired numbering if a delete occurs. Here is what I am struggling with: I need to somehow link the two tables together so that each time a user creates a new record via the form or via command buttons or any other means, I need that second table to automatically create a record for that person and populate the ID key on that table to match the primary table's key so I can link the data. I am unsure what is the most thoural and complete fool proof way to do this so I wont end up with this app errering out or worse yet somehow end up with not being able to join the info from the two tables properly and to the same person in question.

If I can figure the above out, I can then try to dump the second table, then linked, to the main forn to allow the users to update that info as well. Of course two separate forms are necessary due to fields on each form having the 255 limit.

Any help would be greatly appreciated.

Thanks,

Joe

 

[tad0075]

Cityworks uses something like this for their comment fields to allow unlimited space for comments on each workorder. Their setup is something like this:

Workorder:
Workorder ID (pk)
Workorder details

WOComment:
CommentID (pk)
WorkorderID (fk)
SeqID
Comments

The SeqID field counts sequentially through the comment as it is split up, so a comment split into 3 255 character entries are indicated in order from 1 to 3.

They split the comments in their back end, which I haven't seen, but I assume you could accomplish this by querying out the first 255 characters, assign SeqID 1, then loop back and repeat while incrementing your SeqID value. You should be able to end the loop when Len(originalcomment) is 0. I'm sure this would have to be done via VBA function tied into the data entry form for ongoing use.

Whenever I need to look at the comment field as a whole, I use ConcatRelated() from Allen Browne's site, grouping by WorkorderID and ordering by SeqID. Works great.

Hope this helps,

Tyler

 

[Pat Hartman]

This table design is so bad it's scary so I hate to even jump in at all but you can force the related record to be created by writing an after insert trigger for the "parent" table.

You are going to hate this application so much that you may even refuse to ever use access again and it's all because of poor table design. You will be creating tons of code and hundreds of queries to analyze the data. All of it unnecessary and required only because you have created a spreadsheet and called it a table.

You will be far better off exporting the data to Excel for analysis.

This table should be tall and thin. One question per row rather than hundreds.

 

[speakers_86]

So your issue is that there are more then 255 questions in the survey? I suppose your current table layout is one table for answers, one field per question, resulting in going over 255.

If that is right, then I would suggest another approach.

tblQuestions
QuestionID
QuestionTXT

tblAnswers
AnswerID
QuestionID
VolunteerID
AnswerTXT

tblVolunteers
VolunteerID
Other Relevant Info


This approach is normalized better and avoids issues with the 255 field limit per table issue. Or you can just use Excel as Pat Hartman suggests.

 

[Galaxiom]

Put each table as the RecordSource of a subform, probably on different tabs. The MasterLink and ChildLink fields with keep them synchronised.

As soon as a record is put into a subform it will get the key from the form field specified in the MasterLinkFields property. This field can be a field in another subform.

You might run into some problems on loading depending on which subform loads first. In my experience subforms load in the order they are added to the design. Avoid replacing the one you specified as the master.

 

[tad0075]

Hey Folks,

...goes well over the 255 field limit per table/form...

My bad, I somehow read this as being about the 255 character/field limit.