How to Fix ‘Invalid Argument’ Error Message in MS Access?

Many Access users have reported experiencing the “Invalid Argument” error message while performing different tasks in the MS Access application. The error can appear when using VBA code to import or export data from Access database file to Access project or vice versa. It usually occurs if there are issues with arguments or parameters specified in functions, macros, queries, or other objects in the VBA code. This error can occur in the following scenarios:

• When trying to run an update query after upgrading the Access application to a new version.
• When using the Import function (TransferSpreadsheet method) in the VBA code.
• When using the linked Table Manager to link external data sources in Access.
• When executing queries to import large-sized database files.

Now, let’s learn about the reasons behind this error and the solutions to resolve it.

What Causes the ‘Invalid Argument’ Error Message in MS Access?

There are no known reasons for the “Invalid Argument” error in MS Access. The error may occur due to any of the following reasons:

• If the data types mentioned in your linked tables do not match with the data types specified in the queries. (If your database contains linked tables).
• Incorrect linked table configuration.
• Your queries rely on the external libraries that are missing.
• You're calling a VBA function with an incorrect argument. For example, passing a text string to a function that expects a number.
• You have accidentally passed the incorrect parameters or arguments in the Import function (TransferSpreadsheet method).  
• You’re using an incomplete SQL query in the database.
• The database file's linked tables, queries, or other elements are corrupted.
• If you are running queries on a large-sized database file.
• Incorrect operators or expressions are used in the queries, like Update/Append query.
• Issues with the Macros action.
• You’ve assigned invalid values while initializing variables in the VBA code.
• Disabled Macro Settings.

Methods to Fix ‘Invalid Argument’ Error Message in MS Access

The Invalid Argument error may occur if the macro actions in queries failed due to disabled Macro Settings. Check and enable all macros options in Access’ Macro Settings. However, these settings can make your system vulnerable to malicious codes. After checking and enabling Macro settings if you still face the error, then follow the below troubleshooting methods.

Method 1: Check VBA Code Compatibility with your MS Access Application Version

The Invalid Argument error can occur due to compatibility issues between the VBA code and the application version of Microsoft Access. This usually happens when the VBA code in the database is written and designed for a lower version and you are using it in a higher version. For example, you are executing VBA code created before the Office 2010 release (VBA version 6 and lower) on a 64-bit version (Office 2019). You can check and modify the code to make it compatible with both 32-bit and 64-bit versions. Here are the steps:

• In Access database, click Database Tools, and click the Visual Basic option.

• In VBA Editor, click on the desired database module.
• Check the VBA code and find the statement where you have used the Declare statements. You can add three options - PtrSafe, longlong data-type, and LongPtr. 

Method 2: Create New Backend Database/Relink Tables

Some users have reported facing the “Invalid Argument” error in Access when changing the location of the backend database in Access. It happens when trying to use Linked Table Manager to update the file paths. However, you cannot change the backend location of your database using the Linked Table Manager. This tool is used to manage and update the links to the table between the frontend database and the backend database. So, you can relink tables in the database to change the location of the backend database. Here’s how:

• In the affected Access database, click External Data and then click Linked Table Manager.

• The Linked Table Manager window is displayed. Select the file with linked tables.

• Click the Relink option.
• The Access prompts you to select a new data source location.
• Enter the correct location. You can either give a new name to linked tables or keep the existing name.
• Then, follow the on-screen instructions.

Check the column status to check whether the linked tables have been relinked or not. If it fails, then click the Relink option.

Method 3: Check and Reduce the Database File Size

The “Invalid Argument” error in Microsoft Access can occur due to large size of the database file. You can face such an issue while importing/exporting data between Access and other applications. The Access database file you’re importing should not exceed 2 GB size. Also, the records in the database file you are importing should not exceed the maximum record size limit, i.e., 4000. In such a case, you can split the Access database.

Method 4: Check and Compile VBA Code

Many Access users have reported getting the “Invalid Argument” error message while executing the Append query, Update query, or Import function. The error can appear if you are using incorrect methods, functions, arguments, and parameters in the queries. Sometimes, a simple typo error can disturb the entire syntax used in the code. You can check and compile the VBA code to check and fix the error. Follow these steps:

• In your Access file, click on Database Tools and select Visual Basic.

• In VBA Editor, click Debug, and select Compile VBA Project.

This will highlight the line with errors, which help you to troubleshoot any typo, syntax, and other variable issues.

Method 5: Check Missing References

Sometimes, you can encounter the “Invalid Argument” error due to missing references library in Access. It generally occurs if you are executing queries that rely on the library which is missing. You can check and select the Reference Library. Following are the steps to do so:

• In Access VBA Editor, click Tools and then select References.

• In the References dialog box, check the listed references under available references and ensure that all are selected.
• If any reference is not selected, then select it and click OK.

Method 6: Compact and Repair your Database

The corruption is also one of the reasons behind the “Invalid Argument” error in Access. You can compact and repair your database file using the “Compact and Repair” tool in MS Access. Follow the below steps:

• First, ensure the database file is closed.
• Then, in your Access application, click on the Templates page, and click Blank Database. 
• Click File and then choose Close.
• Now, click Database Tools and then select Compact and Repair Database.
• Select the affected database file in the Database to Compact From dialog box.
• Click on the Compact option.
• You will find the compacted and repaired database at the exact location where you actually stored the database file.

If the Access database file is highly corrupted, then the tool may fail to provide the intended results. In this case, you can use a reliable third-party Access repair tool. Stellar Repair for Access is one such tool that can repair severely corrupted Access database files. This tool can recover all the data from corrupted .accdb/.mdb file, including queries, macros, and tables. You can even check all the repaired data before saving it.

Closure

MS Access can throw the “Invalid Argument” error message if it fails to recognize the queries in the VBA code. It can occur due to incorrect Macro Settings, incompatible VBA code, missing references, etc. Try the solutions discussed above to eliminate the error. The error can also occur due to corrupted queries and macros in the database. In such a case, you can use Stellar Repair for Access. This advanced repair tool can repair and restore the damaged queries in the Access database with complete integrity.