Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.data.ado
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Real Newbie newbie question | New Users to Excel | |||
Help for a newbie... please | Excel Worksheet Functions | |||
Very very new newbie | New Users to Excel | |||
Help for a newbie? | Excel Worksheet Functions | |||
JET dB Newbie Needs Help | Excel Discussion (Misc queries) |