PDA

View Full Version : [SOLVED] Operation must use an updateable query



emosms
12-26-2014, 10:26 AM
Hi, I got really sick of trying to do sql insert on an excel named range.
The named range is an excel table.
I managed to do sql select, both select * and specific columns.

Connection string:
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;ReadOnly=0;"";"
Set cn = CreateObject("ADODB.Connection")
Recordset:
Set rs = CreateObject("ADODB.Recordset")
' Is that ok?
Opening recordset:
an ultra simple insert statement
strSQL = "insert into [" & sheet & "$D10:F15] values (1,2);"
rs.Open strSQL, cn, 3, 3
' tried any kind of Cursor Types and Lock Types
Checking if the range is ok:
Debug.Print rng2.AllowEdit
- True
The workbook is NOT read only.
I right click both the excel file and the folder which the file is in and added full controll to IUSR, guest, guests, everyone, everything, my grandma, whatever.........
(not exhaustive, but I don't think this is the problem)
---
What coud I do now? I want to do a simple app retrieving and comparing/ evaluating some data and I am really borred to manipulate the ranges in a procedural manner.
But it seems I cannot do this with several sql queries and a small amount of vba as i wanted.
---
Is it possible at all to do insert/update within excel ranges, using
Provider=Microsoft.ACE.OLEDB.12.0 ?
---
getting recordset with select statment and trying
rs.addNew
- run time error 3251. Current recorset does not support updating. This may be a limitation of the porvider or of the selected locktype.
I allready played with locktypes in rs.Open

Kind Regards

emosms
12-26-2014, 11:21 AM
No success till now with rs.execute(sql)
also with DAO recordset
trying to insert, update,delete - impossible :D

SamT
12-26-2014, 03:27 PM
emosms,

Welcome to VBA Express.

Don't give up. tis the season for delays. over the whole World.

People will start returning here soon.

emosms
12-27-2014, 07:22 AM
Yes, it is not a good thing to work on christmas :D.
I waste a whole afternoon, missing to read this on the stackoverflow site:
---
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;"";"
One must remove IMEX = 1 from the connection string.
The error messages otherwise are not very informative about the problem.
---
answer 1 - stackoverflow site, link seems to be forbidden
insert and update work OK. Delete is not working:
Deleting data in a linked table is not supported by this ISAM.
---
In general, the JET engine will not allow rows deletion, but if necessary one can delete and bulk delete sheet rows (and the table rows on these rows).
Of course, in a procedural manner, getting rows by number, looping, e.t.c. - what I eagerly tried to avoid in my app.

SamT
12-28-2014, 09:50 AM
Is your problem now solved?

emosms
12-29-2014, 12:49 AM
Problem solved.

And don't forget, if you get datatype mismatch to use sql datatype conversion functions INSIDE the SQL strings you pass.
F.ex.
"select price from [sheet$range_address] where date > Cdate(" & date_param & ")"
The data type per column is set via right click/format cells (if you must use specific datatype)
Very lame, but wasted me another couple of hours.
---
Btw - the range of an excel table has an address excluding the headers row.
So using HDR=YES sets that the first row is non-data and you can use the values there as column headers(db table column names).
But, getting the range by table name allready excludes the headers row, so the JET enginge looks for column headers on the first table's data row.
It is nice, that the named range for the table dinamically shrinks and expands with the table, incl. adding and removing columns.
I needed to make a get_tbl(sheet_name, range_name) function, returning a range addres including the original headers row of the excel table.

Correct me if I am wrong.

Kind regards