NextRecordset available for Access?

[imterpsfan3]

I am dynamically filling combo boxes on an unbound form. I know with a VB/SQL Server/ADO setup you can loop through several recordsets by calling the NextRecordset method.

However, I have not been able to figure out how to do this with an Access frontend and Access backend, with either ADO or DAO. I know that ADO does not support NextRecordset with an Access database, but wasn't sure for DAO.

Every example I've seen demonstrates using SQL Server or some database server. Does this mean that you can't use NextRecordset for ADO/DAO at all for an Access backend?

 

[TheAceMan1]

How are ya imterpsfan3 . . . . .

To my knowledge [blue]NextRecordset[/blue] is for [blue]ODBCDirect workspaces only![/blue]

Perhaps if you explain what you want to do would be better. Espcially since your talking dynamic. This leaves things a little vague.

Your thoughts . . .

See Ya! . . . . . .

 

[imterpsfan3]

Basically what I intend to do is load all the combo boxes on a form in one shot. Let's say I have 5 combo boxes on a form. What I do in non-Access applications is create a stored procedure that includes 5 select statements and then use the ADO nextrecordset or ADO.NET nextresult to pump the data into the combos in one function call.

This seems to be a limitation when dealing with an Access backend. It doesn't seem possible to use the nextrecordset unless you are dealing with a SQL Server database.

This form needs to be unbound because I'm dealing with a shaped recordset (hierarchical recordset) that needs to be continually synchronized. So I'm using code to populate controls.

So what I'm doing instead is opening a recordset, populating the first combo box, closing the recordset, opening it again, populating the second combo box, etc.

 

[TheAceMan1]

imterpsfan3 . . . . .

Although I've never used ADO NextRecordset, Out of curiosity I was able to test on a network with SQL Server. Had all kinds of problems! Couldn't get compound statements to work. I got as close as loading the first recordset, while the rest of the stream failed.

This is intriguing and when I can get to the network I'll keep digging.

I myself would never load combo's across a network (a definite performance hit compared to local), so out of curiosity whats keeping you from performing tied to the network and not performing similar methods (as you have) locally?

See Ya! . . . . . .

 

[imterpsfan3]

Actually what I do is create a method that returns a recordset to the client, disconnect from the connection, then I load the combos. It's returning a client cursor, so the work is being done on the client machine, not the server. This works well in a VB/SQL Server setup.

You do bring up a good point about data traversing the network. Access tends to be very network intensive. I'm going to look at doing this stuff locally as much as possible.

I keep the lookup tables on the backend database as a rule, because this data is updated and it would be too much trouble to keep it on the user's local machine and continually synchronizing it. The Access 2000 Developer's Guide to SQL Server has some interesting techniques for loading combo boxes with code. In some instances they keep lookup tables that don't change much on the user's front end or load combo boxes from xml files.

For small lookup tables I'm just going to create value lists that won't require hitting the database at all. I may even convert some of these lookups to xml files.