Importing data to or from Access database is little dicey task to perform. Encounterance of 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 drops 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 about some commonly encountered Access database import errors and ways to resolve such import errors.
Error 1: Fix MS Access Error Importing Excel File
It is seen Access encounters import /export errors while working with Excel *.xlsx, .xls file. The problem mainly occurs when the cells contain data more than 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 releasing of Access 2010 Service Pack 1. This prevent MS Access application from opening an existing Excel*.xlxs file having cell data more 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 memo/long text fields size under 8224 bytes
- If you are exporting, deleting the existing .xls file right before performing the export task by making use of the same file name.
- Now open up your .xls file before starting the import/export process from Access.
Error 2: Fix Import Specification Error in Microsoft Access
well there are some Microsoft Access import errors which is quiet frustrating and seems like 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.”
Most probably you must have get confused between the previously saved set of “import steps” with “import/export specification”. Meanwhile using the Docmd.TransferText command.
You need to consider the below mentioned sample VBA code which uses the Docmd.TransferText command for importing a delimited file (from a path stored in string variable strInputFileName) within a table named “tbl_Access_Import_Data” using the 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 then try to reference this saved “import steps” along with the Docmd.TransferText method.
As you can’t reference “import steps” only with “Import/Export specifications”.
Make use of the Import Text Wizard for defining and delimiting the columns within a specified .csv file. In the above figure I have also indicated 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 is defined previously.
I have created a saved “import step” by mistakenly with the name “My Saved Access Import Spec”. This time the name is according to the value which is erroneously passed to method Docmd.TransferText in code.
This results in fixing the ‘Run-time error 3625’.
For saving the legitimate Export/import specification which is successfully referenced with Docmd.TransferText method. After entering into the last window of Import Text Wizard hit the “Advanced” button right before tapping to the “Finish“ option.
Tap to the “Save As” option (Step 2 above) present on the right-hand side of your windows.
Assign name and then save your true Import/Export specification name and tap to the “OK” option.
If the same window gets open every time then you can hit the “Specs…” button. To see the name of all previously saved Import/Export specifications.
In the above shown figure I have only one Import/Export specification named with “My Real Saved Access Import Spec”.
Once the true Import/Export specification get reference in VBA Code. The code will be executed 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 The Fix File 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 with field which you need to fix for “ImportErrors” in table/report.
Open the freshly created table to check which thing is actually making issue with the import.
Import error table:
In figure you can see there is 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 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 different length and data type.
In the above discussed case datatype for “CC” is “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, data type of field “CC” which is “Text” must be changed to “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 huge amount of data.
In the latest version of MS Access there is two type of data type “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 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 type too. After doing this when you import this file, you won’t get any truncation error.
Methods To Fix Any Kind Of Import Error:
Here are some techniques I’ve learned over the years to deal with them.
- If in your access application you are using TextTransfermethod for importing CSV file. after the import process Access skip or round 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 short format like this 15-Nov-2017, but your Access application always consider 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 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 Driveras ODBC driver.
Go The Professional Recommended Solution:
Meanwhile the MS Access import error if your lost up your MS access table, report or any other object. Then you can fix it 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 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.
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 diminishes the issue. Try all the aforementioned fixes to resolve any kind of issues related to MS Access import error.