Query on a record set

views:

122

answers:

Query on a record set

Hi, Will a query like this on a recordset work

rs.open "select * from table where vd=1; update table set vd1 = 1 where vd=2 or vd=3;"

or is there anything wrong

thanks

It won't work - you'll have to execute them as separate commands

Presumably you've declared rs as a recordset, so you can use that to return the SELECT statement results.

I would use a SQL command to execute the second statement.

Galwegian 2008-12-03 09:49:18

is there any other way i mean other than seperating them

tksy 2008-12-03 09:52:11

thanks I will work on it

tksy 2008-12-03 10:00:15

A recordset can only be defined with a single SELECT statement (though, of course, you can UNION multiple selects if they have the same number of columns).

Any action SQL (INSERT, UPDATE, DELETE) cannot be executed with a recordset, but by using the .Execute method.

If you're using ADO, .Execute can also be used for SELECTs (it returns the rows), but Jet's native data interface layer, DAO, cannot -- .Execute works only for action queries. This seems sensible to me, but then, ADO has always seemed like a waste of time to me, personally.

Also, Jet (the db engine used by default by Access) cannot execute multiple SQL statements at once as many server-based db engines can. This is not such a big limitation as it might seem to those accustomed to batching SQL statements -- it's just different.

David-W-Fenton 2008-12-05 03:57:53

"Jet... cannot execute multiple SQL statements at once... This is not such a big limitation" - It's a HUGE show-stoppng limitation! It forces one to write multiple stored procs and trust client code to call them in the correct order or -- gulp! -- write your *database* logic client side.

onedaywhen 2008-12-05 08:47:37

Uh, Jet can't execute multiple statements, but is it not possible to batch them in a passthrough query? This would explicitly put all the processing server-side, and, it seems to me, would eliminate your objection. It still wouldn't work with a Jet back end, but, well, there's no server involved.

David-W-Fenton 2008-12-08 00:44:00

"It still wouldn't work with a Jet back end" -- that's my objection! e.g. you can't write even a simple upsert (http://en.wikipedia.org/wiki/Upsert): you have to instead write an INSERT proc and an UPDATE proc and trust client code to either pick the right one or run (and failover) both.

onedaywhen 2008-12-11 14:49:16

Then don't use JET as your back end. D'oh.

David-W-Fenton 2008-12-13 00:20:18

So, if I need an 'upsert' proc, I should avoid Jet, right? Well, then, that's just about every data-centric application I've ever written :)

onedaywhen 2008-12-17 08:43:55

If you need a stored procedure, then you're not going to be using Jet in the first place. So, I really don't understand what your problem is here.

David-W-Fenton 2008-12-19 01:40:39

My 'problem' (as you put it) is I am accustomed both Jet and to database engines that can execute multiple SQL statements in a single stored proc and the fact the two are mutually exclusive is the reason I now avoid Jet. But I'd call that a correction to your misstatement, not a problem :)

onedaywhen 2008-12-19 12:04:55

BTW I used the CREATE PROCEDURE DDL syntax in Jet for many years. I use the term "Jet stored proc" to refer to the database object created using CREATE PROCEDURE. Substitute your own term as you see fit but Jet and stored procs are not mutually exclusive.

onedaywhen 2008-12-19 12:06:09

That's just one of those lovely inconsistencies that comes about from using ADO, a generic abstraction layer to manipulate Jet, instead of native interfaces. The PROCEDURE you create is nothing at all like what you'd have in a server-based database engine.

David-W-Fenton 2008-12-23 03:39:50

CREATE PROCEDURE is native Jet DDL syntax (Jet 4.0 and ACE), nothing to do with ADO. The problem is that *you* are using a abstraction layer: the distinction between PROCEDURE and VIEW is lost on you becasue MS Access lumps them together as the generic Query object. Similarly DAO only sees QueryDef.

onedaywhen 2009-01-02 14:00:14

Yes, it was stuck into Jet DDL for consistency with ADO. What you get when you use the result in Jet (or Access) is nothing like a procedure in a server database.

David-W-Fenton 2009-01-03 02:43:29

Being able to make distinguish a SQL VIEW from other kinds of executable stored SQL is richer model than having just one Query object. That's a good thing. Who cares whether "consistency with ADO" was the driver of such change?!

onedaywhen 2009-01-05 08:49:52

ansaurus

tags:

views:

answers:

Query on a record set

related questions