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)?
asked Jul 26, 2010 at 15:09
Memo fields and OLE fields are not counted as part of the record limits.
answered Jul 26, 2010 at 15:18
FionnualaFionnuala
90.2k7 gold badges113 silver badges152 bronze badges
2
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.
answered Jul 26, 2010 at 15:19
davedave
1,34410 silver badges16 bronze badges
2
Номер ошибки: | Ошибка 3047 | |
Название ошибки: | Microsoft Access Error 3047 | |
Описание ошибки: | Record is too large. | |
Разработчик: | Microsoft Corporation | |
Программное обеспечение: | Microsoft Access | |
Относится к: | Windows XP, Vista, 7, 8, 10, 11 |
«Microsoft Access Error 3047» Введение
«Microsoft Access Error 3047» обычно называется формой «ошибки времени выполнения». Когда дело доходит до программного обеспечения, как Microsoft Access, инженеры могут использовать различные инструменты, чтобы попытаться сорвать эти ошибки как можно скорее. К сожалению, некоторые критические проблемы, такие как ошибка 3047, часто могут быть упущены из виду.
Ошибка 3047, рассматриваемая как «Record is too large.», может возникнуть пользователями Microsoft Access в результате нормального использования программы. Когда появится ошибка, пользователи компьютеров смогут уведомить разработчика о наличии ошибки 3047 через отчеты об ошибках. Затем Microsoft Corporation исправляет эти дефектные записи кода и сделает обновление доступным для загрузки. Эта ситуация происходит из-за обновления программного обеспечения Microsoft Access является одним из решений ошибок 3047 ошибок и других проблем.
Что генерирует ошибку времени выполнения 3047?
Вполне вероятно, что при загрузке Microsoft Access вы столкнетесь с «Microsoft Access Error 3047». Рассмотрим распространенные причины ошибок ошибки 3047 во время выполнения:
Ошибка 3047 Crash — это типичная ошибка 3047 во время выполнения, которая полностью аварийно завершает работу компьютера. Эти ошибки обычно возникают, когда входы Microsoft Access не могут быть правильно обработаны, или они смущены тем, что должно быть выведено.
Утечка памяти «Microsoft Access Error 3047» — ошибка 3047 утечка памяти приводит к тому, что Microsoft Access постоянно использует все больше и больше памяти, увяская систему. Возможные причины включают сбой Microsoft Corporation для девыделения памяти в программе или когда плохой код выполняет «бесконечный цикл».
Ошибка 3047 Logic Error — Компьютерная система создает неверную информацию или дает другой результат, даже если входные данные являются точными. Обычные причины этой проблемы связаны с ошибками в обработке данных.
Как правило, такие Microsoft Corporation ошибки возникают из-за повреждённых или отсутствующих файлов Microsoft Access Error 3047, а иногда — в результате заражения вредоносным ПО в настоящем или прошлом, что оказало влияние на Microsoft Access. Большую часть проблем, связанных с данными файлами, можно решить посредством скачивания и установки последней версии файла Microsoft Corporation. Помимо прочего, в качестве общей меры по профилактике и очистке мы рекомендуем использовать очиститель реестра для очистки любых недопустимых записей файлов, расширений файлов Microsoft Corporation или разделов реестра, что позволит предотвратить появление связанных с ними сообщений об ошибках.
Типичные ошибки Microsoft Access Error 3047
Обнаруженные проблемы Microsoft Access Error 3047 с Microsoft Access включают:
- «Ошибка Microsoft Access Error 3047. «
- «Недопустимая программа Win32: Microsoft Access Error 3047»
- «Возникла ошибка в приложении Microsoft Access Error 3047. Приложение будет закрыто. Приносим извинения за неудобства.»
- «Microsoft Access Error 3047 не может быть найден. «
- «Microsoft Access Error 3047 не найден.»
- «Ошибка запуска программы: Microsoft Access Error 3047.»
- «Microsoft Access Error 3047 не работает. «
- «Microsoft Access Error 3047 остановлен. «
- «Ошибка пути программного обеспечения: Microsoft Access Error 3047. «
Ошибки Microsoft Access Error 3047 EXE возникают во время установки Microsoft Access, при запуске приложений, связанных с Microsoft Access Error 3047 (Microsoft Access), во время запуска или завершения работы или во время установки ОС Windows. Важно отметить, когда возникают проблемы Microsoft Access Error 3047, так как это помогает устранять проблемы Microsoft Access (и сообщать в Microsoft Corporation).
Эпицентры Microsoft Access Error 3047 Головные боли
Заражение вредоносными программами, недопустимые записи реестра Microsoft Access или отсутствующие или поврежденные файлы Microsoft Access Error 3047 могут создать эти ошибки Microsoft Access Error 3047.
В основном, осложнения Microsoft Access Error 3047 связаны с:
- Недопустимые разделы реестра Microsoft Access Error 3047/повреждены.
- Загрязненный вирусом и поврежденный Microsoft Access Error 3047.
- Microsoft Access Error 3047 злонамеренно или ошибочно удален другим программным обеспечением (кроме Microsoft Access).
- Другая программа находится в конфликте с Microsoft Access и его общими файлами ссылок.
- Неполный или поврежденный Microsoft Access (Microsoft Access Error 3047) из загрузки или установки.
Продукт Solvusoft
Загрузка
WinThruster 2023 — Проверьте свой компьютер на наличие ошибок.
Совместима с Windows 2000, XP, Vista, 7, 8, 10 и 11
Установить необязательные продукты — WinThruster (Solvusoft) | Лицензия | Политика защиты личных сведений | Условия | Удаление
This Microsoft Access tutorial explains how to work around the error 3047 Record is too large when using DoCmd.TransferSpreadSheet Method.
Although Microsoft Access is a database management system, unfortunately it does not allow users to put too much data in the database.
According to Access 2016 specifications , Access Table record is limited to 4000 characters, and the total size of Access is 2GB.
If you create a Table that contains a record with more than 4000 characters, you will receive an error message Error 3047 Record is too large. The simplest solution is to cut the record characters, but if not possible, you can create an Excel Worksheet (maximum 32,767 characters for a Cell) and use Linked Table, so that you can create a Query that contains more than 4000 characters, and then export to Excel without problem.
However when you want to mass export Queries, you need to do it in VBA. When using DoCmd.TransferSpreadSheet Method to export the Query / Table to Excel, Error 3047 Record is too large would prevent you from doing that.
Root cause of Error 3047 Record is too large
According to the explanation of Microsoft website, the error is due to the follow reason.
You defined or imported a table with records larger than 4K. This error occurs when you enter data into the record — not when you define the table structure. Redefine the table by making some fields shorter, removing unneeded fields, or moving some fields to other tables.
However this statement doesn’t explain how the error happens when using DoCmd.TransferSpreadSheet Method. In my testing, it is possible to export a Query that contains more than 4000 characters from Linked Table, but when you have to JOIN another Table, I believe Access performs checking.
Workaround for Error 3047 Record is too large
In order to export Query with records of more than 4000 characters in VBA, try to use DoCmd.OutputTo Method.
The syntax is slightly different from DoCmd.TransferSpreadSheet Method, but you will get well formatted headers same as that in Access Query. However, the file size is larger and it takes longer time to export.
But there is one problem with DoCmd.OutputTo Method. If you export the Query to XLSX format. Some Access formats cannot be perfectly supported, and an error pops up when users try to open the exported workbook, and users have to save the file as a new one for auto repair. To work around this issue, you can export as XLS format instead.
DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="Query1", OutputFormat:=acFormatXLS, Outputfile:="C:testtest.xls"
- Remove From My Forums
-
Question
-
Hello Developers,
I am trying to write Access/VBA Update query in my Table which Throws Run time error 3047 «Record is too Large» error. My access file size is 206 MB. The table which i am trying to update has 34 fields in total with
Text fields= 15
Number= 6
Memo= 6
Date/Time= 3
Multi-value text field= 1 (Choice field with 4 choices)
Multi-value number field= 3 (Lookup fields which look into seperate table for value)
There are only 83 records in my table. The field which i am trying to update is a text field.
I tried first deleting only 4 multi-value field and keeping rest of field and than run the update query, it throws same error
than I tried deleting 4 multi-value field and 6 memo field and than run the update query, it throws same error
finally I deleted all fields and just keep 4 fields (text-2, number-1 and date/time-1) the update query woks fine, but i need all 34 fields in my table as they are needed for report.
Any suggestions please. Thanks for your time.
Answers
-
A record is limited to 1000 characters, total, excluing Memo, OLE fields and (probably) multi-values fields (I never use them). That is not about the number of records, but about the length of a single record which must fit on a «page», and pages are
2K, but with overhead, you are left with the equivalent of 1000 characters (uncompressed, characters of 2 bytes). As soon as the space which is really NEEDED exceed the page, the error happens.( I stress the point that the error happens when
the need is REAL, not at design time, as example, where the text length is just a MAX, not effectively a space reserved for the field)I suspect that the use of so many fields indicate that the design breaks some normalisation rule to start with. You should try to redesign the table. That is, instead of:
ClientID, January, February, March, …, December
1010 1 null 4
22have
ClientID, MonthID, Amount
1010 January 1
1010 March 4
…
1010 December 22and you can always use a crosstab to get back the «visual presentation» you were playing with, but now, you would have a much more efficient table design, for queries and data manipulation.
-
Marked as answer by
Friday, September 16, 2011 12:06 PM
-
Marked as answer by
You maybe starting to think that Microsoft Access is a buggy and is a ‘full of bugs’ application becoming very unstable indeed but the complexity and the power of what MS Access provides out ways some minor and silly errors that can occur.
Error code 3047 refers to records being too large for a database file and typically triggered when attempting to import data from another external application.
The common method you will see this is when you use the DoCmd.TransferSpreadSheet method which is a VBA command to import and export datavia typically Excel.
Ironically, Microsoft Access is a large database management tool but still has issues handling the wrong size type of data and there are size restrictions (even for the latest 2016 version). As a reminder, take a look the specification sheet for Microsoft Access to know what limits are set so you can determine the correct methods for data migration and population – it may help and avoid the time-wasting errors!
If you create a Table that contains a record with more than 4,000 characters, you will certainly receive an error message Error 3047 (Record is too large). The simplest solution is to cut the record characters, but if not possible, you can create an Excel Worksheet (which can contain a maximum of 32,767 characters for a Cell) and then use the ‘Linked Table’, so that you can then create a Query that contains more than the 4,000 characters being exceeded and repot out from there.
However, when you want to mass export Queries, you need to do it in VBA using DoCmd.TransferSpreadSheet Method to export to Excel, the Error 3047 will also raise its ugly head andprevent you from doing that too.
Importing data into a table for larger volume character sets may require a tweak to the table design to allow it through namely by changing from an existing‘Text’ data type to a ‘Memo’ data type which can hold larger characters (bytes of data). Naturally, some performance may be affected if you use too many fields set as a Memo field, so a balance is needed here.
Make sure the data you are importing is actually considered normalised for an Access database in the first place since this is a different way to storing data than say a spreadsheet. By observing the Microsoft Access Normalisation rules, you may actually avoid tweaking the data types and it may just nicely fit into your table structure.
Here’s a useful guide on importing data information (with our free eBook)here.
Tags: building microsoft access databases, converting microsoft access databases, deploying your microsoft access database, how to speed up slow microsoft access database, microsoft access database, Microsoft Access Database Error
Does your access database frequently encountering the error “record is too large”? If yes then just follow the workarounds needed to fix this Access Runtime Error 3047.
Issue:
Ms Access is pseudo-relational database engine from Microsoft that can be used to add, view, share, delete and organize data stored in the database. It consists of objects, macros, tables, queries, modules & forms. Former version of access database creates file with .mdb extension whereas the newer edition of access database make use of .accdb file format.
Although Microsoft access is a database management system but unfortunately it doesn’t allow users to put over the limit data in the database.
When access database data limit exceeds the following error “record is too large” seems to encounters each time when tries to update or add a text field in table. usually the issue generate when the record size exceeds about 2000 bytes for all fields combined.
Resolution to fix “record is too large” error
Fix 1: redefine the table again by creating some short fields or by removing unnecessary fields.
Fix 2: You can avoid this access error 3047 by using fields with the Memo type instead of Text type. Set the field type property to 12 in order to get the memo type, instead of 10 to get a text type. When memo field crosses the limit of 250 bytes or whenever the 2k limit is reached on a record, visual basic will automatically puts the memo fields on a separate page in the database file. If you text field has such related data, you could improve space usage by concatenating the fields into one large memo field.
Fix 3: Use Access Repair And Recovery Solution
If you don’t wants to go with the manual or programmatic code then also you need not to worry just try the Access Repair And Recovery tool. As, it is the simplest and easiest way to recover up your lost Access Database password. This is the most appropriate software that is particularly designed to repair corrupt or damaged MS Access database files. It comes with powerful algorithm that scans even severely corrupted access database files.
Conclusion:
Try the fixes carefully but if you are not so sure about performing manual solution correctly then go with automatic solution i.e Access Repair & Recovery Tool. As it can easily be performed by novice user and doesn’t require having any technical knowledge.