M
Mike NG
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
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