[Access2003, VBA] Use DAO or ADO?
Emil
I'm VBA-programmer and quite new to data accessing libraries. As I read, DAO
is older, but stable. ADO is modern, but candidate to be taken over by
ADO.NET. Which of the libraries DAO and ADO would you recommend to take a
closer look at? Which librarie/framework is used by Access2007?
Thanks,
Emil
Albert D. Kallal
You can use either one. Most here prefer dao, as it is the native object
that the jet data base engine uses. So, it tends to be a better choice when
working with jet based systems. Ms-access is the developers tool, and you
can use ms-access with sql server, MySql, or the file based "jet" engine.
If you have a bunch of existing code that is ADO, or if you really
comfortable with ADO then it not really a problem if you choose ADO for your
data object. If you plan to use sql server for your back end data part, then
again one can make the case for using ADO, as it is bit cleaner when working
with sql server.
However, if you are somewhat indifferent or are 100% free to make this
choice yourself, then for sure use DAO, as that's what most of us here use,
and there are some performance advantages in some cases.
Note that up to access 97 (which is quite old now), the DAO data object was
the default setting.
Then for access 2000, and 2002 ADO was the default data object (however, you
can choose either one to use in your code). In that 2000 time period ADO was
all the rage. However, most of us access developers continued to use DAO. In
fact the folks in Redmond even noticed this, and thus for 2003, DAO once
again became the default, and remains so for 2007.
So, we kind of not changed our ways, stayed the course and ms-access has
kind of come back home for us in regards as dao being the preferred choice.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
Rick Brandt
General recommendation is DAO for Jet/Ace database access (the default
ones) and ADO for server back ends. Using either one for everything
mostly works just fine with some performance decrease in various
situations. Whether that decrease is perceptible is very doubtful (IMO).
If you have one you prefer or are already familiar with then use that.
If starting at ground zero I would stay with DAO unless you use a server
engine for the back end.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
David W. Fenton
news:nI83l.53$sC4...@newsfe04.iad:
> Then for access 2000, and 2002 ADO was the default data object
> (however, you can choose either one to use in your code). In that
> 2000 time period ADO was all the rage. However, most of us access
> developers continued to use DAO. In fact the folks in Redmond even
> noticed this, and thus for 2003, DAO once again became the
> default, and remains so for 2007.
Your description misrepresents what happened, I think -- you're way
too charitable (something no one will ever accuse *me* of!).
For Access 2000, MS was pushing certain aspects of their enterprise
software strategy. ADO was something of a replacement for ODBC (and
really much better than ODBC in a whole host of ways), and MS did a
lot of work in A2K to try to make it "enterprise-friendly" in
hooking up with SQL Server. In that context, ADO was a welcome
innovation.
But MS then turned around and deprecated Jet's native data access
library, DAO, in favor of a non-native abstraction layer, ADO. This
never made a lick of sense for Jet, and thus, for the majority of
Access applications.
In my opinion, MS was trying to kill Jet.
They wanted to get rid of it.
Note that at the same time they introduced a Jet-less front end for
SQL Server, the Access Data Project (ADP).
Access developers who weren't developing against SQL Server saw no
advantage to ADO, and continued to use DAO.
Shortly after the introduction of ADO in Access 2000, MS was pushing
.NET and they needed a data abstraction layer for that, so they
created ADO.NET. Once this happened, classic ADO as supported by
Access was basically dead -- it still worked with Access and classic
ASP, but was no longer going to be further developed because it
didn't fit in with MS's long-term strategy (which was all-.NET all
the time).
Thus, MS found itself in the awkward position of having saddled
Access with not one, but TWO obsolete data access layers. At that
point it became clear to anyone with half the brains god promised a
ham sandwich that there was no point in continuing to push ADO as
the default for all Access development, and by Access 2003, sanity
had returned, with DAO as the default library again.
MS screwed up. They never should have pushed ADO at the expense of
DAO.
They've also backtracked on ADPs, no longer recommending it for SQL
Server development.
So, just about all the innovations that were introduced in Access
2000 have been abandoned or significantly demoted in importance.
And the reason for that is because most of those "innovations" had
nothing to do with the main mission of Access as an application --
they were almost entirely motivated by external factors that were
part of MS's long-term strategies for other product lines. The fact
that they've returned to DAO and MDB/ODBC for use with SQL Server
says to me that they've stopped trying to shoehorn Access into a box
where it doesn't belong. The ACE, introduced with Access 2007, shows
me that this is not just a short-term course correction, but that
long term, MS now has a better understanding of Access and what it
should be.
My only fear now is that they will swing too far in the other
direction and deprecate developer-level features in favor of
end-user features. Some of the changes in A2K7 look that way to me
(the increased emphasis on macros as a way of avoiding security
prompts, the integration with SharePoint, the dropping of ULS and
replication in the ACE), but I'm hopeful that the right people are
at the helm of the Access development team now.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton
news:dL83l.10355$yr3....@nlpi068.nbdc.sbc.com:
> On Sat, 20 Dec 2008 16:21:59 +0100, Emil wrote:
>
>> Hi,
>>
>> I'm VBA-programmer and quite new to data accessing libraries. As
>> I read, DAO is older, but stable. ADO is modern, but candidate to
>> be taken over by ADO.NET. Which of the libraries DAO and ADO
>> would you recommend to take a closer look at? Which
>> librarie/framework is used by Access2007?
>>
>> Thanks,
>> Emil
>
> General recommendation is DAO for Jet/Ace database access (the
> default ones) and ADO for server back ends.
Er, what? I thought ADO was a good idea only for SQL Server? If you
want the convenience of linked tables, you're going to use ODBC,
whatever the back end.
And, certainly, you can't use ADO with server dbs that don't provide
an OLEDB driver (e.g., MySQL, so far as I know).
lyle fairfield
news:Xns9B7AB77D26468f9...@74.209.136.88:
<snip>
> Shortly after the introduction of ADO in Access 2000, MS was pushing
> .NET and they needed a data abstraction layer for that, so they
> created ADO.NET. Once this happened, classic ADO as supported by
> Access was basically dead -- it still worked with Access and classic
> ASP, but was no longer going to be further developed because it
> didn't fit in with MS's long-term strategy (which was all-.NET all
> the time).
ADO: ActiveX Data Objects (ADO) provides a high-level programming model
that will continue to be enhanced. Although a little less performance than
coding to OLE DB or ODBC directly, ADO is straightforward to learn and use,
and it can be used from script languages, such as Microsoft Visual Basic
Scripting Edition (VBScript) or Microsoft JScript.
from
http://msdn.microsoft.com/en-us/library/ms810810.aspx
Data Access Technologies Road Map
January 2002
Revised January 2008
--
lyle fairfield
Albert D. Kallal
> Er, what? I thought ADO was a good idea only for SQL Server? If you
> want the convenience of linked tables, you're going to use ODBC,
> whatever the back end.
True the tables are linked by odbc, but you still have to
choose a data object model for your reocrdsets. So, a lot of
people when using linked tables STILL prefer ado because that's what
they are comfortable with.
You can use CurrentProject.Connection when you have linked tables to a jet
backend to open a ado recordset.
While there is some advantages when using ADO with sql server, oracle
(at least ones that provide a oleDB connection) there also a good case
to choose ADO if you plan to upgrade to sql server in the future.
And another great case is if your SAME application needs to work with
a jet file share for some customers, but other larger customers want/need
sql server and another wants to use MySql.
So, ADO is a better abstraction and it is more data engine
neutral then DAO.
You could likely accomplish writing an application with dao that
works for the 3 above mentioned systems, but ADO does tend to
force you to be somewhat more data engine neural when you write
your sql statements.
Of course an ADP project is another matter, and you
MUST use sql server with a adp project.
David W. Fenton
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> \
>
>> Er, what? I thought ADO was a good idea only for SQL Server? If
>> you want the convenience of linked tables, you're going to use
>> ODBC, whatever the back end.
>
> True the tables are linked by odbc, but you still have to
> choose a data object model for your reocrdsets. So, a lot of
> people when using linked tables STILL prefer ado because that's
> what they are comfortable with.
Access developers? I don't think so, unless they spend most of their
time with ADPs.
> You can use CurrentProject.Connection when you have linked tables
> to a jet backend to open a ado recordset.
Why would you want to do so?
> While there is some advantages when using ADO with sql server,
> oracle (at least ones that provide a oleDB connection) there also
> a good case to choose ADO if you plan to upgrade to sql server in
> the future.
Why? If you're using ODBC, you gain exactly nothing.
> And another great case is if your SAME application needs to work
> with a jet file share for some customers, but other larger
> customers want/need sql server and another wants to use MySql.
What? Sounds like a perfect place where Jet will be the best
interface, since you can use linked tables for Jet, and ODBC linked
tables for both SQL Server and MySQL.
> So, ADO is a better abstraction and it is more data engine
> neutral then DAO.
I think that's pure bullshit. If you're using linked tables, then
Jet is involved, and it's just bloody silly to use ADO to work with
Jet, even if the back end is something other than Jet. If you're
using ODBC, then you're not communicating directly with the back end
tables.
ADO is fine for unbound apps, but it's not of much use in an app
with linked tables.
> You could likely accomplish writing an application with dao that
> works for the 3 above mentioned systems, but ADO does tend to
> force you to be somewhat more data engine neural when you write
> your sql statements.
I think that is, once again, completely BS.
> Of course an ADP project is another matter, and you
> MUST use sql server with a adp project.
And that's right where I started. ADO for ADPs, DAO for everything
with linked tables. If you're not using linked tables, then you
really oughtn't be using Access at all. In other words, DAO makes
more sense for nearly every realistic scenario. ADO makes little
sense for any Access development.
lyle fairfield
duplicated in DAO.
ADO Objects
Command Object (ADO)
Connection Object (ADO)
Error Object
Field Object
Parameter Object
Property Object (ADO)
Record Object (ADO)
Recordset Object (ADO)
Stream Object (ADO)
ADO Events
BeginTransComplete, CommitTransComplete, and RollbackTransComplete
Events (ADO)
ConnectComplete and Disconnect Events (ADO)
EndOfRecordset Event (ADO)
ExecuteComplete Event (ADO)
FetchComplete Event (ADO)
FetchProgress Event (ADO)
InfoMessage Event (ADO)
WillChangeField and FieldChangeComplete Events (ADO)
WillChangeRecord and RecordChangeComplete Events (ADO)
WillChangeRecordset and RecordsetChangeComplete Events (ADO)
WillConnect Event (ADO)
WillExecute Event (ADO)
WillMove and MoveComplete Events (ADO)
ADO Collections
Errors Collection (ADO)
Fields Collection (ADO)
Parameters Collection (ADO)
Properties Collection (ADO)
ADO Properties
AbsolutePage Property (ADO)
AbsolutePosition Property (ADO)
ActiveCommand Property (ADO)
ActiveConnection Property (ADO)
ActualSize Property (ADO)
Attributes Property (ADO)
BOF, EOF Properties (ADO)
Bookmark Property (ADO)
CacheSize Property (ADO)
Chapter Property (ADO)
Charset Property (ADO)
CommandStream Property (ADO)
CommandText Property (ADO)
CommandTimeout Property (ADO)
CommandType Property (ADO)
ConnectionString Property (ADO)
ConnectionTimeout Property (ADO)
Count Property (ADO)
CursorLocation Property (ADO)
CursorType Property (ADO)
DataMember Property
DefaultDatabase Property
DefinedSize Property
Description Property
Dialect Property
DataSource Property (ADO)
Direction Property
EditMode Property
EOS Property
Filter Property
HelpContext, HelpFile Properties
Index Property
IsolationLevel Property
Item Property (ADO)
LineSeparator Property (ADO)
LockType Property (ADO)
MarshalOptions Property (ADO)
MaxRecords Property (ADO)
Mode Property (ADO)
Name Property (ADO)
NamedParameters Property (ADO)
NativeError Property (ADO)
Number Property (ADO)
NumericScale Property (ADO)
OriginalValue Property (ADO)
PageCount Property (ADO)
PageSize Property (ADO)
ParentRow Property (ADO)
ParentURL Property (ADO)
Position Property (ADO)
Precision Property (ADO)
Prepared Property (ADO)
Provider Property (ADO)
RecordCount Property (ADO)
RecordType Property (ADO)
Row Property (ADO)
RowPosition Property (ADO)
Rowset Property (ADO)
Size Property (ADO Parameter)
Size Property (ADO Stream)
Sort Property
Source Property (ADO Error)
Source Property (ADO Record)
Source Property (ADO Recordset)
SQLState Property
Status Property (ADO Field)
Status Property (ADO Recordset)
StayInSync Property
State Property (ADO)
Stream Property
Type Property (ADO)
Type Property (ADO Stream)
UnderlyingValue Property
Value Property (ADO)
Version Property (ADO)
ADO Dynamic Properties
ADO Dynamic Property Index
Optimize Property-Dynamic (ADO)
Prompt Property-Dynamic (ADO)
Reshape Name Property-Dynamic (ADO)
Resync Command Property-Dynamic (ADO)
Unique Table, Unique Schema, Unique Catalog Properties-Dynamic (ADO)
Update Resync Property-Dynamic (ADO)
ADO Methods
AddNew Method (ADO)
Append Method (ADO)
AppendChunk Method (ADO)
BeginTrans, CommitTrans, and RollbackTrans Methods (ADO)
Cancel Method (ADO)
CancelBatch Method (ADO)
CancelUpdate Method (ADO)
Clear Method (ADO)
Clone Method (ADO)
Close Method (ADO)
CompareBookmarks Method (ADO)
CopyRecord Method (ADO)
CopyTo Method (ADO)
CreateParameter Method (ADO)
Delete Method (ADO Parameters Collection)
Delete Method (ADO Fields Collection)
Delete Method (ADO Recordset)
DeleteRecord Method (ADO)
Execute Method (ADO Command)
Execute Method (ADO Connection)
Find Method (ADO)
Flush Method (ADO)
get_OLEDBCommand Method
GetChildren Method (ADO)
GetChunk Method (ADO)
GetDataProviderDSO Method
GetRows Method (ADO)
GetString Method (ADO)
LoadFromFile Method (ADO)
Move Method (ADO)
MoveFirst, MoveLast, MoveNext, and MovePrevious Methods (ADO)
MoveRecord Method (ADO)
NextRecordset Method (ADO)
Open Method (ADO Connection)
Open Method (ADO Record)
Open Method (ADO Recordset)
Open Method (ADO Stream)
OpenSchema Method
put_OLEDBCommand Method
Read Method
ReadText Method
Refresh Method (ADO)
Requery Method
Resync Method
Save Method
SaveToFile Method
Seek Method
SetEOS Method
SkipLine Method
Stat Method
Supports Method
Update Method
UpdateBatch Method
Write Method
WriteText Method
On Dec 22, 9:57 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
Albert D. Kallal
>> You can use CurrentProject.Connection when you have linked tables
>> to a jet backend to open a ado recordset.
>
> Why would you want to do so?
Because one might have chosen to use ado in place dao in their application.
So, I am saying *if* one makes the choice to use ado, you do NOT have to
build a connection string to the back end JET share nor do you have to use a
jet odbc connection to the back end. There is a built-in connection object
created for you (it likely pulls the connection string out of the linked
table..but it not using odbc).
So, that means you can go in your code with ado:
Dim rs As New ADODB.Recordset
rs.Open ("select * from contacts"), CurrentProject.Connection
Do While rs.EOF = False
Debug.Print rs!FirstName
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Perhaps you are not clear or even aware that when using linked tables to a
jet file share you do have a built in ADO connection object. So, again my
point was **IF** you going to use a ado recordsets, you don't have create
or build your connection string to the jet back end.
So, in the above I not setting up a odbc connection to that jet back end..am
I???
>> While there is some advantages when using ADO with sql server,
>> oracle (at least ones that provide a oleDB connection) there also
>> a good case to choose ADO if you plan to upgrade to sql server in
>> the future.
>
> Why? If you're using ODBC, you gain exactly nothing.
>
You gain sql neutral code. eg:
dao:
select * from tclCustomers where city like 'new*'
The above is jet/dao syntax, but if you use an ado reocrdset, you can go:
ado:
select * from tclCustomers where city like 'new%'
Both the above are hitting a jet back end. However, the 2nd syntax will ALSO
work for mysql, oracle and sql server if I take that sql and want to run it
server side. And, if simply want to change the reocrset to pass-though, then
again I do NOT have to change the sql syntax. (in fact, if I do setup a
oleDB connection string, then I get 100% by-pass of jet, and I not have to
change the sql one bit. If you code to dao, you WILL have to change your
syntax to work with sql,mysql etc. if you want to avoid performance
bottle necks.
Note that it is not that oleDB is faster then odbc, it
is the fact that you are by-passing jet (that DOES make a big difference
in performance). So, while it most a myth that oleDB is faster then odbc,
a pass-though that by-passes jet (via oleDB, or jet odbc) is MUCH faster
then going though jet. Again, MUCH faster!
Hence, with ado, you wind up using ONE sql syntax, and a NOT mix/mash in
your reocrdset code when you hitting the server.
This simply makes (forces) your sql statements to be MORE database engine
neural. So, that is why ado is a BETTER choice then dao if you plan to be
working with different data engines.
>> So, ADO is a better abstraction and it is more data engine
>> neutral then DAO.
>
> I think that's pure bullshit. If you're using linked tables, then
> Jet is involved, and it's just bloody silly to use ADO to work with
> Jet, even if the back end is something other than Jet. If you're
> using ODBC, then you're not communicating directly with the back end
> tables.
Well, if you change your connection string from JET to sql server, you
ARE now communication directly with the server side..and you NOT have
changed your sql syntax. This will be far less work to use that
sql if you choose ado as your recordset model.
>> ADO does tend to
>> force you to be somewhat more data engine neural when you write
>> your sql statements.
>
> I think that is, once again, completely BS.
>
Well, I guess we see this differently. ADO is MORE vendor/data engine
natural then dao is. That is not conjecture, that is just the way it is.
ADO is NOT the data engine where as dao is pretty much the jet engine object
model.
ADO represents an abstraction on top of whatever you are using, be it
jet,oracle, MySql, sql server etc.
I don't see really any reason to use ADO for access applications, but there
are cases when server side stuff is involved.
When we had vb6 developers coming into access development, they often used
ado because that's what they had been using. However, now that ado is on it
way out, there is LESS reasons to choose ado (even in ms-access). However,
using ado in access still can make sense in some cases.
David W. Fenton
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
>
>>> You can use CurrentProject.Connection when you have linked
>>> tables to a jet backend to open a ado recordset.
>>
>> Why would you want to do so?
>
> Because one might have chosen to use ado in place dao in their
> application.
Why would you have done that?
> So, I am saying *if* one makes the choice to use ado,
...which I'm saying is a mistake in any app with linked tables...
> you do NOT have to
> build a connection string to the back end JET share
...which you'd never have to do if you used linked tables...
> nor do you have to use a
> jet odbc connection to the back end.
...which you'd never have to do with linked tables...
> There is a built-in connection object
> created for you (it likely pulls the connection string out of the
> linked table..but it not using odbc).
...which you'd never need if you're using linked tables.
> So, that means you can go in your code with ado:
>
> Dim rs As New ADODB.Recordset
>
> rs.Open ("select * from contacts"), CurrentProject.Connection
> Do While rs.EOF = False
> Debug.Print rs!FirstName
> rs.MoveNext
> Loop
> rs.Close
> Set rs = Nothing
Who cares? If you're using linked tables, you don't have to do it
that way at all. You can use DAO on the CurrentDB and be done with
it, never having to worry one bit about connection strings for your
back end.
This is the point of linked tables.
Not using them unnecessarily complicates an application without
offering any signficant benefit.
> Perhaps you are not clear or even aware that when using linked
> tables to a jet file share you do have a built in ADO connection
> object.
If you're using ADO. But why would you use ADO with linked tables?
> So, again my
> point was **IF** you going to use a ado recordsets, you don't have
> create or build your connection string to the jet back end.
But *why* would you use ADO if you're using linked tables?
> So, in the above I not setting up a odbc connection to that jet
> back end..am I???
I don't know and I really don't care. You're giving an academic
example of what ADO makes easier if you choose the wrong tools.
That's nice that ADO makes it really easy to get around the mistake
of choosing ADO as your interface, but, well, I'm questioning the
initial choice of ADO when you have linked tables -- DAO makes
*much* more sense.
And eliminates the problem you say ADO "solves."
>>> While there is some advantages when using ADO with sql server,
>>> oracle (at least ones that provide a oleDB connection) there
>>> also a good case to choose ADO if you plan to upgrade to sql
>>> server in the future.
>>
>> Why? If you're using ODBC, you gain exactly nothing.
>
> You gain sql neutral code. eg:
>
> dao:
> select * from tclCustomers where city like 'new*'
> The above is jet/dao syntax, but if you use an ado reocrdset, you
> can go:
>
> ado:
> select * from tclCustomers where city like 'new%'
Why does this matter? Every ODBC driver usable by Jet will translate
Jet's syntax into the relevant syntax for the back end in use. Why
would anyone *care* about having "neutral" SQL in your Access front
end?
> Both the above are hitting a jet back end. However, the 2nd syntax
> will ALSO work for mysql, oracle and sql server if I take that sql
> and want to run it server side.
Via ODBC linked tables, the first will also work with MySQL, Oracle
and SQL Server.
> And, if simply want to change the reocrset to pass-though, then
> again I do NOT have to change the sql syntax. (in fact, if I do
> setup a oleDB connection string, then I get 100% by-pass of jet,
I'm not sure this is correct, but can't prove that it's not.
But why anyone would want to throw away all the enormous capability
of Jet, I don't know. So far as I can tell, it's only ignorant
people with an irrational fear of an incredibly versatile database
engine that they don't understand who want to avoid Jet at all
costs.
> and I not have to
> change the sql one bit. If you code to dao, you WILL have to
> change your syntax to work with sql,mysql etc. if you want to
> avoid performance bottle necks.
And if you're using linked tables, none of this is important.
> Note that it is not that oleDB is faster then odbc, it
> is the fact that you are by-passing jet (that DOES make a big
> difference in performance). So, while it most a myth that oleDB is
> faster then odbc, a pass-though that by-passes jet (via oleDB, or
> jet odbc) is MUCH faster then going though jet. Again, MUCH
> faster!
I'd like to see some evidence for this claim. I don't believe it's
true. If it were, MS wouldn't be saying that MDB/ODBC is better than
ADP/OLEDB. Remember, they explain that the data shaping layer with
ADP/OLEDB causes significant bottleneck. And that layer is there
with ADO used from an MDB, too.
So, your claim of performance benefits seems to me to be at odds
with MS's own recommendations.
> Hence, with ado, you wind up using ONE sql syntax, and a NOT
> mix/mash in your reocrdset code when you hitting the server.
With linked tables you end up using one SQL syntax and it works
automatically with all back ends. You don't *need* to know what the
back end SQL dialect is.
> This simply makes (forces) your sql statements to be MORE database
> engine neural.
And this is good because...?
> So, that is why ado is a BETTER choice then dao if you plan to be
> working with different data engines.
I think this is all so much bullshit. The point of ODBC and linked
tables is that they make the specifics of the back end database
engine irrelevant.
>>> So, ADO is a better abstraction and it is more data engine
>>> neutral then DAO.
>>
>> I think that's pure bullshit. If you're using linked tables, then
>> Jet is involved, and it's just bloody silly to use ADO to work
>> with Jet, even if the back end is something other than Jet. If
>> you're using ODBC, then you're not communicating directly with
>> the back end tables.
>
> Well, if you change your connection string from JET to sql server,
> you ARE now communication directly with the server side..and you
> NOT have changed your sql syntax. This will be far less work to
> use that sql if you choose ado as your recordset model.
If you're using linked tables, you don't have to make any changes to
the SQL syntax when switching from Jet to SQL Server linked tables.
I don't know what you're nattering on about. Every example you
provide seems to ignore reality, claiming for ADO exactly the same
benefits you get with linked tables and DAO.
>>> ADO does tend to
>>> force you to be somewhat more data engine neural when you write
>>> your sql statements.
>>
>> I think that is, once again, completely BS.
>
> Well, I guess we see this differently. ADO is MORE vendor/data
> engine natural then dao is. That is not conjecture, that is just
> the way it is.
Give me an example of where you have to write SQL for linked tables
differently for different database engines?
> ADO is NOT the data engine where as dao is pretty much the jet
> engine object model.
And Jet can communicate via ODBC and various ISAMs with a whole host
of database engines. And it's remarkably smart in doing so, taking
away much of the complexity involved, and presenting all data
sources as similar.
> ADO represents an abstraction on top of whatever you are using, be
> it jet,oracle, MySql, sql server etc.
>
> I don't see really any reason to use ADO for access applications,
> but there are cases when server side stuff is involved.
And if you're using linked tables, there's no reason to use ADO.
Which is what I've been saying repeatedly.
> When we had vb6 developers coming into access development, they
> often used ado because that's what they had been using.
Yes, because they are ignorant of the benefits of linked tables in
Access, which they've never encountered before.
> However, now that ado is on it
> way out, there is LESS reasons to choose ado (even in ms-access).
> However, using ado in access still can make sense in some cases.
There is *no* reason to use ADO from Access if you're using linked
tables, except for the handful of things that can be done through
ADo that can't be done through DAO. For Jet data, there's about 4 of
these, for other db engines, I'm sure there are more. For non-Jet
dbs, passthrough queries obviate any need to resort to ADO, seems to
me.
You have not provided a single compelling argument for using ADO in
an Access application, except if you're not using linked tables.
Since linked tables are the key feature of Access that makes it easy
to use, ADO only makes sense if you're ignoring the best practices
for the application development tools you're using.
lyle fairfield
creative and powerful the ODBC engine can be in translating what I ask
for in JET to something the engine hosting the linked tables can
understand. But the ODBC engine is not as creative as a developer who
has learned the SQL language of that engine, and it offers no
incentive to the Access Developer to learn that SQL language. The
Access Developer who uses ODBC to link to SQL-Server tables may know
absolutely nothing of SQL-Server. Is it likely that he/she will create
applications that optimize the use of SQL-Server?
In my opinion, ADO and ADPS too, encourage Access Developers to learn
and use SQL-Server to its optimum level.
Why has MS abandoned ADPs? (BTW it has NOT abandoned ADO).
My guess is:
1. Huge and shocking security problems;
2. Documentation of development has been so bad that NO ONE knows how
an ADP actually works;
3. MS is squeezing the notion of Application Development out of
Access; Access's place is to be an interactive database for the casual
and mostly uneducated (in databases) user; ADPs impinge on the niche
MS has planned for its Dot Net fiasco; and so ADPs must die.
<rant>
(.Net is not COM; think about it folks; this is not like Americans and
Russians; it's like Americans and Martians. When people say ADO will
be supplanted by ADO.Net I mostly think they have NO idea what they're
talking about; it's like saying rifles will be supplanted by evil
thought ray machines; ADO.Net is not a superior rifle; it's not a
rifle at all!)
</rant>
<rant 2>
I have been experimenting with Linux recently. There is NOTHING I can
do with Windows/MS (costs) that I cannot do with Linux(free) except
fill up a hard drive PDQ.
</rant 2>
Albert D. Kallal
>>>
>>
>> You gain sql neutral code. eg:
>>
>> dao:
>> select * from tclCustomers where city like 'new*'
>> The above is jet/dao syntax, but if you use an ado reocrdset, you
>> can go:
>>
>> ado:
>> select * from tclCustomers where city like 'new%'
>
> Why does this matter? Every ODBC driver usable by Jet will translate
> Jet's syntax into the relevant syntax for the back end in use. Why
> would anyone *care* about having "neutral" SQL in your Access front
> end?
Because if you want a query to be pass-though then you DO NOT have to
change the syntax of your sql.
>
>> And, if simply want to change the reocrset to pass-though, then
>> again I do NOT have to change the sql syntax. (in fact, if I do
>> setup a oleDB connection string, then I get 100% by-pass of jet,
>
> I'm not sure this is correct, but can't prove that it's not.
>
It is the case.
> But why anyone would want to throw away all the enormous capability
> of Jet
We don't want to throw it out, we want our sql to not be tied to jet
for the case when we NEED a pass-though query.
>
>> Note that it is not that oleDB is faster then odbc, it
>> is the fact that you are by-passing jet (that DOES make a big
>> difference in performance). So, while it most a myth that oleDB is
>> faster then odbc, a pass-though that by-passes jet (via oleDB, or
>> jet odbc direct). These can be MUCH faster then going though jet.
>> Again, MUCH faster!
>
> I'd like to see some evidence for this claim. I don't believe it's
> true.
Read careful what I said:
>> a pass-though that by-passes jet (via oleDB, or
>> jet odbc) is MUCH faster then going though jet.
In other words, a jet (direct) odbc pass-though and a oleDB pass-though are
BOTH about the SAME in terms of performance.
So, that pass-though query (either oleDB, or JET direct) can be
SIGNIFICANTLY faster then going though jet linked tables. (got it now???).
If you use ado, then you can simply change where connection string points,
and you done. If you use dao, you *can* get the performance boast, but your
sql may have to be re-written, and you HAVE to change from a linked table to
a pass-though query (or use a odbc jet direct workspace + connection).
Try make a append (or make) query from MySql that based joins from two
linked tables to the local mdb file. Make the query pass-though (you
don't even need to use ado here). You will see anywhere from 20% to even 45%
better performance in some cases then that of operating on the linked
tables.
>If it were, MS wouldn't be saying that MDB/ODBC is better than
> ADP/OLEDB.
It is not better. However to get the same perforamnce boast in dao/jet it
will
often take additional work and worse you might even have to change your sql
syntax.
>
>> Hence, with ado, you wind up using ONE sql syntax, and a NOT
>> mix/mash in your reocrdset code when you hitting the server.
>
> With linked tables you end up using one SQL syntax and it works
> automatically with all back ends. You don't *need* to know what the
> back end SQL dialect is.
Right, but you pay a perforamnce penablity for some of that sql then. One
can aruge that the performance hit might be worth while trade off in your
case.
However, in some cases you REALLY do want to advoid jet messing things
up and by-pass jet...and with ado this will be easier for recordset
code.
Larry Linson
did not have to search out developers who knew specific back-end server
language dialects. Those were harder to locate and more expensive to
hire/contract; competent Access developers were in greater supply and were
less expensive. Those clients _instructed_ the prime contractors to avoid
passthrough queries unless there was a compelling need; the common
compelling need we found was a Stored Procedure to calculate the next key
for each given table. Those keys were not subject to being "lost" like
Autonumber or its server equivalent and could have been shown to the users
(though we did not do so... for business and business logic reasons, we used
them similarly to how Autonumber is used).
OLEdb seems obviously to be a dying technology... classic ADO may be around
in the Office world, and a few other software packages, but in "Microsoft's
real world of development, Dot Net", ADO has been superceded by ADO.NET
which is not OLEdb-based, and uses a different Object Model.
I agree, David, that the claimed "advantages" of ADO are most often simply
workarounds to avoid problems created by selecting ADO in circumstances
where it made no sense in the first place, except that someone thought
(wrongly) that it was to be the "wave of the future" in Microsoft
development. Instead, it was just another of Microsoft's attempts at
three-letter-acronym data handling techniques that lasted no, or little,
longer than any of the others.
Larry Linson
Microsoft Office Access MVP
"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns9B7EAC2D9756f9...@74.209.136.88...
lyle fairfield
news:Xmc5l.1238$BC4...@nwrddc02.gnilink.net:
> Those clients _instructed_
> the prime contractors to avoid passthrough queries unless there was a
> compelling need; the common compelling need we found was a Stored
> Procedure to calculate the next key for each given table. Those keys
> were not subject to being "lost" like Autonumber or its server
> equivalent and could have been shown to the users (though we did not
> do so... for business and business logic reasons, we used them
> similarly to how Autonumber is used).
Example?
--
lyle fairfield
David W. Fenton
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
>>>>
>>>> Why? If you're using ODBC, you gain exactly nothing.
>>>
>>> You gain sql neutral code. eg:
>>>
>>> dao:
>>> select * from tclCustomers where city like 'new*'
>>> The above is jet/dao syntax, but if you use an ado reocrdset,
>>> you can go:
>>>
>>> ado:
>>> select * from tclCustomers where city like 'new%'
>>
>> Why does this matter? Every ODBC driver usable by Jet will
>> translate Jet's syntax into the relevant syntax for the back end
>> in use. Why would anyone *care* about having "neutral" SQL in
>> your Access front end?
>
> Because if you want a query to be pass-though then you DO NOT have
> to change the syntax of your sql.
But you still have to execute it in code, instead of via the nice UI
of Access. Also, you can put Access in SQL 92 mode and, well, that
gets you what you want, right?
>>> And, if simply want to change the reocrset to pass-though, then
>>> again I do NOT have to change the sql syntax. (in fact, if I do
>>> setup a oleDB connection string, then I get 100% by-pass of jet,
>>
>> I'm not sure this is correct, but can't prove that it's not.
>
> It is the case.
But who the fuck cares if it bypasses Jet? Why would anyone be
worried about that who doesn't have an irrational fear of Jet?
>> But why anyone would want to throw away all the enormous
>> capability of Jet
>
> We don't want to throw it out, we want our sql to not be tied to
> jet for the case when we NEED a pass-though query.
And why does this matter? I'm failing to see the argument at all. If
you don't want to be tied to Access, then DON'T PROGRAM IN ACCESS.
>>> Note that it is not that oleDB is faster then odbc, it
>>> is the fact that you are by-passing jet (that DOES make a big
>>> difference in performance). So, while it most a myth that oleDB
>>> is faster then odbc, a pass-though that by-passes jet (via
>>> oleDB, or jet odbc direct). These can be MUCH faster then going
>>> though jet. Again, MUCH faster!
>>
>> I'd like to see some evidence for this claim. I don't believe
>> it's true.
>
> Read careful what I said:
>
>>> a pass-though that by-passes jet (via oleDB, or
>>> jet odbc) is MUCH faster then going though jet.
>
> In other words, a jet (direct) odbc pass-though and a oleDB
> pass-though are BOTH about the SAME in terms of performance.
>
> So, that pass-though query (either oleDB, or JET direct) can be
> SIGNIFICANTLY faster then going though jet linked tables. (got it
> now???).
And where's the evidence for the assertion?
> If you use ado, then you can simply change where connection string
> points, and you done. If you use dao, you *can* get the
> performance boast, but your sql may have to be re-written, and you
> HAVE to change from a linked table to a pass-though query (or use
> a odbc jet direct workspace + connection).
And where's the evidence for your assertion?
> Try make a append (or make) query from MySql that based joins from
> two linked tables to the local mdb file. Make the query
> pass-though (you don't even need to use ado here). You will see
> anywhere from 20% to even 45% better performance in some cases
> then that of operating on the linked tables.
You're now arguing that you need to do things in a specific way to
overcome the shortcomings of particular back-end database engines
(MySQL is notorious for join inefficiency). I thought you wanted to
write SQL that was db-engine neutral?
>>If it were, MS wouldn't be saying that MDB/ODBC is better than
>> ADP/OLEDB.
>
> It is not better.
MS says it is. Are you saying they are mistaken?
> However to get the same perforamnce boast in dao/jet it
> will
> often take additional work and worse you might even have to change
> your sql syntax.
And it's work you almost *never* need to do. Ever.
>>> Hence, with ado, you wind up using ONE sql syntax, and a NOT
>>> mix/mash in your reocrdset code when you hitting the server.
>>
>> With linked tables you end up using one SQL syntax and it works
>> automatically with all back ends. You don't *need* to know what
>> the back end SQL dialect is.
>
> Right, but you pay a perforamnce penablity
So you say, but have offered not actual evidence...
> for some of that sql then. One
> can aruge that the performance hit might be worth while trade off
> in your case.
>
> However, in some cases you REALLY do want to advoid jet messing
> things up and by-pass jet...and with ado this will be easier for
> recordset code.
A passthrough query is going to give you the ability to bypass Jet
when Jet makes the wrong optimization decisions. Why you think ADO
adds anything at all that is useful to that, I can't say.
Again, there is simply no need for ADO in an app using linked
tables.
Secondly, the goal of SQL neutrality is a form of what is called
"premature optimization" -- why put that as a goal before ease of
development, especially when an app based on ODBC linked tables
still offers the ability to bypass Jet when Jet is not as efficient
as other methods.
Classic ADO is a dead technology that no Access developer who is not
developing ADPs should spend even one minute with.
SmartbizAustralia
DAO is used to access the system tables for refreshing links...
Access 2007 seems to have gone back to that.
Some developers forget about the A in VBA (A standing for the
Application which in this case is Access) and write millions of sql
strings in code.
Try to use the queries and nice user interface even if in code you
change parameters...
Then you'll write very little dao or ado.....
But for sql server connections, ado gives you access to certain field
types and encourages a more sql way of doing things.
I like dao for simplicity but use ado for sql connections.
Always set sql compatibility so for wildcards you use % rather than
* ... Cannot stress this enough
And always reference the library - e,g, adodb.recordset for an ado
recordset and dao.recordset for a dao one...
Then you won't get issues with the application getting confused as to
which library to use!
Regards,
Tom Bizannes
Access and Sql Server Specialist
Sydney, Australia
lyle fairfield
wrote:
> Classic ADO is a dead technology that no Access developer who is not
> developing ADPs should spend even one minute with.
If ADO is a dead technology and MS has returned to ODBC links as its
preferred way of interfacing with Server-hosted data, then why, in
Access 2007, does this code
Dim myWorkSpace As DAO.Workspace
Set myWorkSpace = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
elicit this (error) response:
Microsoft Visual Basic
Run Time Error ‘3847’
"ODBCDirect is no longer supported.
Rewrite the code to use ADO instead of DAO."
?
aaron...@gmail.com
Where do you come up with this shit?
ADO works against multiple databases.
DAO _DOES_NOT_.
Fuck you and your DAO bullshit.
Stop spreading mis-information, you crybaby.
And for the record, User Level Security and Replication are not
available in Access 2007 (format).
-Aaron
MCITP: SQL Server 2005 DBA
On Dec 22, 6:57 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
> "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com> wrote innews:cXB3l.67200$JU5....@newsfe20.iad:
>
> > "David W. Fenton" <XXXuse...@dfenton.com.invalid> wrote in message
> > \
>
> >> Er, what? I thought ADO was a good idea only for SQL Server? If
> >> you want the convenience of linked tables, you're going to use
> >> ODBC, whatever the back end.
>
> > True the tables are linked by odbc, but you still have to
> > choose a data object model for your reocrdsets. So, a lot of
> > people when using linked tables STILL prefer ado because that's
> > what they are comfortable with.
>
> Accessdevelopers? I don't think so, unless they spend most of their
> really oughtn't be usingAccessat all. In other words, DAO makes
aaron...@gmail.com
On Dec 24, 1:55 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
> "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com> wrote innews:s3l4l.1975$H11....@newsfe09.iad:
>
> > "David W. Fenton" <XXXuse...@dfenton.com.invalid> wrote in message
> > avoidperformancebottle necks.
>
> And if you're using linked tables, none of this is important.
>
> > Note that it is not that oleDB is faster then odbc, it
> > is the fact that you are by-passing jet (that DOES make a big
> > difference inperformance). So, while it most a myth that oleDB is
> > faster then odbc, a pass-though that by-passes jet (via oleDB, or
> > jet odbc) is MUCH faster then going though jet. Again, MUCH
> > faster!
>
> I'd like to see some evidence for this claim. I don't believe it's
> true. If it were, MS wouldn't be saying that MDB/ODBC is better than
> ADP/OLEDB. Remember, they explain that the data shaping layer with
> ADP/OLEDB causes significant bottleneck. And that layer is there
> with ADO used from an MDB, too.
>
> So, your claim ofperformancebenefits seems to me to be at odds
> > When we had vb6 developers coming intoaccessdevelopment, they
> > often used ado because that's what they had been using.
>
> Yes, because they are ignorant of the benefits of linked tables inAccess, which they've never encountered before.
>
> > However, now that ado is on it
> > way out, there is LESS reasons to choose ado (even in ms-access).
> > However, using ado inaccessstill can make sense in some cases.
>
> There is *no* reason to use ADO fromAccessif you're using linked
> tables, except for the handful of things that can be done through
> ADo that can't be done through DAO. For Jet data, there's about 4 of
> these, for other db engines, I'm sure there are more. For non-Jet
> dbs, passthrough queries obviate any need to resort to ADO, seems to
> me.
>
> You have not provided a single compelling argument for using ADO in
> anAccessapplication, except if you're not using linked tables.
> Since linked tables are the key feature ofAccessthat makes it easy
> to use, ADO only makes sense if you're ignoring the best practices
> for the application development tools you're using.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/- Hide quoted text -
>
> - Show quoted text -
aaron...@gmail.com
SQL Server is more popular than Jet.
Stop living in a cave, old man.
-Aaron
On Dec 26, 1:52 pm, "Larry Linson" <boun...@localhost.not> wrote:
> One of the reasons my clients preferredAccess/ Jet / ODBC was that they
> did not have to search out developers who knew specific back-end server
> language dialects. Those were harder to locate and more expensive to
> hire/contract; competentAccessdevelopers were in greater supply and were
> less expensive. Those clients _instructed_ the prime contractors to avoid
> passthrough queries unless there was a compelling need; the common
> compelling need we found was a Stored Procedure to calculate the next key
> for each given table. Those keys were not subject to being "lost" like
> Autonumber or its server equivalent and could have been shown to the users
> (though we did not do so... for business and business logic reasons, we used
> them similarly to how Autonumber is used).
>
> OLEdb seems obviously to be a dying technology... classic ADO may be around
> in the Office world, and a few other software packages, but in "Microsoft's
> real world of development, Dot Net", ADO has been superceded by ADO.NET
> which is not OLEdb-based, and uses a different Object Model.
>
> I agree, David, that the claimed "advantages" of ADO are most often simply
> workarounds to avoid problems created by selecting ADO in circumstances
> where it made no sense in the first place, except that someone thought
> (wrongly) that it was to be the "wave of the future" inMicrosoft
> development. Instead, it was just another ofMicrosoft'sattempts at
> three-letter-acronym data handling techniques that lasted no, or little,
> longer than any of the others.
>
> Larry Linson
> MicrosoftOfficeAccessMVP
>
> "David W. Fenton" <XXXuse...@dfenton.com.invalid> wrote in messagenews:Xns9B7EAC2D9756f9...@74.209.136.88...
>
>
>
> > "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com> wrote in
> >news:s3l4l.1975$H11....@newsfe09.iad:
>
> >> "David W. Fenton" <XXXuse...@dfenton.com.invalid> wrote in message
> >> avoidperformancebottle necks.
>
> > And if you're using linked tables, none of this is important.
>
> >> Note that it is not that oleDB is faster then odbc, it
> >> is the fact that you are by-passing jet (that DOES make a big
> >> difference inperformance). So, while it most a myth that oleDB is
> >> faster then odbc, a pass-though that by-passes jet (via oleDB, or
> >> jet odbc) is MUCH faster then going though jet. Again, MUCH
> >> faster!
>
> > I'd like to see some evidence for this claim. I don't believe it's
> > true. If it were, MS wouldn't be saying that MDB/ODBC is better than
> > ADP/OLEDB. Remember, they explain that the data shaping layer with
> > ADP/OLEDB causes significant bottleneck. And that layer is there
> > with ADO used from an MDB, too.
>
> > So, your claim ofperformancebenefits seems to me to be at odds
> >> When we had vb6 developers coming intoaccessdevelopment, they
> >> often used ado because that's what they had been using.
>
> > Yes, because they are ignorant of the benefits of linked tables in
> >Access, which they've never encountered before.
>
> >> However, now that ado is on it
>
> ...
>
> read more »- Hide quoted text -
aaron...@gmail.com
thanks Lyle-- I applaud your passion
thanks Lyle-- I applaud your passion
On Dec 27, 6:12 am, lyle fairfield <lyle.fairfi...@gmail.com> wrote:
> On Dec 26, 10:22 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
> wrote:
>
> > Classic ADO is a dead technology that noAccessdeveloper who is not
> > developing ADPs should spend even one minute with.
>
> If ADO is a dead technology and MS has returned to ODBC links as its
> preferred way of interfacing with Server-hosted data, then why, inAccess2007, does this code
>
> Dim myWorkSpace As DAO.Workspace
> Set myWorkSpace = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
>
> elicit this (error) response:
>
> MicrosoftVisual Basic
David W. Fenton
DAO. Without question. ADO is dead and has no role in any Access
application using linked tables.
That isn't to say that you might not be occasionally forced to use
ADO (because of the stupid decisions MS made back in 1999 or so
about what things to put in ADO vs. DAO when they upgraded Jet to
Jet 4), but ADO should be something you use exceptionally, only when
forced to do so.
![Tony Toews [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjVFNFhAO8kxGtutFBSPYzx5Lbrrty7WRKPHNhjuWtTEHvsoQdk=s40-c)
Tony Toews [MVP]
>You couldn't have picked a more contentious subject.
Except natural vs surrogate keys. <smile>
tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Larry Linson
> If ADO is a dead technology
"Dead technology" can be interpreted in several ways. I certainly see that
in what Microsoft calls "the real world of development" (Dot Net) classic
ADO has been superceded by ADO.NET, which is based on different technology
and has a different object model.
> and MS has returned
> to ODBC links as its preferred way of interfacing
> with Server-hosted data, then why, in Access 2007,
> does this code
> Dim myWorkSpace As DAO.Workspace
> Set myWorkSpace = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
> elicit this (error) response:
> Microsoft Visual Basic
> Run Time Error ‘3847’
> "ODBCDirect is no longer supported.
> Rewrite the code to use ADO instead of DAO."?
Because changing the text of error messages was not the highest priority
item in the development of Access 2009?
Larry Linson
> SQL Server is more popular than Jet.
Your data, or your perception of the data, is in error, or you have been
taken in by "marketing material". Jet 4.0 has been included with the
Windows Operating System for a long time... so long that they no longer had
to include it in the MDAC post-version-2.5 (as you posted that you had
discovered). The same is not true for SQL Server.
Jet remains the most-used database engine in the world. ACE will, I suspect,
soon be giving it competition as new releases of the Windows Operating
System occur.
Larry Linson
> Example?
As surrogate keys, for joins; not for identifying particular items/records
to a user.
David W. Fenton
news:666e469d-c7eb-4c5c...@r34g2000vbp.googlegroups.co
m:
Because there is still code hanging around from the bad old "ADO
EVERYWHERE" days, just as a lot of online documentation still has
ADO-only examples.
It takes a long time to correct a major mistake like this. That's
one of the reasons they should have had more sense and avoided
making that mistake in the first place.
lyle fairfield
wrote:
> lyle fairfield <lyle.fairfi...@gmail.com> wrote innews:666e469d-c7eb-4c5c...@r34g2000vbp.googlegroups.co
What I reported isn't old; it's new. That is, DirectODBC was supported
until Access 2007. So this is not 'code hanging around from the bad
old "ADO
EVERYWHERE" days'.
Regardless, to make the !!!!!dynamic!!!!! [not a persistent link]
connection to the ODBC source, say SQL Server, (which is what, TTBOMK,
DirectODBC was used for) what should one use? Not ADO? (bad old code).
Not DirectODBC? (no longer supported). So what?
BTW I posted a kludge a few weeks ago.
lyle fairfield
> "lyle fairfield" <lyle.fairfi...@gmail.com> wrote
They couldn't change it because it didn't exist. They introduced it.
There was no such error message prior to access 2007 because
ODBCDirect was still supported. This is a new message.
If one will not use ADO as MS recommends what does one use in place of
ODBCDirect?
Rick Brandt
> If one will not use ADO as MS recommends what does one use in place of
> ODBCDirect?
Under what circumstances does one need to use ODBCDirect?
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
lyle fairfield
I have used ODBCDirect to provide a dynamic connection to a data
source. ODBCDirect is outlined at http://msdn.microsoft.com/en-us/library/aa199500(office.10).aspx.
But what it's used for is not the point of my post.
It used to work.
Now (2007) it is not-supported.
And the new message says, "Use ADO."
lyle fairfield
This function gets information from an sql server database. It's not
necessary to be linked to the sql server database. The connection is
entirely dynamic. The information could be anything from the sql
database, icluding data, of course.
The function could be used in an mdb which used JET Tables, or was
linked to Oracle. It works in Access 2000-2003. It does not work in
2007. ADO is offered as an alternative.
Public Function GetFullyQualifiedNames$(ByVal ServerName$, ByVal
DatabaseName$)
Dim DAOConnection As DAO.Connection
Dim DAOField As DAO.Field
Dim DAORecordset As DAO.Recordset
Dim DAOWorkSpace As DAO.Workspace
Set DAOWorkSpace = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
With DAOWorkSpace
Set DAOConnection = .OpenConnection("", _
dbDriverComplete, _
True, _
"ODBC;Driver={SQL Server};Server=" & ServerName _
& ";Database=" & DatabaseName)
With DAOConnection
Set DAORecordset = .OpenRecordset("SELECT SO.[Name] AS
TableName " _
& ", SS.[Name] AS SchemaName " _
& "FROM Sys.Objects AS SO " _
& "INNER JOIN Sys.Schemas AS SS ON SO.[Schema_ID] = SS.
[Schema_ID] " _
& "WHERE SO.[Type] = 'U' " _
& "ORDER BY SO.[Name]")
End With
End With
With DAORecordset
While Not .EOF
GetFullyQualifiedNames = GetFullyQualifiedNames _
& "[" & ServerName & "]." _
& "[" & DatabaseName & "]." _
& "[" & .Collect("SchemaName") & "]." _
& "[" & .Collect("TableName") & "]" _
& vbNewLine
.MoveNext
Wend
End With
End Function
Sub Test()
Debug.Print GetFullyQualifiedNames("FFDBA-Laptop\SonyLaptop",
"DB_51315")
End Sub
Rick Brandt
> On Dec 30, 9:48 am, Rick Brandt <rickbran...@hotmail.com> wrote:
>> On Tue, 30 Dec 2008 06:25:56 -0800, lyle fairfield wrote:
>> > If one will not use ADO as MS recommends what does one use in place
>> > of ODBCDirect?
>>
>> Under what circumstances does one need to use ODBCDirect?
>
> necessary to be linked to the sql server database. The connection is
> entirely dynamic. The information could be anything from the sql
> database, icluding data, of course.
I suppose that would be useful if I ever find myself wanting data from a
server that I am not linked to. Hasn't come up yet though. The only
circumstances similar to that is when I get data over the internet, but
in those cases I communicate with a web service middle tier as we don't
expose our data server ports directly to the internet. On a LAN I can't
see any circumstance where I wouldn't be linked or using a passthrough.