VBA & Macro Problems

How to Copy Excel or Access Records to a History Table Using VBA

Emma Brown Oct 15, 2026 869 views

Question details

The user needs a reliable procedure to copy active records to a permanent history table while assigning a specific, user-selected export date to each archived record.

How to Copy Excel or Access Records to a History Table Using VBA

Product: Excel and Access
Device & OS: not provided
Scenario: Exporting and archiving current workflow data into a permanent historical database table during a routine backup or export process.
Observed behavior: The VBA command fails or throws a syntax error when attempting to insert the user-selected date, typically due to improper string and variable concatenation.

Before you start

Before modifying your VBA scripts or running append queries, create a backup copy of your database or workbook to prevent accidental data loss or duplication.

Solution 1Recommended

Fix VBA String Concatenation for the Export Date

Use this solution to resolve VBA syntax errors by correctly isolating the form control variable from the SQL command string using an ampersand.

When building an SQL command inside VBA, form controls cannot be read directly inside quotation marks. You must break the string and concatenate the control's value using an ampersand (&).

Open the VBA Editor

Press Alt + F11 in your application to open the Visual Basic for Applications (VBA) editor.

Locate the Append Procedure

Find the macro or event procedure (such as a button click event) that handles copying the records to the history table.

Add the Ampersand (&) Operator

Locate the SQL string referencing your date control. Break the quotes and add an ampersand before the reference. For example: strSQL = "INSERT INTO HistoryTable (ExportDate) VALUES (#" & Me.txtExportDate.Value & "#)".

Run and Test

Save your code, close the editor, and trigger the export procedure to ensure the records copy over with the correct date.

Fix VBA String Concatenation for the Export Date

Formatting Dates: Always enclose dates in SQL strings with hash marks (#) to ensure the database engine interprets them correctly as Date/Time values rather than math equations.

Solution 2

Use an Archive Field to Filter Active Records

Instead of moving data across tables, keep all records in one table and use an archive status column to filter out historical data from daily views.

Solution 3

Automate Archiving Using Data Macros (Access Only)

Utilize table-level data macros to automatically copy records to a history table whenever they are modified or deleted, requiring no user interface.

Free Microsoft Office alternative

Edit Excel VBA Macros Seamlessly in WPS Office

If you are working heavily with Excel macros and looking for a cost-effective solution, WPS Office provides a powerful, free alternative to Microsoft Office. It offers robust spreadsheet tools, advanced macro support, and full compatibility with complex data tracking workflows.

1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
2. Install WPS Office: Run the downloaded executable file and follow the quick on-screen setup instructions.
3. Open Your Macro Workbook: Launch WPS Spreadsheet and open your existing .xlsm files to continue running and editing your VBA projects.

Free and lightweight alternative to Microsoft OfficeFully compatible with Microsoft Excel formats including .xls, .xlsx, and .xlsmBuilt-in support for advanced macros to automate history table exportsFamiliar user interface ensuring a seamless migration for existing users

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA SQL append command fail with a syntax error?

Syntax errors in VBA SQL commands usually happen due to missing spaces, quotes, or improper string concatenation. Ensure you use an ampersand (&) to separate variables like 'Me.txtExportDate.Value' from the literal text of the SQL string.

Can I track historical records without moving them to a completely new table?

Yes. A highly recommended alternative is adding an 'Archived' Boolean (Yes/No) field to your existing table. You can then configure your daily forms and views to filter out records where Archived is set to True.

What file format do I need to save my Excel file in to preserve VBA macros?

To preserve your VBA code, you must save your workbook as an Excel Macro-Enabled Workbook (.xlsm) or an older Excel 97-2003 Workbook (.xls). Saving as a standard .xlsx file will permanently strip away your macro code.

Excel & Data Problems