SQL Queries in VBA

[javydreamercsw]

Since no answered yet my earlier post, I've been working with sq
queries trying to fix that. Dunno if someone knows about queries in VB
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 Updat
query it always give me a syntax error. It seems like a right write
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

 

[Jake Marx]

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]