How to delete files with SQL using VBA

[learning2fly]

Hello,

I am using an ODBC link to a MySQL database. I am controlling this through an Excel VBA application.

My VBA code includes the following line to open the database:

Set dbData = OpenDatabase("", , False, "ODBC:&quot

I then have no problem adding some record to the database using SQL. I use the commands:

sqlAdd = "INSERT INTO marketdatapx(network, date1, hour, idp, session, offertype, prc1, prc2, prc3, prc4, prc5, prc6, prc7, prc8, prc9, prc10, vol1, vol2, vol3, vol4, vol5, vol6, vol7, vol8, vol9, vol10) VALUES('IEEE57', 777000," & BidPeriod & ", 222, 0, 'b'," & Price1 & ", " & Price2 & ", " & Price3 & ", " & Price4 & ", " & Price5 & ", " & Price6 & ", " & Price7 & ", " & Price8 & ", " & Price9 & ", " & Price10 & " ," & Vol & ", " & Vol & ", " & Vol & ", " & Vol & ", " & Vol & ", " & Vol & ", " & Vol & ", " & Vol & ", " & Vol & ", " & Vol & ""

dbData.Execute sqlAdd

However I cannot delete from the database and when I use the commands:

sqlClear = "DELETE FROM marketdatapx WHERE network = 'IEEE57' and date1 = 777000 and hour = " & BidPeriod

dbData.Execute sqlClear

I get the error "Operation must use an updateable query".

Eventhough, when using the SQL command directly on the database (using MySQL front), this line works perfectly. (I have tried using the line without the variabe 'BidPeriod' - typing it in exactly as in SQL - and it still gives me the same error).

I would be very grateful if someone could explain to me how to delete entries to the database.

Thanks for your time

Gordon

 

[ETID]

Do you use a DSN, and if so is it defined as read only?

 

[learning2fly]

I do use a DSN but the 'configure' settings box does not have a 'Read only' option. I do not think it is read only anyway as I am able to write to the database (described above.)

 

[ETID]

Do you have delete authority to the DB?

 

[learning2fly]

Yes I have authority to delete and I can delete the entries in question using MySQL front from my machine.

 

[ETID]

[READING] ....GOTTA STUDIE ON THAT ONE!?

 

[ETID]

...AND LEARN TO SPELL STUDY