SELECT * INTO Access FROM csv ; running VBA ADO from Excel

Question

upd.: Here is a Syntax that finaly solved the issue: 'createSQL = "SELECT * INTO newlyСreatedTableName FROM [fileName.csv] IN 'folderPath' [""Text;HDR=YES;FMT=Delimited""]"

Struggling with data transferring. I have a .csv with a 6 mln rows and trying to import it to Access DB running code in Excel. I have a simple code.

Function getAccCN(ByVal dbFullPath As String) As Object
    Set getAccCN = CreateObject("ADODB.Connection")
    getAccCN.connectionString="Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" & dbFullPath
End Function

Function createSQL() As String
    createSQL = "Select * INTO [" & _ 
                fileName & "] FROM [Data source = " & _
                repFile.ParentFolder.Path & _ 
                "\; Extended Properties=""Text;HDR=Yes;FMT=Delimited""]." & _ 
                repFile.Name ' repFile is a *.csv as "Scripting.FileSystemObject"

    Debug.Print createSQL ' returns following:
     ' Select * INTO [Classification] FROM [Data source = \\av-fs01.av.local\profiles$\...\Project IQVIA\; Extended Properties="Text;HDR=Yes;FMT=Delimited"].Classification.csv
     ' *.accdb and the table name and  *.csv have the same base name - "Classification"
End Function

Function uploadCSV() as Boolean
Dim CN as Object
    Set CN = getAccCN(repFile.ParentFolder.Path & "\" & baseFileName & ".accdb")
    CN.Open
    CN.Execute createSQL() ' this creates Error
    Exit Function
ErrHandler:
    Debug.Print Err.Number ' = -2147467259  "Wrong argument."
       'CN.ERROR - Arguments are of the wrong type, out of range, or conflict with each other.
End Function

Headers and first rows of the source.

So, I can't find out how to solve the issue. I'would be very thankful for anyhelp.

Answer 1

Here is a Syntax that solved the issue:

'createSQL = _
     "SELECT * INTO newlyСreatedTableName" & _ 
     " FROM [fileName.csv] IN 'folderPath' [""Text;HDR=YES;FMT=Delimited""]"