Others

How to Fix SharePoint Linked Table Column Changes in Microsoft Access

Chanuka Geekiyanage Oct 22, 2026 868 views

Question details

Users experience unexpected changes in column names and values when copying SharePoint-linked tables to local Access databases.

Fix SharePoint Linked Table Column Changes in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Copying a SharePoint-linked table to a local database table for offline use or data manipulation.
Observed behavior: Using DoCmd.CopyObject unexpectedly alters the schema; the ID field may change to 'Color', and additional fields may incorrectly display date and time values.

Before you start

Ensure you have created a backup copy of your Microsoft Access database before executing any SQL queries or altering table schemas.

Solution 1Recommended

Use a SELECT INTO Query Instead of DoCmd.CopyObject

Bypassing the DoCmd.CopyObject method and using a direct SQL query prevents Access from altering the schema during the table copy process.

The DoCmd.CopyObject function can sometimes misinterpret complex SharePoint list schemas when converting them to local Access tables. Using a make-table query (SELECT INTO) ensures the exact data structure and records are preserved without unintended column renaming.

Open Query Design

Launch your Microsoft Access database, navigate to the 'Create' tab on the top ribbon, and click on 'Query Design'.

Switch to SQL View

Close the 'Show Table' dialog box that appears. Right-click the new query tab and select 'SQL View' from the context menu.

Enter the SELECT INTO Query

Type the following SQL command: SELECT * INTO local_table FROM linked_table; (Replace 'local_table' with your desired new table name and 'linked_table' with the exact name of your SharePoint-linked table).

Run the Query

Click the 'Run' button (the red exclamation mark) on the 'Query Design' tab to execute the command and create your local table with the correct columns.

Use a SELECT INTO Query Instead of DoCmd.CopyObject

Verify Your Data: Open the newly created local table in Design View to verify that the ID field and all other columns match the original SharePoint list perfectly.

Solution 2

Seek Assistance in the Office VBA Community

If the SELECT INTO workaround fails or you encounter highly specific VBA behavioral changes, consulting the community can provide deeper insights.

Free Microsoft Office alternative

Looking for a Free, Lightweight Office Suite?

While WPS Office does not include a direct relational database program like Access, it is a highly compatible, free Microsoft Office alternative for your daily document, spreadsheet, and presentation needs. It is lightweight, offers a familiar user interface, and supports seamless migration for Word, Excel, and PowerPoint files.

1. Visit the Official Website: Navigate to the official WPS Office website to explore the comprehensive, free productivity suite.
2. Download the Installer: Click the 'Free Download' button to get the lightweight installation package for your operating system.
3. Install and Migrate Seamlessly: Run the installer, launch the application, and open your existing Office files with zero formatting loss.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight installation that uses minimal system resources.Familiar, tabbed user interface for quick adoption and seamless migration.Powerful Spreadsheet capabilities with advanced formulas and pivot tables for managing tabular data without needing a full database application.

microsoft office alternative - wps office

Frequently Asked Questions

Why does DoCmd.CopyObject change my column names?

DoCmd.CopyObject sometimes struggles to correctly interpret complex field data types and hidden columns generated by SharePoint lists. This causes the internal mapping to fail, resulting in renamed fields (such as changing an ID field to Color).

Will SELECT INTO copy the primary key constraints from my SharePoint table?

No, a SELECT INTO query copies the table data and basic field data types, but it does not copy primary keys, indexes, or relationships. You will need to manually set the primary key on the new local Access table after the query runs.

Does WPS Office have a database application similar to Microsoft Access?

WPS Office does not include a dedicated relational database management application like Microsoft Access. However, WPS Spreadsheet offers robust tools for managing flat-file tabular data, including advanced filtering, conditional formatting, and pivot tables.

Office Quick Solutions