Importing data to or from the Access database is a little dicey task to perform. Encountering import errors meanwhile the import process complicates the process more. Mainly if this leads to data loss then losing a single piece of data is not acceptable.
If you are facing this MS Access import error while working with few records. Then tasks of entering data manually and missing value is not a big deal.
Moreover, if the Microsoft Access import errors drop thousands of values then you must get into a problem.
So, before the problem gets too complicated it’s better to fix MS Access import errors. Here in this post, we will discuss some commonly encountered Access database import errors and ways to resolve such import errors.
How To Fix Import Errors In Access?
In this section, we will discuss 3 different Access import errors and their fixes.
Error 1: Fix MS Access Error Importing Excel File
It is seen Access encounters export/import errors while using the Excel *.xlsx, .xls file. The problem mainly occurs when the cells contain data above 8224 bytes.
Take a look over the error that displayed during MS Access error importing excel file.
Import Error: The wizard is unable to access information in the file ‘<filename>’. Please check that the file exists and is in the correct format.
Export Error: External table is not in the expected format.
The main reason behind this issue originates after the release of Access 2010 Service Pack 1. This prevents MS Access application to open an already existing Excel*.xlxs file having cell data higher than 8224 bytes.
Try the following manual methods to fix MS Access error importing Excel file.
- Make use of the *.xlsx format
- keep all the long/memo text fields size under 8224 bytes
- If you are exporting, deleting the already existing .xls file right before doing the export task with the same name as the file.
- Now open up your .xls file before starting the export/import process from Access.
Error 2: Fix Import Specification Error in Microsoft Access
Well, there are some Microsoft Access import errors that are quite frustrating and seem like a puzzle-solving task to debug. Among those error one very frequently encountered Access import error is:
“Run-Time error ‘3625’: The text file specification ‘My Saved Access Import Spec’ does not exist. You cannot import, export, or link using that specification.”
an error occurred trying to save import/export specification
Most probably you must have to get confused between the previously saved “import steps” set with “import/export specification”. Meanwhile using the Docmd.TransferText command.
You need to consider the below mentioned VBA code sample which makes use of the Docmd.TransferText command for importing a delimited file (from the stored path having string variable name strInputFileName) right within a table named “tbl_Access_Import_Data” making use of import specification.
Private Sub cmd_Import_Table_Click()
Dim strInputFileName As String
‘Set Path to Local CSV File. This file will be imported into an Access Table.
strInputFileName = “C:\Users\Desktop\Access Data\Access_Import_Data”
‘ Use a Macro to Import a delimited file
‘ “My Saved Access Import Spec” = Import Spec
‘ “tbl_Access_Import_Data” = Destination Access Table
‘ strInputFileName = hardcoded path to source csv file
DoCmd.TransferText acImportDelim, “My Saved Access Import Spec”, “tbl_Access_Import_Data”, strInputFileName
You need to save “import steps” and after then try to reference this saved “import steps” along with the method Docmd.TransferText.
As you can’t reference “import steps” only with “Import/Export specifications”.
Make use of the Wizard “Import Text” for defining and delimiting the columns within an already specified .csv file. In the above figure, I have also highlighted the table in which my data gets imported. After then press the Finish option.
After tapping the Finish option. On the next screen, you have to save the import steps which are defined previously.
I have saved the “import step” by mistakenly with the name “My Saved Access Import Spec”. Well at this time the name is according to the value which is erroneously moved to method Docmd.TransferText in code.
This results in fixing the ‘Run-time error 3625’.
For saving the legitimate Export/import specification which is perfectly referenced with Docmd.TransferText method. After entering into the last window of Import Text Wizard hit to the “Advanced” button right before tapping to the “Finish“ option.
Tap to the “Save As” option (Step 2 above) present on the right side of your Windows.
Assign a name and after then save your right Import/Export specification name. Now tap to the “OK” option.
If you are encountering the same window every time then you can press the “Specs…” button. For seeing the name of all previously saved Export/Import specifications.
In the above-shown figure, I have only one Export/Import specification having the name “My Real Saved Access Import Spec”.
Once the right Export/Import specification gets the reference in the VBA Code. This will execute the code as intended.
Error 3: Fix Microsoft Access Import Truncation error
If you are trying to import table to Access from any file and you are getting “ImportErrors” in your created table/report. Then the chances must be that your table is not imported correctly.
Causes Behind Import Truncation Error
Let’s know why you are getting this MS Access Import Error Field Truncation.
I caught this issue when I was trying to import a .dat file and converting it to an Access table.
At the time of importing in Access, I have noticed that it fails to import some fields due to encountrance of error. Along with that, it notified you of the field which you need to fix for “ImportErrors” in the table/report.
Open the freshly created table to check which thing is actually taking issue with the import.
Import error table:
In the figure you can see there are 3 columns:
Error: this column contains error encountered in File Import
Field: This column has the Field Name where the error occurs
Row: it indicates the row number in which error is encountered.
In the shown table you can see that in the first row we have “field truncation” error for row number and field name. through this table shown in the figure, you will get all information needed for searching the content of that row.
MS Access Import Error Field Truncation occurs when the variable type is not that big to keep the data assigned to it. The fact is different variable type covers the different length and data type.
In the above-discussed case datatype for “CC” is the “Text” variable. This text variable has the maximum capacity of holding 255 characters.
|Text||Maximum capacity of holding 255 characters (numbers and text)|
|Memo||Maximum capacity of holding 65,536 characters (numbers and text)|
So, to resolve the above issue, the data type of field “CC” which is “Text” must be changed to the “Memo” data type. Do this before ending up the importing task.
You all must be thinking that memo data type should be used everywhere for resolving the MS Access import truncation error.
So, let me clear this to you that use this memo data type only when working with a huge amount of data.
In the latest version of MS Access, there are two types of data types “Short Text” and “Text”.
To make changes of datatype follow the steps mentioned here:
- In the File-> Import screen:
Import Text Wizard
- Tap to the “Advanced” button present on the left bottom. This will open the following screen.
Now, you need to look for the “Field Name” of “CC” and change the “Data Type” to “Memo”. If necessary, change all other fields’ data types too. After doing this when you import this file, you won’t get any truncation error.
How To Avoid Import Errors In Access?
Here are some techniques I’ve learned over the years on how to avoid import errors in Access.
- If in your access application you are using TextTransfermethod for importing CSV file. after the import process Access skip or around your values. Add this line right above the line where you have call action TextTransfer:
[YourSourceFile.txt].[YourColumn].numberformat = “@”
- If the field having problem contains the monetary values like dollars. Try to change the data type to double or integer.
- If the source file contains the dates in a short format like this 15-Nov-2017, but your Access application always considers this in a long format 11/15/2017 0:00:00. In that case, you need to make a query having calculated fields that format.
ExpDate: Format([YourDateField], “Short Date”)
- If you are having a source data in a text file format that MS Access application refuses for importing. Then make use of Access application for establishing an ODBC connection with the file. For this, you need to create a new data source in Windows. After then select Microsoft Text Driver as ODBC driver.
Go The Professional Recommended Solution:
Meanwhile, the MS Access import error if you lost up your MS Access table, report, or any other object. Then you can get it back by using MS Access Repair Tool. With this professional software, you can easily fix corrupt, damaged, or inaccessible Access database. Apart from this, the tool is also very helpful to restore your corrupt tables, indexes, queries, and relations.
The powerful algorithm of this tool efficiently scans severely corrupted Access database files. And it’s highly interactive GUI interface helps the user to easily repair Access database without hampering the original structure of the file. You can also recover the password-protected forms or modules and many more.
A free trial version is available that you can install to check the features and reliability of the software. This tool can easily fix all minor /major database errors and problems occurred in MS Access 2019, 2016, 2013, 2010, 2007, 2003, 2002, 2000 on almost all version of Windows operating system such as Windows 8/7/Vista/XP/2003.
Managing data properly before starting up the importing process can significantly diminish the issue. Try all the aforementioned fixes to resolve any kind of issues related to MS Access import error.