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

Question

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

Andy G · Accepted Answer · 2013-07-19 00:16:35Z

1

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.

answered Jul 19, 2013 at 0:16

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Benson Igarabuza · Accepted Answer · 2019-11-22 22:45:06Z

0

I had a similar issue. Oddly enough, it was because I had to either have the same field names in the Excel file as the MS Access Database or specify the fields in the SQL string. Similar to what was mentioned in this post.

answered Nov 22, 2019 at 22:45

Benson Igarabuza

514 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related