Is it possible to create a Recordset without a Table or Query?

[prabha_friend]

Yes. You read it right. Without any objects. Assume the Database is empty but only with some class modules which needed it's instances to be bound to a recordset. possible?
Please confirm. Thank You.

 

[LarryE]

Well, I guess since there are no records, then you can't define a recordset. So, the answer is no.

 

[Uncle Gizmo]

Lookup disconnected record sets

 

[GPGeorge]

Yes. You read it right. Without any objects. Assume the Database is empty but only with some class modules which needed it's instances to be bound to a recordset. possible?
Please confirm. Thank You.

Sometimes, the way a question is presented leads to more questions before we ever get to answers. Like this one.

WHY do you want to do this? What is the goal? What is the environment where it'll be implemented? What kinds of values will be put into this "recordset"? From where will they be obtained? That kind of question.

 

[Uncle Gizmo]

Lookup disconnected record sets

I found a link:-

Disconnected Recordsets - Excel 2007 VBA - Engram9 VBA

The Retrieving Data from Microsoft Access Using a Plain Text Query section mentioned that getting in and out of the database as quickly as possible was an

www.engram9.info

I haven't done it but I suspect you would be able to add items to the the disconnected recordset with VBA code. like I said, not something I've done but something I might like to try one day!

 

[The_Doc_Man]

This leads to a complex problem. An ADO method exists to create a disconnected recordset, but the appropriate method creates an object. The object is not linked to much, but a disconnected recordset IS an object.

So in the really nit-picking viewpoint, the answer to your lead-in question is NO - because if your class modules need binding to a recordset, a recordset MUST exist for the binding to occur. But there is nothing wrong with the idea that the instantiation code of the object could create its own object if you don't happen to supply one. If that is not adequate, we would need to know more about the requirement.

 

[prabha_friend]

Sometimes, the way a question is presented leads to more questions before we ever get to answers. Like this one.

WHY do you want to do this? What is the goal? What is the environment where it'll be implemented? What kinds of values will be put into this "recordset"? From where will they be obtained? That kind of question.

Assume there are tables called [Plans],[Tasks] and [Plan_Tasks] respectively
Usually what we do is we select the plan_id and Task_id in the [Plan_Tasks] Table
What if the user wants the Developer to show all the Plans to already pre-entered into the Plan_Tasks table whenever a new task is generated and also wants a checkbox to be ticked by default but can be unticked if the user doesn't want to attach that particular task in that plan. Currently I have created table called Plan_Tasks_Menu to hold the checkbox value. Actually I feel that the Menu table is not actually need to present in the Database. That's why I feel better if I create a class to instanciate just to hold the true/false for the Tasks in the Memory.

 

[LarryE]

Yes, you might be able to create a table with a standalone Public Function VBA code routine, and then create a recordset from the table. You could import data into it with VBA code too. I didn't think that was what was asked, but yes you could most likely do those things. I've created tables and queries using VBA and imported data, but have never attempted to create a table using VBA with no tables already created.

 

[The_Doc_Man]

Assume there are tables called [Plans],[Tasks] and [Plan_Tasks] respectively
Usually what we do is we select the plan_id and Task_id in the [Plan_Tasks] Table
What if the user wants the Developer to show all the Plans to already pre-entered into the Plan_Tasks table whenever a new task is generated and also wants a checkbox to be ticked by default but can be unticked if the user doesn't want to attach that particular task in that plan. Currently I have created table called Plan_Tasks_Menu to hold the checkbox value. Actually I feel that the Menu table is not actually need to present in the Database. That's why I feel better if I create a class to instanciate just to hold the true/false for the Tasks in the Memory.

If ALL you are doing is remembering some T/F flags, look into TempVars as a way to hold up to 255 such flags, each of them named and thus individually accessible, but not as part of a recordset.

 

[GPGeorge]

Ah. Thanks. Well, there are two ways to look at this.

