How to Export MS Access Tables with Over 500 Rows to SharePoint
Question details
The user needs to export a large MS Access table (over 500 rows) to a SharePoint list without the transfer stopping or dropping data due to field limitations.

- Product
- Microsoft Access, Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Migrating a local database table containing thousands of business records (like contacts or companies) from MS Access to a SharePoint site.
- Observed behavior
- The export process stops prematurely, transferring only 500 records. Attempting to paste records directly yields field size limit errors, storing remaining rows in a local 'Pasted Records' table.
Verify that you have administrative permissions on the target SharePoint site to create lists and that your MS Access database is not currently locked by another user.
Troubleshoot Field Limits and Migrate via Append Query
Identify incompatible field limits by testing a small batch of records, then use an append query to transfer the remaining records to SharePoint.
Migration problems are frequently caused by indexes, relationships, foreign keys, or incompatible field types. For instance, numeric fields in Access may exceed permitted field size limits in SharePoint, causing the export to halt at the row where the error occurs. Isolating the table structure first ensures a smooth data transfer.
Before making any modifications, right-click your original MS Access table, select 'Copy', and then 'Paste' to create a complete backup copy of your data.
Open a copy of the table and delete all but a few records (e.g., 10-20). Navigate to the 'External Data' tab and use the 'Export to SharePoint List' option to migrate this small table.
Once the list successfully creates on SharePoint, go to the 'External Data' tab in Access, select 'New Data Source' > 'From Online Services' > 'SharePoint List', and link the new list back into your database.
Right-click the newly linked SharePoint table and select 'Design View'. Inspect the field types and sizes, specifically checking for numeric fields that exceed SharePoint's permitted limits. Adjust the field properties to be compatible.
Go to the 'Create' tab, click 'Query Design', and add your original backup table. Change the query type to 'Append', select the linked SharePoint list as the destination, and run the query to push the remaining records.
Need a Lightweight Office Suite for Daily Tasks? Try WPS Office
While complex database migrations require specialized tools like MS Access, most of your daily document, spreadsheet, and presentation needs can be handled efficiently with a lighter, cost-effective solution. WPS Office is a highly compatible and free alternative to Microsoft Office.
- 1. Download the Installer: Visit the official WPS Office website to download the free installation package tailored for your operating system.
- 2. Install WPS Office: Run the downloaded installer and follow the simple on-screen prompts to set up the software.
- 3. Open Office Files: Launch WPS Office and directly open your existing Word, Excel, and PowerPoint files without needing any format conversion.

Frequently Asked Questions
Why does Access only export 500 records to SharePoint when I have thousands?
This usually happens when the export process encounters a data type mismatch, a field exceeding SharePoint's size limits, or conflicts with indexes and foreign keys. The export engine halts at the specific row containing the incompatible data, which in many cases falls around the 500-record mark depending on the dataset.
How can I identify which field is causing the SharePoint export error?
Try copying and pasting a small batch of records directly from Access into the SharePoint list Datasheet view. If there is a field size or type issue, Access will generate an error and typically create a local 'Pasted Records' table. Reviewing this table will show you exactly which field violated SharePoint's rules (e.g., a numeric field that is too large).
Can I increase the maximum field size limit in a SharePoint list?
While you can modify certain column settings in SharePoint (like increasing character limits for multi-line text fields), strict data type limitations—like standard numeric field boundaries—cannot be bypassed. You must adjust your Access table's field properties in Design View to match SharePoint's supported column types before migrating.




