1

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
ZygD
  • 22,092
  • 39
  • 79
  • 102
Holmes IV
  • 1,673
  • 2
  • 23
  • 47

2 Answers2

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.
Andy G
  • 19,232
  • 5
  • 47
  • 69
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.

Benson Igarabuza
  • 51
  • 4