ACE Connection String From Excel 2010 to Itself

Question

I am trying to use SQL in VBA to update data in an existing table in Excel (same file as vba code). This is being written with SQL queries and updating with the intent of moving the source data to a DB shortly after rollout, but there are reasons why not right now. I keep getting the error "Operation must use an updateable query." Some sources show ReadOnly in the Extended Properties and some do not; both have been tried. StatusData is a named range in the Excel file. SELECT statements using the named range are working fine using the following connection string:

DBFullName = ThisWorkbook.Path & "\" & ThisWorkbook.Name

Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBFullName & "';" & _ "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"

Changes from the query connection include using the "Excel 12.0 Macro" extended property and ReadOnly=0, which have been incrementally. Broken code is as follows:

Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBFullName & "';" & _
  "Extended Properties='Excel 12.0 Macro;ReadOnly=0;HDR=Yes;IMEX=1';"
Set Cn = New ADODB.Connection
Cn.Open ConnectionString:=Cnct
strSQL = "UPDATE StatusData SET Notes='ttt' WHERE [Program Category]='something' AND [Program Name]='something' AND [LN]='1' AND [SN]='101';"
Cn.Execute strSQL, RecordsAffected, adExecuteNoRecords

Remove the Single Quotes from "Extended Properties='Excel 12.0 Macro;ReadOnly=0;HDR=Yes;IMEX=1';" and try again? "Extended Properties=Excel 12.0 Macro;ReadOnly=0;HDR=Yes;IMEX=1;" — Siddharth Rout
Then the error changes to "Could not find an installable ISAM." — Engineer playing Programmer
I found a page (social.msdn.microsoft.com/Forums/en-US/…) that stated that I could not use IMEX=1 or IMEX=2. The data has a lot of NULLs and mixed numbers and text in fields. If I cannot use IMEX=1 for UPDATE or INSERT statements, is there a way to tell it to interpret the data as strings always? It appears that it uses MS Access rules because I tried CAST and CONVERT with no luck. CStr() converts the data retrieved if it is not nulled by the provider first. — Engineer playing Programmer
Success with setting IMEX=0. This will lead to problems with my IF EXISTS that I would like to use though with the mixed data type fields in the (Select...) that will decide to use UPDATE or INSERT. Any ideas? — Engineer playing Programmer

Coder375 Coder375 · Accepted Answer · 2015-07-13T09:26:08

I had problems using IMEX so in the end I stopped using it for this purpose, this connection string has never given me problems for using ADO/ACE to connect back to the workbook (usually for a SQL GroupBy), but you might need to do some conditioning on the data before making the connection (I take the entire table into an array and loop though the fields making changes as needed, then dump back to the sheet), easy enough as its already in the same workbook of course:

Dim conn    As Object
Dim sPath   As String

sPath = "C:\offlinestorage\temp1.xlsx"
Set conn = CreateObject("ADODB.Connection")
With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = Format("Data Source=" & sPath & ";Extended Properties='Excel 12.0 XML;HDR=Yes';")
    .Open
End With

ACE Connection String From Excel 2010 to Itself

1 Answers