If you have a table with that many columns you need to think about normalizing or splitting your table up.
Secondly, if you are approacing the maximum number of characters in a Memo box you can always store the information as an OLE object, this will allow you maximum size up to 1GB. However a memo holds 64k worth of data, that is A LOT of information.
I am thinking your DB may have problems greather than the number of fields and space for data that wont be resolved with MS Access 2k3
I have no problem with the design of the database I can split the tables no problem. The trouble I am having is when I have to send the data to clients who have requirements for lots of codes in one field. I.e a string of text in a field such as F009#F987#F654#F432 and so on?
The design of the database has nothing to do with this. The values are stored within a table seperate or not it seems irrelevant to me.
A field in the table called Question1 has a value of -1 when the tick box on the form has been ticked. Say we have 30 questions. Each box is ticked. So I have 30 fields of -1's. I then have to change the -1's to relevant codes for my client. In question1 the -1 changes to F098, The -1 in question2 changes to F786 and so on.
I then have to put all these values in one field seperated by #. I do this via an update query.
So I then achieve F098#F786#F786 and so on. My problem is that I dont only have 30 questions some times it is hundreds and the memo field is not big enough.
With a simple table design like this you can eliminate the number of columns and the size of the memo fields. You could have a survey with 1million responses taken by 100 people and still not reach critical mass on an AccessDB. You need to think about how your data is organized and get an understanding of 3NF.