ADO: adCmdTable vs adCmdTableDirect

[Robotron]

I am curious if anyone can shed some light on this subject.

In the help file in Access under the CommandTypeEnum heading, it lists (among other options) adCmdTable and adCmdTableDirect along with the descriptions of these options:

adCmdTable : Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query.

adCmdTableDirect: Evaluates CommandText as a table name whose columns are all returned.

My question is: What is the real difference here? It would seem that they are the same. I realize by wording that one is generated by an internally generated SQL query. What does that mean in terms of use? When would I choose one over the other for efficiency?

Thanks in advance.

Phil Edwards

 

[RoyVidar]

Hmm - seems to be almost the same, don't they;-)

It seems whenever one opens recordset, the table name is translated by ADO to "select * from table" (adCmdTable)

Dependig on provider, one can also get direct table access, which is possibe at least for Jet tables, and I think SQL Server OLE DB Providers, and is necessary when using the .Seek method (adCmdTableDirect is required).

I think the general recommandation is to use adCmdTable which makes the code more general, and not targeted against specific providers.

Roy-Vidar

 

[Robotron]

Thanks Roy. That is correct. If I know what my provider is (Access or SQL Server), is it better to use adCmdTable or adCmdTableDirect?

Phil Edwards

 

[RoyVidar]

he he - you know what your provider is, do you also know what it is going to be thru the lifespan of the system?

Sorry, I dont' wish to make a specific recomandation.

Browsing one of my references (Access 2000 Developers Handbook, Sybex), they state the following:

"If you want your code to be as general as possible for use against any OLE DB provider, you should avoid the adCmdTableDirect option. However, some operations (such as using the Seek method to locate a record using an index) can only be performed if you use adCmdTableDirect." (page 258)

I've only used adCmdTableDirect once in a production system, where I found using the .Seek method in stead of the .Find method was essential for one time critical task (of course documented properly, so that the customer knows where they'd have to make amendments should they change platform). So I'm following the "general code" thingie...

But again, no recommandations from me, but perhaps some thoughts that can help you make your decision;-)

Roy-Vidar

 

[Robotron]

Thanks Roy. I do not have the Access 2000 Developers Handbook. The book I am referencing is Programming Microsoft Access Version 2002 by Rick Dobson. Since I am no prophet, I will use adCmdTable.

Phil Edwards