ADO newbie help please
 

Hi
I have an excel UserForm application, accessing an XLS file as its
database. Until now, I have just being doing my own manual reads /
writes, and have come to the conclusion it will be much neater to use
ADO, which a work colleague has introduced me to


I've been using this code

Public Sub TestUpdate()

ExcelADO "F:\TESTDB.xls", _
"UPDATE [DB$] set Nme = 'FREDDY BLOGGS' where MemIdr = 1 ;"

End Sub

Public Sub ExcelADO(filename As String, SQL As String)

Dim oRS As ADODB.Recordset
Set oRS = New ADODB.Recordset
Dim sConnect As String

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & filename & ";" & _
"Extended Properties=Excel 8.0;"

oRS.Open SQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
End Sub


And here's a snippet of my existing data file
http://tinyurl.com/hsjh

What happens when I run the SQL, and try and re-open the test database,
I get "Excel has caused an error in EXCEL.EXE - excel will now close"

The problem seems to be with cells H2 and J2 on the DB sheet. If I
visit both of those, press the delete key, and save the file again,
everything is hunky dory

As my real database is quite big, can my code be modified to work with
these cells, or is there some conversion routine I can run across the
spreadsheet. I've done an INSERT into an empty database with not all
fields populated, and then run the UPDATE again as above, and this
worked fine


I am a bit concerned that my database could get corrupted so easily by
something like this if something else should crop up in future. Is
there anything I can do to protect myself
--
Mike