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
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