Access not finding Specifications Name

Question

I have an Access database that I want to import a comma-delimited text file into. I have completed the import manually and wanted to automate the procedure. I keep getting an error message that Access cannot find the named specification.

The import has been saved.

My code:

Sub ImportCSVToTable()

Dim db As DAO.Database 

Dim strFilePath As String 

Dim strTableName As String 

Dim strSpecificationName As String 

' Set the path to the CSV file 

strFilePath = "C:\temp\CalendarEvents4.txt" 

' Set the name of the table to import into 

strTableName = "CalendarEvents4" 

' Set the name of the import specification (optional) 

strSpecificationName = "ImportEvents" 

' Get the current database 

Set db = CurrentDb 

' Import the CSV file into the table 

DoCmd.TransferText acImportDelim, strSpecificationName, strTableName, strFilePath, True 

' Clean up 

Set db = Nothing 

MsgBox "txt file has been imported successfully!", vbInformation 

End Sub

Any ideas?

Answer 1

Hi troutusa,

Specifications are stored in system tables (MSysIMEXSpecs, MSysIMEXColumns), you can get all spec Names with this SQL:

SELECT SpecName from MSysIMEXSpecs

Double check if the file path to your spec is exists, here I tested your VBA code, and it works for me:

Answer 2

I was able to use your query to get the actual spec name. I put that name into my code and it worked perfectly.

Thank you so much for your help.