SQL problem - Jamie

[Todd Huttenstine]

Dim objConn As ADODB.Connection
Dim szConnect As String
Dim szSQL As String
' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\QTD\Sales.xls;" & _
"Extended Properties=Excel 8.0;"

' Create the SQL statement.
szSQL = "INSERT INTO [Sheet3$A2:A2] VALUES
('TestValue1'); "

' Create and open the Connection object.
Set objConn = New ADODB.Connection
objConn.Open szConnect
' Execute the insert statement.
objConn.Execute szSQL
' Close and destroy the Connection object.
objConn.Close
Set objConn = Nothing

Above is my code. Jamie Collins gave me advice(bottom of
this post) but I cant seem to get 1 part to work.

I have 3 questions:
When I run this code it inserts the value "Testvalue1"
into cell A3. But why when I open Sales.xls and delete
the value and then run the code again, I get the
error "This table contains cells that are outside the
range of cells defined in this spreadsheet"? How do I
make it to where I can simply update this?

Also I cant figure out where to put the HDR=Yes and/or
HDR=no statement in the extended properties.

Last, how can I make it to where I can delete a value or
update a value instead of having to insert a new line to
the table?


Jamies Response:
To update a single cell in an existing table you use
HDR=No in the
extended properties of the connection string and use
UPDATE sql syntax
such as:

UPDATE [Sheet3$A2:A2] SET F1='TestValue1';

F1 is the default name Jet assigns to the first column
where the
column name is unknown or invalid. The inserted value must
match the
data type for the *whole* column. Better to use a key
column with
HDR=Yes e.g.

UPDATE [Sheet3$]
SET MyDataCol='TestValue1'
WHERE MyKeyCol=55
;

INSERT INTO is used to add a row to a table. With Excel,
it is always
added to the end of the existing table, subject to having
enough space
for the new row.

You can use INSERT INTO to put a value into a cell but
AFAIK this only
works on a sheet without an existing 'table' e.g.

INSERT INTO
[TotallyBlank$A1:A1]
(F1) VALUES ('TestValue1')
;

will put the value into cell A2 on an existing totally
blank sheet
named TotallyBlank. Did I say cell A2? Sure did. The only
way I've
found to insert the value into A1 is:

CREATE TABLE
TotallyBlank
(
TestValue1 VARCHAR(255)
)
;

This puts a column header into cell A1, in a new defined
Name named
TotallyBlank on a new sheet named TotallyBlank (unless
sheet
TotallyBlank already existed, then it would create a new
sheet named
TotallyBlank1).

Jamie.

 

[Jamie Collins]

I'm changing my advice <g>. For a single cell, even on a totally blank
sheet, use UPDATE rather than INSERT INTO e.g.

UPDATE [Sheet3$A2:A2] SET F1='TestValue1';

changes the value in cell A2, so none of this 'specify the cell above'
malarkey. Warning: do not attempt to update cell A1 e.g. I tried

UPDATE [Sheet3$A1:A1] SET F1='TestValue1';

and for me Excel never recovered and I had to restart my machine. Also

UPDATE [Sheet3$A65535:A65535] SET F1='TestValue1';

made Excel go GPF, so there may be a limit for this kind of operation
(needs some more experimentation).

The DELETE sql syntax is not supported for Excel. The workarounds
include setting the value to null e.g.

UPDATE [Sheet3$A3:A3] SET F1=Null

which leaves a 'blank' row in the table. The other workaround is to
create a new table e.g. using CREATE TABLE or a SELECT..INTO query,
both of which create a new sheet if required. You can additionally
issue a DROP TABLE on the original.

If you delete the cell value but not the cell itself, then Jet 'knows'
there is a table there (because Jet created it) and considers it to
have a null row (in Excel terms, this pertains to the UsedRange
object). You can't INSERT INTO an existing row, even if it is null,
but an UPDATE would presumably now work, subject to matching the
column's existing data type.

If you delete the whole column, you will do the manual equivalent of a
DROP TABLE and subsequent INSERTs should succeed. But as I said, I now
consider an UPDATE to be preferable.

For to add a second parameter (e.g. HDR=No) to the connection string,
you must enclose them in quotes e.g.

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\QTD\Sales.xls;" & _
"Extended Properties=" & _
Chr$(39) & "Excel 8.0;HDR=No" & Chr$(39)

Jamie.

--

 

[Todd Huttenstine]

Hey Jamie

I can never get the update line to work. It always
freezes up and I have to end task on Excel.

 

[Jamie Collins]

...

Hey Jamie

I can never get the update line to work. It always
freezes up and I have to end task on Excel.

I did some more experimentation today and had no problems updating
single cells: totally blank sheet or existing data, cell A1 and cell
A65536, no crashes. The only time the insert failed was trying to
insert e.g. a string into a column of numerics but this is expected
behavior and I merely got a run-time error.

I've even tried you code, with a few amendments for my circumstances
(posted below), with no problems.

Things that were different today include the version of ADO: 2.1
yesterday, 2.7 today. Also, I think at one point yesterday my source
workbook was open, creating a memory leak which may have caused
problems from that point onwards.

Sub test()
Dim objConn As ADODB.Connection
Dim szConnect As String
Dim szSQL As String

' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\db.xls;" & _
"Extended Properties='Excel 8.0;HDR=No'"

' Create the SQL statement.
szSQL = "UPDATE [Sheet3$A1:A1] SET F1='TestValue1';"

' Create and open the Connection object.
Set objConn = New ADODB.Connection
objConn.Open szConnect
' Execute the insert statement.
objConn.Execute szSQL
' Close and destroy the Connection object.
objConn.Close
End Sub


Jamie.

--

 

[Todd Huttenstine]

Hey Jamie

Thanks so much. That worked. I really appreciate your
help over the last few days.

This is something Ive been trying to accomplish for a few
months but have not had the kind of help you provided.
Thanks again.


Todd Huttenstine

-----Original Message-----
...

Hey Jamie

I can never get the update line to work. It always
freezes up and I have to end task on Excel.

I did some more experimentation today and had no problems updating
single cells: totally blank sheet or existing data, cell A1 and cell
A65536, no crashes. The only time the insert failed was trying to
insert e.g. a string into a column of numerics but this is expected
behavior and I merely got a run-time error.

I've even tried you code, with a few amendments for my circumstances
(posted below), with no problems.

Things that were different today include the version of ADO: 2.1
yesterday, 2.7 today. Also, I think at one point yesterday my source
workbook was open, creating a memory leak which may have caused
problems from that point onwards.

Sub test()
Dim objConn As ADODB.Connection
Dim szConnect As String
Dim szSQL As String

' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\db.xls;" & _
"Extended Properties='Excel 8.0;HDR=No'"

' Create the SQL statement.
szSQL = "UPDATE [Sheet3$A1:A1] SET F1='TestValue1';"

' Create and open the Connection object.
Set objConn = New ADODB.Connection
objConn.Open szConnect
' Execute the insert statement.
objConn.Execute szSQL
' Close and destroy the Connection object.
objConn.Close
End Sub


Jamie.