excelvbaoledbadodb

"Microsoft Access database engine cannot open or write to the file" when trying to open connection with ADODB to query .csv files

I'm trying to run SQL queries on .csv files using VBA, however when I attempt to open a connection using any directory on my local computer, I get an error

"The Microsoft Access database engine cannot open or write to the file '[directory name]'. It is already opened exclusively by another user, or you need permission to view and write its data."

I don't have administrator privileges, but I am able to open the csv files and edit them normally. Do I need administrator privileges to open this connection? Here is my code so far:

Sub generateStudentOfficeVisitsReport()

Dim currentDir As String

currentDir = VBAProject.ThisWorkbook.Path + "\"
Debug.Print ("Current Dir: '" + currentDir + "'")

Dim filter As String

filter = "SHARRSDiagnostic*.csv"

Dim currentFile As String
currentFile = Dir(currentDir & filter)
Dim cN As ADODB.Connection
Dim RS As ADODB.Recordset
Set cN = New ADODB.Connection
Set RS = New ADODB.Recordset

cN.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & currentDir & """;Extended Properties='Excel 12.0;HDR=YES;IMEX=1';")

RS.ActiveConnection = cN

Do Until currentFile = ""
    Debug.Print (currentFile)

    RS.Source = "select * from " & currentFile '
    
    'TODO
    
    currentFile = Dir
Loop

End Sub

I tried adding single/double quotes around all my parameters in the db connection string because I saw that sometimes solved issues for others, but that didn't work for me. I also tried adding parameters like "Trusted_connection=yes;" and "Integrated Security=SSPI" to see if it would satisfy some security requirement, but the former gave an error "Could not find installable ISAM", and the latter "Multiple-step OLE DB operation generated errors".

Solution

  • Your connection string should look like

    cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
    & currentDir & ";Extended Properties='text;HDR=YES;IMEX=1'")

    as you want to connect to a text file.

    Further Reading