ADO programming

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hi all -

I am in the process of trying to learn to use ADO. But it occur to me that
where are the ADO features for creating queries, tables and execute queries,
query defs and table defs?

Thanks,

Ben


--
 
ADO (and DAO - my preferance) is a means for connecting to a database and
executing queries. While there may be tools out there that will help with
creating the code necessary to work with a database via ADO/DAO, you pretty
much have to create the statements and code yourself.

Google 'ADO Tutorial' and 'DAO Tutorial'
 
Hi dch3,
I understanding that I would need to code, but where are the features ADO,
that would allow one to program Access like those found in DAO. Specifically
I am not sure if there are such equivalence in ADO for creating queries,
tables in Access.

Thanks,

Ben
 
Ben said:
Hi dch3,

I understanding that I would need to code, but where are the features ADO,
that would allow one to program Access like those found in DAO. Specifically
I am not sure if there are such equivalence in ADO for creating queries,
tables in Access.

Thanks,

Ben
Click to expand...

To actually create new tables and queries, you need ADOX. I don't believe
the data definition commands work directly from ADO (not 100% sure), and
there's nothing else built in without adding the ADOX library.



Rob
 
Hi Rob,

Thanks for that info. So I guess that's the piece that does all the other
functions that DAO used to do. Thanks.

Ben
 
Out of curiosity, why are you switching to ADO? DAO is still the preferred
method for dealing with Jet databases (i.e. MDB files), and its successor
ACE is the preferred method for dealing with ACCDB files.
 
Hi Doug,

First off, I just want to say thanks for helping me with my posts in the
past. Well,I been looking at Ken Getz, Litwin and Roman's books about Access
and ADO.

It sounded to me ADO was to stay and DAO into obscurity. In my experience,
I have seen an Excel app using ADO to query against oracle, the result came
back in nearly instantaneously written out on an Excel worksheet.

But when I wrote the same application in Access (2003) using DAO, and
through automation writing the recordset out an Excel spreadsheet, it was far
from near instantaneous, granted the code had to work through COM automation
instead of directly within Excel. But the difference in time was very
substantial, the recordset came in about 1300 records. In the case of Excel
app, it came back so fast it was unbelievable. In my case using Access with
DAO, I got the recordset and write out to Excel, it took close to 1 min.
Same query.

Why does it matter, because, our internal users are accustom to that near
instantaneous response. My Access application improved one of the other old
queries in the Excel app, it went from 15min wait to about near
instantaneous, but that was due some query optimization I did.

The other reason was just to learn a new skill set, but as I dig deeper, I
realized ADO can't create queries, tables...etc, things I routinely do in
DAO. And this is frustrating. I recently learned that I have to use ADOX,
after I did some web search, posting my questions and reading Roman's book
(Access Database by ORA)

I am not sure if my Access DAO response was slow primarily due to COM
automation, but the Excel app using ADO was REALLY fast. Your thoughts?


Thanks,

Ben
 
I think the main difference is about the recordset type you used. If you use
a FIREHOSE recordset, with ADO, you have the minimal recordset possible,
having fetched just the first record (from the SELECT statement), and which
will wait for you to explicitly make a move next record to fetch the next
record, while with DAO, you may have, by default, a recordset which pump the
whole table (all records, or at least, a set of bookmarks). Note that a
firehose recordset is a ReadOnly ForwardOnly recordset. You can also try not
force waiting the end of execution of a parallel thread of execution by not
asking, as example, for a MoveLast: generally, Access will open the
recordset and fill it on a parallel thread of execution, so your VBA thread
of execution may not have to wait that the recordset is fully initialized.
You can see it in action when you open a long query: the first page is
displayed quite fast, but the "y" value in the displayed label "x record of
y" may take much more time to appear.

Access, Excel, VBA, DAO and ADO all work based on COM technology.

ADO can use tables created by DAO without problem. Can also use tables
create in Oracle, by whatever tool you used to speak to Oracle in order to
create the tables. The tool used to create the tables is irrelevant, just
the tables themselves matter.




Vanderghast, Access MVP
 
If I may jump in here,
COM certainly contributes to the delay.
If one of the two is doomed to obscurity, it is ADO. If you haven't
noticed, in 2003, the default was reverted from ADO back to DAO.

Native DAO is actually faster than ADO.

Neither ADO nor DAO create tables or queries. They are both data access
methods.

Linking to external data sources is different for ADO and DAO, but that is
because they are different methods.

Comparing a direct load from Oracle to Excel to Oracle to Access to Excel is
not a reasonable comparison.

But, that is not to say you should not use ADO if you feel you must.

I do think the consensus would be that even when dealing with another
database engine like Oracle or SQL Server, most will not use ADO, but will
still opt for DAO.
 
You can also execute command, like "CREATE TABLE ... ", "CREATE VIEW ... "
(for the queries without parameter) and "CREATE PROC ... " (for the queries
with parameters). It is a matter to use JET -SQL - DDL, which is a language
independent of DAO, ADO, ADONET, or whatever... Well, in fact, some of these
constructions, defined by Jet 4.0, only work if you use ADO to execute the
command (as if DAO had not been updated to expose publicly these Jet 4.0
extensions).



Vanderghast, Access MVP
 
Reading a few more posts, I see what you are talking about with creating
tables.
If you are using ADO, you can still use DAO to programmatically create
tables or you can use SQL DDL
 
Michel, David, Doug -

Thanks for sharing your thoughts...wow, I learned alot from what you said.
I appreciated. Yes, I am much more comfortable using DAO. Thanks again, it
was very helpful.

Ben




--
 
Back
Top