I'm getting error 3047 in MS Access 2007 importing a large amount of data (beware of users with free text fields). I found online that this is due to the 2Kb limit on records in access. Is there a way around this limit without normalizing the table (and re-writing all the code)?
+4
A:
Memo fields and OLE fields are not counted as part of the record limits.
Remou
2010-07-26 15:18:16
@Remou, the problem is not the *field* size, but the record *size*.
C. Ross
2010-07-26 15:48:53
I understand that, however, data stored in a memo field does not count as part of the record size limit, therefore, the text field can be changed to a memo field as a workaround for your problem `memo: 65,535 when entering data through the user interface;1 gigabyte of character storage when entering data programmatically` OLE Fields are also excluded.
Remou
2010-07-26 15:53:12
+1 I don't understand why this answer was down-voted.
HansUp
2010-07-26 15:56:15
@Remou Sorry, If you edit your answer I'll switch my vote.
C. Ross
2010-07-26 16:04:08
Is that better ;)
Remou
2010-07-26 16:10:55
@Remou, Yes, plus your edit allowed me to switch my vote!
C. Ross
2010-07-26 16:16:59
+2
A:
You can avoide re-writing much code, while still normalizing.
In that case, normalize the tables, and then create a query that 'de-normalizes' it back into what the original table looked like (same name, same fields). That way, anything that depends on the currently de-normized table will be able to refer to the new query without having to be re-coded.
dave
2010-07-26 15:19:29
+1 Excellent suggestion. It can be useful in many other situations, too. One potential drawback, the query used as a proxy for the original table may not allow editing like the original table ... e.g. the one side of a one-to-many join.
HansUp
2010-07-26 15:54:37
My idea was basically just indended for a bandaid (with SELECTS in mind, not INSERTS so much). The fact of the matter is, there is more you can do with a band-aid approach, but in the end, there is no good permanent substitute for normalized tables in terms of scalability, and the more band-aids you use, the worse things eventually will get.If it's not too much re-coding, then I'd suggest at this point creating INSERT/UPDATE procedures for your newly normalized tables. (i.e., sub-forms, or whatever you decide to use)
dave
2010-07-26 16:05:43