ExcelBanter - SQL Queries in VBA

Hi javydreamercsw,

Your second query does not return a recordset, as it is an UPDATE query. So
change this:

sql = "UPDATE Sección SET section = '" &
Range("Información!B3").Value & "' WHERE courseno = '" &
Range("Información!B4").Value & "'"
Set rs = cn.Execute(sql)

to this:

sql = "UPDATE Sección SET section = '" &
Range("Información!B3").Value & "' WHERE courseno = '" &
Range("Información!B4").Value & "'"
cn.Execute sql

and it should work.

That said, you really don't need the first query at all. The second one
will only affect records with the specified courseno anyway, so no need to
make 2 trips to the data source. You could check the recordsaffected
argument's value after the Execute method to see if any records were updated
by the statement.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Since no answered yet my earlier post, I've been working with sql
queries trying to fix that. Dunno if someone knows about queries in
VBA but here we go...

sql = "SELECT * FROM Sección WHERE courseno = '" &
Range("Información!B4").Value & "'"
Set rs = cn.Execute(sql)
If Not rs.EOF Then 'No existe
sql = "UPDATE Sección SET section = '" &
Range("Información!B3").Value & "' WHERE courseno = '" &
Range("Información!B4").Value & "'"
MsgBox sql
Set rs = cn.Execute(sql)

The first query (select) works fine but when trying to do the Update
query it always give me a syntax error. It seems like a right writen
query to me. Same happens with this query:

sql = "INSERT INTO Sección (courseno,section) VALUES('" &
Range("Información!B4").Value & "','" & Range("Información!B3").Value
& "'"
Set rs = cn.Execute(sql)

Any Idea?

---
Message posted from http://www.ExcelForum.com/