The text file specification <name> does not exist. You cannot import, export, or link using the specification. (Error 3625) (4 Viewers)

[StuartBowen]

OK. Banging my had for hours now.

I am doing something exceptionally simple yet it fails.

I am importing some data in various CSV files in a loop into Access, and all CSV files have the same format.

I need to check a file exists for a client (filename is in format domainname.co.uk-Users.csv
Because Access doesn't like importing a file with extra .'s I rename if it exists to M365Temp.csv
I then import to M365Temp, run a query to append to a main table, delete the M365Temp.csv file and loop though to the next client.

DoCmd.TransferText acImportDelim, "M365Temp", "M365Temp", "C:\CIPPExports\" & "M365Temp.csv", True

Some clients run through fine yet others I hit the 'The text file specification <name> does not exist. You cannot import, export, or link using the specification. (Error 3625)' error. However, it does because it worked 2 minutes ago importing the same file with the same name using the same specification. I also see it in MSysIMEXSpecs.

Also, thinking it may be the file structure content, if I just take the line that imports, pop that on a button and run, it is fine.

Any ideas how to troubleshoot this one?

BTW the CSV files are on sharepoint, but I have a 30 sec pause now just in case it is synching and locking. It is definitely synched and fine before it tries to import.

 

[StuartBowen]

Quick update, I moved the location of the files to c:\CIPP\ so outside SharePoint. I run now and one of the files it struggled on, it runs the problem code, imports the file into M365Temp (definitely there) goes on to a delete query, asks if I would like to delete, I click yes and it then comes up with the 'The text file specification <name> does not exist. You cannot import, export, or link using the specification. (Error 3625)' error!!! I click on debug and it is not on the import line but on the delete query line but it comes up with the same error. I have run a repair and compact, still the same.

 

[DickyP]

"Also, thinking it may be the file structure content, if I just take the line that imports, pop that on a button and run, it is fine."

Teaching grandmothers to suck eggs, I assume you have checked the detailed content of the failed records?

And how are you deleting the Temp file?

 

[StuartBowen]

this is the code in question

'On Error Resume Next

Dim Rst As Recordset
Dim Rst2 As Recordset
Dim SngSec As Long

'open the table that the report reads to make sure there is a record in it.

Set Rst = CurrentDb.OpenRecordset("CompanyClientReport")

Rst.MoveFirst

Do Until Rst.EOF

[Forms]![Switchboard]![Recipient] = Rst!Recipient
[Forms]![Switchboard]![CompanyName] = Rst![Company Name]

Set Rst2 = CurrentDb.OpenRecordset("SELECT DISTINCTROW M365Users.Tenant FROM CompanyClientReport LEFT JOIN (CompanyMicrosoft LEFT JOIN (M365Users RIGHT JOIN M365Tenants ON M365Users.Tenant = M365Tenants.defaultDomainName) ON CompanyMicrosoft.Microsoft = M365Tenants.customerId) ON CompanyClientReport.[Company ID] = CompanyMicrosoft.[Company ID] WHERE (((CompanyMicrosoft.[Company Name]) = " & Chr(34) & [Forms]![Switchboard]![CompanyName] & Chr(34) & ")) GROUP BY M365Users.Tenant;")
Rst2.MoveFirst

strPath = "C:\M365\"
MsgBox strPath & Rst2!Tenant & "-Users.csv"
If FileExists(strPath & Rst2!Tenant & "-Users.csv") = True Then

Name strPath & Rst2!Tenant & "-Users.csv" As strPath & "M365Temp.csv"
SngSec = Timer + 30
Do While Timer < SngSec
Loop
DoCmd.TransferText acImportDelim, "M365Temp", "M365Temp", strPath & "M365Temp.csv", True
DoCmd.OpenQuery "M365 User Refresh 2 Delete"
DoCmd.OpenQuery "M365 User Refresh 3 Append"
Kill strPath & "M365Temp.csv"
DoCmd.DeleteObject acTable, "M365Temp"

End If

Rst.MoveNext

As mentioned, I have suspected the content of the particular csv but it has just imported fine, content of M365Temp table looks good, no import errors and yet it fails with the same error on the following delete query.

Also, if I do a manual import using the same specification from sharepoint, it is fine.

 

[DickyP]

Just a thought in passing before I look further - how many records are in the csv files? Are the ones that are failing bigger than the others? That would explain why a single import works and multi file exports don't as the processing needs a finite minimum time to reset.

 

[StuartBowen]

thought I would reboot PC but still same.

So just run again, first file went fine with only approx 8 records. Second one did the thing where it actually imports the 82 records, but errors with the import specification error on the delete query line.

 

[StuartBowen]

just did a switcheroo. I swapped the names on the files so the 82 record import went first. It was fine. Then went to import the 8 record file. it errors. This file was fine two minutes ago.

 

[StuartBowen]

I altered so that it runs and as soon as it is successful in importing one file, it exits. Intention was to see if it was the fact it was importing a second file. It imports the first then exits. I click the button to import again, it doesnt import the first company file because the file doesnt exist any more (deleted in first run) and it then fails on the second with same error.