0

Short version: Using ADO, how can I create an Excel table with the first column containing nrs 0..100

For the last two days I've been playing around with Excel and trying to use it as an ADO table. I found it to be very non-intuitive and buggy but I would really like to get it working.

  • Updating data while the user is editing a cell seems to be impossible (any possible work arounds?)
  • Deleting records is not possible (any possible work arounds?)
  • Inserting starts at an arbitrary point that seems to be maintained by Excel. Even after dropping and re-creating te table.
  • If I create a table from SQL the headers are created on row 2 of the Excel sheet (not 1 as I would expect)

I'm using Delphi with ADO to connect to Excel.

Now suppose I want to fill the first column of a table with numbers 0..100. If I connect to Excel I must assume that the table has rubbish in it so I try to lose the records by dropping the table and creating a new one (on the same sheet). I then add new records, but they appear somewhere on row nr 20 (that's how many rows Excel thinks there are). So the first rows only SEEM empty but in reality they are just blank records. Now, how can I create the table starting from row 1?

So far I've played around with these queries:

UPDATE [Sheet1$] SET [R5_L3]=null, [Time]=null, [Offset0]=null, [Offset1]=null
SELECT * FROM [Sheet1$]
INSERT INTO [Sheet1$] ([R5_L3], [Time], [Offset0], [Offset1]) VALUES ('1', '1', '1', '1')
DROP TABLE [Sheet1$]
CREATE TABLE [Sheet1$] ([R5_L3] VARCHAR(40), [Time] VARCHAR(40), [Offset0] VARCHAR(40), [Offset1] VARCHAR(40))

(1) Clears the table --> works fine except that I have idea how to target a specific row after that. (2) Works fine (3) Inserts records starting wherever Excel thinks it should start (and it's wrong most of the time) (4) Clears the Excel sheet (table) including the headers (5) Creates a new table with header on row 2 (instead of row 1). Inserting still starts on arbitrary row

Bigman74066
  • 408
  • 4
  • 12
  • 1
    Why do you want to misuse Excel as a database!? As you have recognized it makes more trouble than it solves your actual problems. Put your data where it belongs: a real database, e.g. Access, SQLite, MySQL / MariaDB, FireBird, Interbase etc. Then export your data from there to Excel via ``OLE`` to present your data in the way you want it. – Delphi Coder Nov 26 '20 at 22:59
  • Or just automate Excel directly, without using ADO. I don't see anything in your code that requires you to be using ADO instead of just Excel automation. Excel is not a database. It's a spreadsheet. Treat it as such. If you need a database, use Access. – Ken White Nov 27 '20 at 01:42
  • 1
    Of course I do not want to use Excel as a database. I want to feed some real-time temperature measurements into a table, do some calculations and make a graph of that. Automation also has it's problems. I posted this because I needed to know if someone successfully did something similar in the past. For now it seems to buggy to use... – Bigman74066 Nov 28 '20 at 11:24
  • Someone suggested I add the ADOX label to my question, but I think that ADOX has anything to do with this. The question is about using the sheets in Excel as a database table so you can read and write from and to Excel as if it where a database table. I have reviewed my question but I don't know how to make it more clear than it is now. Suggestions for improvement are welcome but be concrete and read the question before telling me it's all wrong. – Bigman74066 Dec 31 '20 at 16:33
  • You can't use an Excel workbook as a database. It's not designed for that purpose, does not support simultaneous multi-user editing, and is simply the wrong choice. If you need a database, use a database. For small apps, an Access table using ADO is simple, and it will support multiple users, complex searches and other things in a suitable way. If you feel like you need to use Excel, it's time to learn something new and learn how to use a proper tool for the job. – Ken White Dec 31 '20 at 21:53
  • Please read the question and the comments before commenting. I'm definitely not trying to use excel as a database. – Bigman74066 Jan 02 '21 at 18:23

0 Answers0