One, the general principle is that we should NOT add Plan_Tasks at all until we actually request them. That's the reverse of what you are saying. You add them and then delete the ones you don't want.

I prefer that approach.

However, you COULD do this with a disconnected recordset into which you first add all of the tasks and then remove the ones you decide you don't want. But that still would require "something" to show to the user to let them pick the items to delete. And that's going to be a form, and that means a recordset as the form's recordsource. That could be the initial recordset created as a disconnected recordset.

Argghhh. All of this makes me a bit uneasy. I have a general principle about designing and implementing relational database applications:

KISS-- or "Keep It Simple Sir".

Why go to all the trouble of writing up a wad of code to do all that? Why not present the user a subform bound to the Plan_Tasks table and let them add only the items they want and none of the items they don't want?

 

[prabha_friend]

My Proposal

His Expectation. Sorry. I cannot convince him. He is adamant.

 

[isladogs]

In case it helps, have a look at my example app:

Encrypted Split No Strings

This example app shows how an Access database can be made reasonably secure against hackers whilst still allowing full functionality to authorised users.

www.isladogs.co.uk

It was written for a different reason but uses disconnected ADO recordsets so there are no linked tables in the FE

 

[GPGeorge]

View attachment 98788
My ProposalView attachment 98789
His Expectation. Sorry. I cannot convince him. He is adamant.

Well, I once had a client like that. We were not good friends, just in a business relationship that ended when the project ended.

If you want to try the Disconnected Recordset approach, that's probably more elegant.

However, the way I would finesse this is to go ahead and add the "bonus" records in the Task_Plan table, but DISPLAY them in this subform in a query that has a second helper table joined in it. The helper table has one field, the "Yes/No" for "Select".
When the user clicked the check box next to a record that was to be removed, I ran a delete query against the table--here that would be the Task_Plan table for that item. Not so elegant, I agree, but it worked and didn't take hours of coding to implement.

 

[prabha_friend]

The answer is LINQ

 

[prabha_friend]

LINQ to Objects

 

[gemma-the-husky]

Assume there are tables called [Plans],[Tasks] and [Plan_Tasks] respectively
Usually what we do is we select the plan_id and Task_id in the [Plan_Tasks] Table
What if the user wants the Developer to show all the Plans to already pre-entered into the Plan_Tasks table whenever a new task is generated and also wants a checkbox to be ticked by default but can be unticked if the user doesn't want to attach that particular task in that plan. Currently I have created table called Plan_Tasks_Menu to hold the checkbox value. Actually I feel that the Menu table is not actually need to present in the Database. That's why I feel better if I create a class to instanciate just to hold the true/false for the Tasks in the Memory.

Without looking too closely, this may be a design - table structure issue. If your structure doesn't lend itself to what users want (or need) then either change the structure so that you can, or decide that you can't provide the required facility.

 

[MajP]

Yes. You read it right. Without any objects. Assume the Database is empty but only with some class modules which needed it's instances to be bound to a recordset. possible?
Please confirm. Thank You.

Yes you most certainly can, and yes there are good reasons to do so. See example.

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

That example is pretty similar to what you are saying. I am not saying it is a good idea to do it that way, but in theory it can be done.

Usually what we do is we select the plan_id and Task_id in the [Plan_Tasks] Table
What if the user wants the Developer to show all the Plans to already pre-entered into the Plan_Tasks table whenever a new task is generated and also wants a checkbox to be ticked by default but can be unticked if the user doesn't want to attach that particular task in that plan. Currently I have created table called Plan_Tasks_Menu to hold the checkbox value. Actually I feel that the Menu table is not actually need to present in the Database. That's why I feel better if I create a class to instanciate just to hold the true/false for the Tasks in the Memory

 

[MajP]

The answer is LINQ

No it is not even close. There is no support to LINQ in VBA. If you are talking VB.NET (VB) then you can easily build a disconnected recordset. Not sure where you think LINQ comes to play.