Search code examples
excelvbams-accessado

File is Read only using VBA Excel with ADO connection to Access

I am trying to write a macro that pushes Excel data into Access. Originally I simply wrote it to open Access as an object and do it that way, but it was horribly slow. So now I am writing it to use an ADO connection. I can get it to work if I write from any file other than the one I am in, however, since this is to a be a Ribbon Button and only use the file I am in, I need it to work so. Below is my code. The error I get is:

Run-time error -2147217911 (80040e09) Cannot update. Database or object is read-only.

This is stupid since I am not updating the Excel file, but such is Microsoft. Any thoughts?

Transfers2 = ActiveWorkbook.FullName & "].[" & ActiveSheet.Name & "$]"
'C:\temp\ContractToolSetV2.xlsm].[Sheet1$]  - this would work, since it is not the file open

Dim con As Object ''Create ADODB Connection
Set con = CreateObject("ADODB.Connection")

SQLcmd = "Insert INTO tblExcelImportCT Select * FROM [Excel 12.0 Xml;Readonly=1;HDR=YES;IMEX=2;ACCDB=YES;DATABASE= " & Transfers2 

con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\nrpi.local\shared_files\ADCC\Commercial Auction Division\BA Support\BAmangment.accdb;"
con.Execute _
    SQLcmd
Solution

  • Yes, you receive this message because the file is open. You could:

    • Copy the worksheet to a new workbook, close and save this, transfer the data then delete this (temporary) file using Kill
    • Create an ADO recordset and loop through the worksheet data, inserting it (AddNew) it the Access table. There are a number of examples of this to be found on the internet.