Runitime error '3131' Syntax error in FROM clause in MS Access 2016 DoCmd.RunSQL Statement

[Hashiru]

Hello All, I am having errors with this line of code:

DoCmd.RunSQL "SELECT FAIN, Scope, ALI, Project, [Resource ID],[System Source],Voucher,Vendor,[Vendor Name],[RMB Amount],[UTL Amount],Type INTO CurrentData FROM [Text;DATABASE=C:\Users\C033732\Desktop\CSVFolderPivotTable;HDR=Yes].FFR Query 20180914.csv"

Thanks in advance.

 

[xenou]

One thing to try is taking the spaces out of the filename (FFR Query 20180914.csv -- FFR_Query_20180914.csv). That should make everything considerably easier.

If that works then you've already found the problem.

 

[Hashiru]

Thanks for the quick response, the file is coming from a system that I do not control and the import will be done by other employee so that will be a problem

 

[xenou]

I would recommend you still change the filename to test if that is the problem. Then you can work out how to deal with spaces in the filename if that is the problem.

 

[james_lankford]

yeah, I get a bunch of files from other sources too
and before I import them I always make a copy of the file and rename the new file by replacing spaces with underlines

 

[Hashiru]

Thanks Xenou and James_Lankford

 

[Micron]

Never had to include a data source string in a sql statement, so just askin;
isn't the lack of quotes around the path when the path contains spaces a problem? Would

[Text;DATABASE='C:\Users\C033732\Desktop\CSVFolderPivotTable;HDR=Yes].FFR Query 20180914.csv'

fix the problem?

 

[Hashiru]

Thanks Xenou, James Lankford, and Micron. You all gave me valuable insight to the solution.