Vba can you query a recordset

You can create a Recordset object based on a stored select query. In the following code example, Current Product List is an existing select query stored in the current database. If a stored select query does not already exist, the OpenRecordset method also accepts an SQL string instead of the name of a query.

The previous example can be rewritten as follows. The disadvantage of this approach is that the query string must be compiled each time it runs, whereas the stored query is compiled the first time it is saved, which usually results in slightly better performance.

Have questions or feedback about Office VBA or this documentation? As with any query, you can easily view and copy a select query's SQL statement by right-clicking the query's title bar and choosing SQL View.

However, unlike in the Datasheet view of a query, which is plainly visible on-screen, a recordset is visible only to VBA, not to humans. Creating a recordset in VBA usually takes several lines of code. As always, you have a ton of options for how to write the code. The syntax of statements that you need in order to create a recordset from one or more tables in the current database generally looks like this:.

Recordset myRecordSet. I myRecordSet is the name that you want to give to your recordset. You use whatever name you put here to refer to the recordset from elsewhere in the procedure. Connection is, you have to tell VBA where to look for it.

Connection is, even if you don't. Start with a simple example. Suppose that you want to create a recordset named myRecordSet that contains all the fields and records from a table named Customers.

Open statement is sufficient, as shown here:. Open "[Customers]". Search Advanced search…. New posts. Search forums. Log in. JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding. You are using an out of date browser. It may not display this or other websites correctly. You should upgrade or use an alternative browser. How do I query a recordset object in VBA? Thread starter jc Start date Aug 15, Local time Today, Joined Aug 15, Messages Hi all, I've been banging my head against this problem for a while now and making no progress and am hoping someone cleverer than me can provide some assistance in solving it, or point me in the right direction.

Here goes I am working solely in the VBA environment and would like to perform an automated process that returns a recordset based on the results of a previously created recordset assuming of course it is possible in the first instance. OpenRecordset strSql rs1. OpenRecordset strSql rs2.

MoveFirst The rs2 query returns the results of that query into what I though was a memory resident table referred to as "jc".

This is most likely where I am going wrong! Using this reference, I perform another query OpenRecordset strSql rs3. MoveFirst And from this point I would be able to process the results if any that I need to Make sure it exists and that its name is spelled correctly.

I am happy to provide more details if it would help solve this problem and would really appreciate any feedback that anyone has to offer. Thanks, JC. Banana split with a cherry atop. Local time Yesterday, Joined Sep 1, Messages 6,