By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Help with converting DAO to ADO

P: n/a
I'm looking at converting DAO to ADO in my app. All of my DAO connections
are of the following structure:

Dim wsName As DAO.Workspace
Dim dbName As DAO.Database
Dim rsName As DAO.Recordset

Set wsName = DBEngine.Workspaces(0)
Set dbName = wsName.OpenDatabase(CurrentProject.FullName)
Set rsName = dbName.OpenRecordset("SQL Statement")

I'm a real newbie with ADO and don't feel at all comfortable with it. Now to
convert to ADO, something like:

Dim cnnName As New ADODB.Connection
Dim rsName As New ADODB.Recordset

and here's where I start to get confused:

Set cnnName = Application.CurrentProject.Connection????
rsName.Open "SQL Statement" ????

Since I have several hundred of these DAO constructs, I'm trying to figure
out if there is a way I could write code to accomplish the conversion.

Thanks for any help.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200602/1
Feb 17 '06 #1
Share this Question
Share on Google+
32 Replies


P: n/a
Robert, can I ask the reason for converting to ADO?

DAO is the native Access library. The A in DAO *is* Access, and it is the
library Access itself uses in the interface, so why would you want to
change?

Microsoft was pushing ADO about 5 years ago, as a more generic library
(suitable for things wider than Access), but it is now dead, replaced by the
very different ADO.NET. There is therefore now no point in doing this just
to learn ADO, no point at all in doing it for an Access application, and no
point at all at all in doing it for an existing Access application.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"robert d via AccessMonster.com" <u6836@uwe> wrote in message
news:5bfe8a67536d4@uwe...
I'm looking at converting DAO to ADO in my app. All of my DAO connections
are of the following structure:

Dim wsName As DAO.Workspace
Dim dbName As DAO.Database
Dim rsName As DAO.Recordset

Set wsName = DBEngine.Workspaces(0)
Set dbName = wsName.OpenDatabase(CurrentProject.FullName)
Set rsName = dbName.OpenRecordset("SQL Statement")

I'm a real newbie with ADO and don't feel at all comfortable with it. Now
to
convert to ADO, something like:

Dim cnnName As New ADODB.Connection
Dim rsName As New ADODB.Recordset

and here's where I start to get confused:

Set cnnName = Application.CurrentProject.Connection????
rsName.Open "SQL Statement" ????

Since I have several hundred of these DAO constructs, I'm trying to figure
out if there is a way I could write code to accomplish the conversion.

Thanks for any help.

Feb 17 '06 #2

P: n/a
Microsoft says:
* ADO: ActiveX Data Objects (ADO) provides a high-level programming
model that will continue to be enhanced. Although a little less
performant than coding to OLE DB or ODBC directly, ADO is
straightforward to learn and use, and can be used from script languages
such as Microsoft Visual Basic® Scripting Edition (VBScript) or
Microsoft JScript®.

Feb 17 '06 #3

P: n/a
Allen:

Thank you for the information. I have another post about a prospective
client who is concerned that my application was developed on a "toy" database
because it is Access. They want me to link to a SQL SERVER backend. In my
other post I have asked forum members to help me justify Access, but let's
not continue that discussion here.

My DB is split so once we convert to SQL Server it won't be a problem. With
DAO I have linked to SQL Server on another machine in a test and the data
retrieval is faster than in linking to a Jet DB located on another machine.

So far so good.

But, I'm trying to anticipate what this prospective client might accept as
alternatives if I can't convince them that my front end is suitable.

I thought about telling them that I will convert it to VB6. But I'm not sure
how the data connections would work to SQL Server. With VB6 can I use DAO to
connect to SQL Server?

Also, what if I have them fairly convinced that my app will do the trick as
it currently is developed, only to have someone ask me if my data access is
via DAO or ADO. I think if I say DAO, it'll be counterproductive at that
point.

Hence, my question.

BTW: Since I've started decompiling my app, the stability has improved
markedly, although when I do have corruption it is now different involving
some weird error message that I can never resolve.

Thanks.

Allen Browne wrote:
Robert, can I ask the reason for converting to ADO?

DAO is the native Access library. The A in DAO *is* Access, and it is the
library Access itself uses in the interface, so why would you want to
change?

Microsoft was pushing ADO about 5 years ago, as a more generic library
(suitable for things wider than Access), but it is now dead, replaced by the
very different ADO.NET. There is therefore now no point in doing this just
to learn ADO, no point at all in doing it for an Access application, and no
point at all at all in doing it for an existing Access application.
I'm looking at converting DAO to ADO in my app. All of my DAO connections
are of the following structure:

[quoted text clipped - 23 lines]

Thanks for any help.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200602/1
Feb 17 '06 #4

P: n/a
I am the world's greatest fan of ADO. Prior to my becoming so I wrote
tons of DAO stuff. I can't think of any reasons other than money or a
gun to my head that would be sufficient for me to convert any of it.
(Well, maybe sex ..., or marinated artichoke hearts). What's yours?

If you want to see how to "do" ADO look in the help file, the object
browser or, top find things quickly ... download the Miscrosoft Data
Access SDK ... which has bunches of great stuff.

Feb 17 '06 #5

P: n/a
"Lyle Fairfield" <ly***********@aim.com> wrote

Do you think it serves any purpose to clutter the newsgroup with old quotes
that _appear to_ but, in fact, no longer _do_ contradict Allen Browne's
perfectly accurate comments about the "classic ADO" of which you are the
"world's greatest fan" versus ADO.NET and using DAO when the database engine
is Jet?

And, then, to post comments about the extremes to which someone would have
to go to persuade you to convert existing DAO to ADO, which apparently
contradict your contradiction?

I had really thought you were "here to help," Lyle, but perhaps I was
mistaken.
Microsoft says:
To be completely honest, Lyle, you should write "Microsoft said" (past
tense).
* ADO: ActiveX Data Objects (ADO) provides
a high-level programming model that will continue
to be enhanced. Although a little less performant
than coding to OLE DB or ODBC directly, ADO
is straightforward to learn and use, and can be used
from script languages such as Microsoft Visual Basic®
Scripting Edition (VBScript) or Microsoft JScript®.


Have not the "knowledgeable 'Softie insiders" blogged that the Access
development team has taken over what used to be called Jet and what used to
be called DAO, for additional development in the next version (apparently
now officially named Office 2007)?

Perhaps they are also enhancing "classic ADO" for Office 2007. Can you
provide a reference to a statement that they are?

Larry Linson
Microsoft Access MVP
Feb 17 '06 #6

P: n/a
"robert d via AccessMonster.com" <u6836@uwe> wrote
But, I'm trying to anticipate what this
prospective client might accept as
alternatives if I can't convince them
that my front end is suitable.
Perhaps it would be best to concentrate on convincing them that your front
end _IS_ suitable?
I thought about telling them that I will
convert it to VB6. But I'm not sure
how the data connections would work
to SQL Server. With VB6 can I use DAO
to connect to SQL Server?
If I didn't know you were grasping at straws just to try to win the
contract, I would ask "What do you expect to gain from converting to VB6?"
There'd be a good deal to be lost, and, likely, nothing to be gained from
such a conversion. It would certainly be the question I'd ask if a client
told me they wanted to convert an Access front end to VB6.

You can see a presentation that I did for my user group on the subject ot
Access versus classic VB as a front-end for database applications at
http://www.appdevissues.com/downloads.htm. It may provide some answers you
can use.

VB6 is now, AFAIK, "out of support". It was superceded by the first version
of VB.NET, and recently the third version of VB.NET was released (as part of
Visual Studio 2005).

Yes, you could use DAO with VB6 (I am reasonably certain that the DAO 3.6
library is there), but be aware that the VB community "drank the ADO
Kool-Aid" to a much greater extent than the Access community did, so you may
have to rely on VB5 examples.
Also, what if I have them fairly convinced that my
app will do the trick as it currently is developed,
only to have someone ask me if my data access is
via DAO or ADO. I think if I say DAO, it'll be
counterproductive at that point.


Allen has already given you the points to cover showing why DAO is the
appropriate choice. You could add that "knowledgeable Microsoft insiders"
including the Product Manager for ADP and ADO for a previous version of
Access now recommend MDB-DAO-Jet-ODBC-server database as being generally
preferrable to ADP-ADODB-server database.

Larry Linson
Microsoft Access MVP
Feb 17 '06 #7

P: n/a
Thank you, Larry.

This information along with my other posts will provide me with a solid basis
for strongly supporting Access as a development environment for a front-end,
which, oh by the way, has a native database known as Jet, but which will not
be used in this implementation (SQL Server) so there are no concerns about
scalability, etc.

Larry Linson wrote:
But, I'm trying to anticipate what this
prospective client might accept as
alternatives if I can't convince them
that my front end is suitable.


Perhaps it would be best to concentrate on convincing them that your front
end _IS_ suitable?
I thought about telling them that I will
convert it to VB6. But I'm not sure
how the data connections would work
to SQL Server. With VB6 can I use DAO
to connect to SQL Server?


If I didn't know you were grasping at straws just to try to win the
contract, I would ask "What do you expect to gain from converting to VB6?"
There'd be a good deal to be lost, and, likely, nothing to be gained from
such a conversion. It would certainly be the question I'd ask if a client
told me they wanted to convert an Access front end to VB6.

You can see a presentation that I did for my user group on the subject ot
Access versus classic VB as a front-end for database applications at
http://www.appdevissues.com/downloads.htm. It may provide some answers you
can use.

VB6 is now, AFAIK, "out of support". It was superceded by the first version
of VB.NET, and recently the third version of VB.NET was released (as part of
Visual Studio 2005).

Yes, you could use DAO with VB6 (I am reasonably certain that the DAO 3.6
library is there), but be aware that the VB community "drank the ADO
Kool-Aid" to a much greater extent than the Access community did, so you may
have to rely on VB5 examples.
Also, what if I have them fairly convinced that my
app will do the trick as it currently is developed,
only to have someone ask me if my data access is
via DAO or ADO. I think if I say DAO, it'll be
counterproductive at that point.


Allen has already given you the points to cover showing why DAO is the
appropriate choice. You could add that "knowledgeable Microsoft insiders"
including the Product Manager for ADP and ADO for a previous version of
Access now recommend MDB-DAO-Jet-ODBC-server database as being generally
preferrable to ADP-ADODB-server database.

Larry Linson
Microsoft Access MVP


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200602/1
Feb 17 '06 #8

P: n/a
Larry Linson wrote:
"Lyle Fairfield" <ly***********@aim.com> wrote

Do you think it serves any purpose to clutter the newsgroup with old quotes
that _appear to_ but, in fact, no longer _do_ contradict Allen Browne's
perfectly accurate comments about the "classic ADO" of which you are the
"world's greatest fan" versus ADO.NET and using DAO when the database engine
is Jet?

And, then, to post comments about the extremes to which someone would have
to go to persuade you to convert existing DAO to ADO, which apparently
contradict your contradiction?

I had really thought you were "here to help," Lyle, but perhaps I was
mistaken.
> Microsoft says:


To be completely honest, Lyle, you should write "Microsoft said" (past
tense).
> * ADO: ActiveX Data Objects (ADO) provides
> a high-level programming model that will continue
> to be enhanced. Although a little less performant
> than coding to OLE DB or ODBC directly, ADO
> is straightforward to learn and use, and can be used
> from script languages such as Microsoft Visual Basic®
> Scripting Edition (VBScript) or Microsoft JScript®.


Have not the "knowledgeable 'Softie insiders" blogged that the Access
development team has taken over what used to be called Jet and what used to
be called DAO, for additional development in the next version (apparently
now officially named Office 2007)?

Perhaps they are also enhancing "classic ADO" for Office 2007. Can you
provide a reference to a statement that they are?

Larry Linson
Microsoft Access MVP


He doesn't know which contradictions are being contradicted by his
contradicting :-). Actually, what Lyle wrote made perfect sense. I'm
a big fan of DAO because I haven't needed the features specific to ADO,
but I wouldn't convert an existing ADO application to DAO without a
very good reason. He does seem to be trying to help. Maybe ADO and
DAO will be rolled up into a single technology. MS doesn't really seem
to be mentioning either one much more than the other. I'm not done
viewing, but from what I've seen, the PDC 05 presenters seemed to
mention ADO a lot more often than they did DAO; but the Access rumors
seem to favor DAO. Maybe it's like speculating that GM and Ford will
merge when one of them goes bankrupt. It's possible, but it's anyone's
guess how probable. Although, it's fun to think about a Ford Camaro or
a GM Mustang :-). I think that neither ADODB nor DAO are going away
anytime soon. If that's the case, it will be back to "Microsoft
says:." I don't disagree with what Allen wrote, but I don't see MS
giving up on ADO functionality that easily. I suspect they're going to
try -- shudder -- to fix it and include it in spite of what we've been
led to believe. But I'm not privy to any inside information so I'm
just having fun speculating.

James A. Fortune
CD********@FortuneJames.com

that was Zen, this is DAO -- Tim Mills-Groninger

Feb 17 '06 #9

P: n/a
On 16 Feb 2006 20:01:43 -0800, Lyle Fairfield wrote:

or marinated artichoke hearts


What is artichoke?
--
K
Feb 17 '06 #10

P: n/a
"Kjell Harnesk" <no****@please.com> wrote in message
news:1w******************************@40tude.net.. .
On 16 Feb 2006 20:01:43 -0800, Lyle Fairfield wrote:

or marinated artichoke hearts


What is artichoke?

Like a potato without the flavor.

--
Darryl Kerkeslager
Feb 17 '06 #11

P: n/a

Larry Linson wrote:
"Lyle Fairfield" <ly***********@aim.com> wrote

Do you think it serves any purpose to clutter the newsgroup with old quotes
that _appear to_ but, in fact, no longer _do_ contradict Allen Browne's
perfectly accurate comments about the "classic ADO" of which you are the
"world's greatest fan" versus ADO.NET and using DAO when the database engine
is Jet?
http://msdn.microsoft.com/data/mdac/...dacroadmap.asp
To be completely honest, Lyle, you should write "Microsoft said" (past
tense).
http://msdn.microsoft.com/data/mdac/...dacroadmap.asp
Larry Linson
Microsoft Access MVP


Feb 17 '06 #12

P: n/a
The first link to the msdn article (revised in September of 2005) states
under a heading entitled: Obsolete Data Access Technologies.

Obsolete technologies are technologies that have not been enhanced or updated
in several product releases and that will be excluded from future product
releases. Do not use these technologies when you write new applications. When
you modify existing applications that are written using these technologies,
consider migrating those applications to ADO.NET.

Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®, and
scripting languages. It was included with Microsoft Office 2000 and Office XP.
DAO 3.6 is the final version of this technology. It will not be available on
the 64-bit Windows operating system.
Can I migrate to ADO.NET even if my application remains a desktop application.
?

Lyle Fairfield wrote:
Do you think it serves any purpose to clutter the newsgroup with old quotes
that _appear to_ but, in fact, no longer _do_ contradict Allen Browne's
perfectly accurate comments about the "classic ADO" of which you are the
"world's greatest fan" versus ADO.NET and using DAO when the database engine
is Jet?


http://msdn.microsoft.com/data/mdac/...dacroadmap.asp
To be completely honest, Lyle, you should write "Microsoft said" (past
tense).


http://msdn.microsoft.com/data/mdac/...dacroadmap.asp
Larry Linson
Microsoft Access MVP


--
Message posted via http://www.accessmonster.com
Feb 17 '06 #13

P: n/a
Larry Linson wrote:
Perhaps they are also enhancing "classic ADO" for Office 2007. Can you
provide a reference to a statement that they are?


ADO is a technology independent of Access and Office. I don't
understand this question at all.
Will ADO die? If MS is willing to have millions of ADP, ASP, VB, HTA,
etc applications cease to function I suppose it could.
Will new versions of Office be able to use ADO? Well, if they can't
they won't be backwards compatible with ADPs and many thousands of
other applications. What are the chances of that?

Feb 17 '06 #14

P: n/a
Larry Linson wrote:
I had really thought you were "here to help," Lyle, but perhaps I was
mistaken.


UPDATE [SELECT s.*, ns.* FROM Suppliers AS s INNER JOIN
Northwind.Mdb.Suppliers AS ns ON s.SupplierID=ns.SupplierID]. AS
SubQuery SET s.CompanyName = ns.CompanyName;

The syntax here must be exact.

If you are using JET 4.0 then you are laughing because you can
substitute ( ) for [ ] for the subquery and omit the "." and alias as
in:
UPDATE (SELECT s.*, ns.* FROM Suppliers AS s INNER JOIN
Northwind.Mdb.Suppliers AS ns ON s.SupplierID=ns.SupplierID) SET
s.CompanyName=ns.CompanyName

This would mean that you can use [] to delimit your external db,
required if the path has spaces. [] are not nestable as delimiters

Application.FollowHyperlink "C:\Documents and Settings\Lyle
Fairfield\My Documents\Word\Materialism.doc"

Split should be available in any version of Access >= 2000 (9).
If for some reason (I can't think of one) you need a UDF you could use
one of the many you could find with a Google search. Here's mine ...
not so extensively tested:

' change this if it does not meet your needs
Const SplitLimit As Long = 4096

Public Function SplitB(ByVal SplitString As String, _
Optional ByVal Delimiter As String = " ", _
Optional Element As Long) As Variant
Static aSplit() As Variant
Dim Position As Long
If Element = 0 Then ReDim aSplit(SplitLimit)
Position = InStr(SplitString, Delimiter)
If Position = 0 Then
aSplit(Element) = SplitString
ReDim Preserve aSplit(Element)
SplitB = aSplit
Else
aSplit(Element) = _
Trim(Left(SplitString, Position - 1))
SplitB = _
SplitB(Mid$(SplitString, Position + 1), Delimiter, Element + 1)
End If
End Function

I suggest a two step approach, eg:
With DBEngine(0)(0)
.Execute "DELETE * FROM Suppliers WHERE SupplierID IN (SELECT
SupplierID FROM Northwind.mdb.Suppliers)"
.Execute "INSERT INTO Suppliers SELECT * FROM
Northwind.mdb.Suppliers"
End With

This may seem inefficient, but TTBOMK it's what databases do for an
update anyway (that is mark for deletion and append).

Public Function ShowTime( _
ByVal hours As Long, _
ByVal minutes As Long, _
ByVal AmorPm As Long) As String
Dim d As Date
d = TimeSerial(hours + (AmorPm - 1) * 12, minutes, 0)
ShowTime = Format(d, "hhnn")
' or
' ShowTime = Format(d, "hh:nn ampm")
End Function

As a last resort I would use an independent connection eg
Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
..Open _
"PROVIDER=SQLOLEDB.1;" _
& "INTEGRATED SECURITY=SSPI;" _
& "INITIAL CATALOG=FFDBA_ESO_LOCAL;" _
& "DATA SOURCE=FFDBA\SQLEXPRESS"
..Execute "spShrinkDatabase"
End With

Private Sub SomeWouldCallAHack()
Dim z As Long
On Error Resume Next
With References
.AddFromFile "C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\ESO\EsoAdmin.adp"
.AddFromFile "C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6EXT.OLB"
With VBE.VBProjects("EsoAdmin").VBComponents
For z = 1 To .Count
Debug.Print .Item(z).Name
Next z
End With
.Remove References("VBIDE")
.Remove References("EsoAdmin")
End With
On Error GoTo 0
End Sub

Could you be talked into

On Error Resume Next
Kill "C:\Documents and Settings\t833299\Desktop\Extracts\*.*"
On Error Goto 0 'or somewher else

From: Peter Tyler - view profile
Date: Fri, Feb 10 2006 7:53 am
Email: "Peter Tyler" <PLTy...@gmail.com>
Groups: comp.databases.ms-access
Not yet rated
Rating:
show options

Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author

I am having a hard time trying to code around this. I have a checkbox
field in a MSAccess table called "100Fund" which is identical in
structure to other checkbox fields in the same table. I have a combo
box listing the various fields in the table for the user to select as a
table filter. If I use the following code in VB6 - where the user
selects the field "100Fund" - I get the above error:

Dim varchoice As String
varchoice = cmbFilter.Text
Adodc1.RecordSource = "select * from [A Table] where " & varchoice & "
= True order by LastName"
Adodc1.Refresh

All other checkbox fields work fine when selected. I appreciate that
it has to do with the field name starting with "100" but varchoice is
clearly a string variable. Using Cstr() doesn't help.

Any ideas? Thanks in advance!

Peter Tyler

Reply Rate this post: Text for clearing space

From: Lyle Fairfield - view profile
Date: Fri, Feb 10 2006 8:00 am
Email: "Lyle Fairfield" <lylefairfi...@aim.com>
Groups: comp.databases.ms-access
Not yet rated
Rating:
show options

Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author

You might try:
where [" & varchoice & "]

Reply Rate this post: Text for clearing space

From: Peter Tyler - view profile
Date: Sat, Feb 11 2006 1:23 am
Email: "Peter Tyler" <PLTy...@gmail.com>
Groups: comp.databases.ms-access
Not yet rated
Rating:
show options

Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author

It works well. Thanks Lyle!

But why? I would normally only use square brackets where the field (or
table) name has a space.

Peter

Reply Rate this post: Text for clearing space

From: Lyle Fairfield - view profile
Date: Sat, Feb 11 2006 6:02 am
Email: "Lyle Fairfield" <lylefairfi...@aim.com>
Groups: comp.databases.ms-access
Not yet rated
Rating:
show options

Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author

from help:

The setting or return value is a String that specifies a name. The name
must start with a letter. The maximum number of characters depends on
the type of object Name applies to, as shown in Remarks. It can include
numbers and underscore characters ( _ ) but can't include punctuation
or spaces.

Reply Rate this post: Text for clearing space

From: Peter Tyler - view profile
Date: Mon, Feb 13 2006 2:16 pm
Email: "Peter Tyler" <PLTy...@gmail.com>
Groups: comp.databases.ms-access
Not yet rated
Rating:
show options

Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author

OK thanks.

Peter

Feb 17 '06 #15

P: n/a
What Desktop Application? To learn more about what Access 12 will be
like one could read:
http://blogs.msdn.com/access/
VB6? My recollection is that installation of some .NET things
uninstalled it, but I might not be remembering correctly. (Some things
happen when you're 68; other things not enough).

How long will an Access or VB6 application using DAO or ADO run on the
Windows platform (as opposed to being able to be developed). Who knows?
If we were sticking with 32 bit then one could say ... ten years ...
twenty years. But we're going to 64 bit (I'm told). Will MS, for sure,
allow us to run DBase III apps from the 1980's in them ... as we can
now (haven't checked lately!)? History says, "Yes". But history could
be wrong.

Feb 17 '06 #16

P: n/a
"robert d via AccessMonster.com" <u6836@uwe> wrote in
news:5c00192105204@uwe:
This information along with my other posts will provide me with a
solid basis for strongly supporting Access as a development
environment for a front-end, which, oh by the way, has a native
database known as Jet, but which will not be used in this
implementation (SQL Server) so there are no concerns about
scalability, etc.


Well, you may not want to *tell* them, but Jet *will* be used if you
use ODBC, since Jet processes all ODBC data operations. This
occasionally causes performance problems, where Jet guesses wrong
about what the most efficient way to process the SQL is, but ADO
does the same thing and it guesses wrong, too (often in much worse
ways that are harder to work around).

That Jet is involved is not a bad thing.

The only thing that matters is that Jet is not your back end data
store.

And, BTW, Jet is involved in an MDB file no matter whether you use
DAO or ADO or ODBC or what, because the forms and reports and so
forth are stored in Jet data tables. The only way to avoid Jet
entirely is to use an ADP, and all of the vociferous defenders of
ADPs have now abandoned using them after encountering insurmountable
problems with them.

There was never any rational reason to want to avoid Jet in the
first place -- Microsoft created the ADP to address an irrational
fear of ignorant developers. Jet is a remarkable technology, both as
a data store and as a data access engine (it's Jet ISAMs that
provices Access with its ability to connect to myriad different
types of data). As a data store, it has its limitations and its very
often appropriate to upsize an app to a server back end in order to
support more users, in order to get five 9s of reliability, in order
to gain 24/7 up-time with hot backups, in order to improve
performance with applications that manipulate very large datasets,
or in order to handle extremely large datasets that are too large
for Jet's 2GB limitation.

But Jet is still very, very good.

Microsoft has bet on it big time by using it in all its copies of
Windows starting with Windows 2000 -- the data store for Active
Directory is Jet. So, Microsoft thinks Jet is a pretty good database
engine for certain kinds of applications.

Also, I think that Microsoft has an agenda that is is promoting to
try to move its customers to more expensive database projects. SQL
Server licenses are more expensive than Jet, which has no per user
licensing. Combine Access as front end with SQL Server as back end
(which is what most of the design engineering in Access 2000 was
aimed at) and Microsoft is getting revenue for the copy of Access
and for the user license for SQL Server. If that app uses Jet,
instead, Microsoft gets less revenue.

I think MS wishes Jet were not so good, to be honest, and that's
part of the reason they started deprecating it.

That campaign has worked prettye well, I guess, if your customers
have swallowed the anti-Jet Kool Aid. But the reputation of Jet is
entirely based on misinformation, ignorance and misuse of Jet by
people who never bothered to understand what it's limitations and
strengths actually were.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Feb 17 '06 #17

P: n/a
"Larry Linson" <bo*****@localhost.not> wrote in
news:UFcJf.78296$_D1.61030@trnddc03:
Have not the "knowledgeable 'Softie insiders" blogged that the
Access development team has taken over what used to be called Jet
and what used to be called DAO, for additional development in the
next version (apparently now officially named Office 2007)?


No. They've said that they are creating their own private
Access-specific version of Jet based on canonical Jet, which is
owned by the Jet team, a part of the Windows development
group.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Feb 17 '06 #18

P: n/a
"Lyle Fairfield" <ly***********@aim.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:
Microsoft says:
* ADO: ActiveX Data Objects (ADO) provides a high-level
programming
model that will continue to be enhanced. Although a little less
performant than coding to OLE DB or ODBC directly, ADO is
straightforward to learn and use, and can be used from script
languages such as Microsoft Visual Basic® Scripting Edition
(VBScript) or Microsoft JScript®.


This is a very odd quotation. Can you provide the URL for it?
Th reason I think it's very odd is that it restricts the use of ADO
to VBScript and JScript, which would, I think, actually support
Allen's point, that ADO is not all that appropriate for use in
Access. I note that plain VBA is not mentioned in the
quotation.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Feb 17 '06 #19

P: n/a
"Larry Linson" <bo*****@localhost.not> wrote in
news:UFcJf.78296$_D1.61030@trnddc03:
Do you think it serves any purpose to clutter the newsgroup with
old quotes that _appear to_ but, in fact, no longer _do_
contradict Allen Browne's perfectly accurate comments about the
"classic ADO" of which you are the "world's greatest fan" versus
ADO.NET and using DAO when the database engine is Jet?


I don't think the quotation contradicts Allen at all -- it specifies
the use of ADO in VBScript and JScript, but not in any other
environment, such as VBA or VB. That would exclude Access, no?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Feb 17 '06 #20

P: n/a
Jim,

In the development environment which _matters_ to Microsoft, that is DotNet,
they already _HAVE_ given up on classic ADO. Its successor is ADO.NET which
is based on a different object model.

Unless I am wrong, the PDC (Professional Developers Conference) almost
totally emphasizes DotNet and the ADO to which they refer so frequently is
ADO.NET -- not the "classic ADO" to which Lyle's quote refers.

Larry Linson
Microsoft Access MVP

<CD********@FortuneJames.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
Larry Linson wrote:
"Lyle Fairfield" <ly***********@aim.com> wrote

Do you think it serves any purpose to clutter the newsgroup with old
quotes
that _appear to_ but, in fact, no longer _do_ contradict Allen Browne's
perfectly accurate comments about the "classic ADO" of which you are the
"world's greatest fan" versus ADO.NET and using DAO when the database
engine
is Jet?

And, then, to post comments about the extremes to which someone would have
to go to persuade you to convert existing DAO to ADO, which apparently
contradict your contradiction?

I had really thought you were "here to help," Lyle, but perhaps I was
mistaken.
> Microsoft says:


To be completely honest, Lyle, you should write "Microsoft said" (past
tense).
> * ADO: ActiveX Data Objects (ADO) provides
> a high-level programming model that will continue
> to be enhanced. Although a little less performant
> than coding to OLE DB or ODBC directly, ADO
> is straightforward to learn and use, and can be used
> from script languages such as Microsoft Visual Basic®
> Scripting Edition (VBScript) or Microsoft JScript®.


Have not the "knowledgeable 'Softie insiders" blogged that the Access
development team has taken over what used to be called Jet and what used
to
be called DAO, for additional development in the next version (apparently
now officially named Office 2007)?

Perhaps they are also enhancing "classic ADO" for Office 2007. Can you
provide a reference to a statement that they are?

Larry Linson
Microsoft Access MVP


He doesn't know which contradictions are being contradicted by his
contradicting :-). Actually, what Lyle wrote made perfect sense. I'm
a big fan of DAO because I haven't needed the features specific to ADO,
but I wouldn't convert an existing ADO application to DAO without a
very good reason. He does seem to be trying to help. Maybe ADO and
DAO will be rolled up into a single technology. MS doesn't really seem
to be mentioning either one much more than the other. I'm not done
viewing, but from what I've seen, the PDC 05 presenters seemed to
mention ADO a lot more often than they did DAO; but the Access rumors
seem to favor DAO. Maybe it's like speculating that GM and Ford will
merge when one of them goes bankrupt. It's possible, but it's anyone's
guess how probable. Although, it's fun to think about a Ford Camaro or
a GM Mustang :-). I think that neither ADODB nor DAO are going away
anytime soon. If that's the case, it will be back to "Microsoft
says:." I don't disagree with what Allen wrote, but I don't see MS
giving up on ADO functionality that easily. I suspect they're going to
try -- shudder -- to fix it and include it in spite of what we've been
led to believe. But I'm not privy to any inside information so I'm
just having fun speculating.

James A. Fortune
CD********@FortuneJames.com

that was Zen, this is DAO -- Tim Mills-Groninger
Feb 18 '06 #21

P: n/a
ADO.NET is database access for the DotNet environment, and used with VB.NET,
C#, and other DotNet languages. You cannot (at least not without jumping
through flaming hoops, if you can at all) migrate an Access database to
ADO.NET, not through Access 2003. There are, as yet, unannounced features
and functions for the next version, and AFAICR ADO.NET has not been
announced.

Larry Linson
Microsoft Access MVP
"robert d via AccessMonster.com" <u6836@uwe> wrote in message
news:5c04c76df5092@uwe...
The first link to the msdn article (revised in September of 2005) states
under a heading entitled: Obsolete Data Access Technologies.

Obsolete technologies are technologies that have not been enhanced or
updated
in several product releases and that will be excluded from future product
releases. Do not use these technologies when you write new applications.
When
you modify existing applications that are written using these
technologies,
consider migrating those applications to ADO.NET.

Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®,
and
scripting languages. It was included with Microsoft Office 2000 and Office
XP.
DAO 3.6 is the final version of this technology. It will not be available
on
the 64-bit Windows operating system.
Can I migrate to ADO.NET even if my application remains a desktop
application.
?

Lyle Fairfield wrote:
Do you think it serves any purpose to clutter the newsgroup with old
quotes
that _appear to_ but, in fact, no longer _do_ contradict Allen Browne's
perfectly accurate comments about the "classic ADO" of which you are the
"world's greatest fan" versus ADO.NET and using DAO when the database
engine
is Jet?


http://msdn.microsoft.com/data/mdac/...dacroadmap.asp
To be completely honest, Lyle, you should write "Microsoft said" (past
tense).


http://msdn.microsoft.com/data/mdac/...dacroadmap.asp
Larry Linson
Microsoft Access MVP


--
Message posted via http://www.accessmonster.com

Feb 18 '06 #22

P: n/a
Robert,

If you use an MDB with ODBC to connect to SQL Server (or other
ODBC-compliant database), you _will_ be using Jet behind the scenes... all
the Queries are Jet Queries, modified by Jet/ODBC except as noted later and
the data will pass through Jet <---> ODBC <---> server. You can use
Passthrough Queries which Jet "leaves alone", but they are typically not
updateable. But, no there will be no scalability problems from using Jet.

You would not be using Jet, even behind the scenes, if you created an Access
project, ADP, but even my ADO-loving friend Lyle no longer does ADPs. I have
worked with ADP, but found nothing that appeared to me to make them better
than MDB and a few things, including classic ADO, that required climbing a
learning curve.

Larry Linson
Microsoft Access MVP
"robert d via AccessMonster.com" <u6836@uwe> wrote in message
news:5c00192105204@uwe...
Thank you, Larry.

This information along with my other posts will provide me with a solid
basis
for strongly supporting Access as a development environment for a
front-end,
which, oh by the way, has a native database known as Jet, but which will
not
be used in this implementation (SQL Server) so there are no concerns about
scalability, etc.

Larry Linson wrote:
> But, I'm trying to anticipate what this
> prospective client might accept as
> alternatives if I can't convince them
> that my front end is suitable.


Perhaps it would be best to concentrate on convincing them that your front
end _IS_ suitable?
> I thought about telling them that I will
> convert it to VB6. But I'm not sure
> how the data connections would work
> to SQL Server. With VB6 can I use DAO
> to connect to SQL Server?


If I didn't know you were grasping at straws just to try to win the
contract, I would ask "What do you expect to gain from converting to VB6?"
There'd be a good deal to be lost, and, likely, nothing to be gained from
such a conversion. It would certainly be the question I'd ask if a client
told me they wanted to convert an Access front end to VB6.

You can see a presentation that I did for my user group on the subject ot
Access versus classic VB as a front-end for database applications at
http://www.appdevissues.com/downloads.htm. It may provide some answers you
can use.

VB6 is now, AFAIK, "out of support". It was superceded by the first
version
of VB.NET, and recently the third version of VB.NET was released (as part
of
Visual Studio 2005).

Yes, you could use DAO with VB6 (I am reasonably certain that the DAO 3.6
library is there), but be aware that the VB community "drank the ADO
Kool-Aid" to a much greater extent than the Access community did, so you
may
have to rely on VB5 examples.
> Also, what if I have them fairly convinced that my
> app will do the trick as it currently is developed,
> only to have someone ask me if my data access is
> via DAO or ADO. I think if I say DAO, it'll be
> counterproductive at that point.


Allen has already given you the points to cover showing why DAO is the
appropriate choice. You could add that "knowledgeable Microsoft insiders"
including the Product Manager for ADP and ADO for a previous version of
Access now recommend MDB-DAO-Jet-ODBC-server database as being generally
preferrable to ADP-ADODB-server database.

Larry Linson
Microsoft Access MVP


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200602/1

Feb 18 '06 #23

P: n/a
Larry Linson wrote:
You would not be using Jet, even behind the scenes, if you created an Access
project, ADP


MichKa: ... But in the ADP case -- if you use wizards, then Jet
gets loaded. If you import or export anything via the wizard, then Jet
gets loaded. If you use any of the DoCmd.Transfer* methods, then Jet
gets loaded. And if you use any form or report, then Jet gets loaded
(for the Jet expression service)....

Feb 18 '06 #24

P: n/a

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Larry Linson wrote:
You would not be using Jet, even behind the scenes, if you created an
Access
project, ADP


MichKa: ... But in the ADP case -- if you use wizards, then Jet
gets loaded. If you import or export anything via the wizard, then Jet
gets loaded. If you use any of the DoCmd.Transfer* methods, then Jet
gets loaded. And if you use any form or report, then Jet gets loaded
(for the Jet expression service)....


I stand corrected -- Jet will be used, but it will not be a primary factor
in actually accessing the MS SQL Server data as it would be in the MDB/ODBC.

In the MDB/Jet/ODBC/serverDB environment, all database I/O goes through Jet
(though it doesn't change Queries designated as Passthrough Queries).

Larry Linson
Microsoft Access MVP
Feb 18 '06 #25

P: n/a
HK
As a newcomer to Access and to the whole database development scene, I
am enjoying this thread immensely. After starting to learn Access,
wanting to connect to a Foxpro app, and having had problems doing so, I
became more interested in the underlying technologies that are used to
retrieve and pass data from point A to point B. It is not that I am so
genuinely intrigued, but rather that ADO was recommended to me as a way
to reach my goal effectively.

In researching it, I found that DAO vs ADO, .mdb vs ADP, etc.
discussions are being continued everywhere I turn. It seems that there
is such a massive confusion and large misunderstanding about these
technologies and their future. And discussions are endless because of
it.

I am truly in the dark about DAO, and in all the posts I've read, this
is the first one that refers to it as the native technology that is
being used on a standard form created by the wizard, or created in
design view, for that matter. I read posts that tell you how to use
DAO and they always start out telling you how to define your database
and your recordset, and I'm sitting here thinking, "I'm connected to
the database and I'm viewing records. What did I do right? I never
defined my database or my recordset." I think I'm beginning to
understand that capabilites can be extended by coding your DAO calls
but where was this explanation when I needed it?

I wish that the Microsoft community, along with those in the upper hand
on database technologies, would team up and put out a white paper that
is an authoritative discussion on the matter. It would be nice to see
DAO, ADO, and ADO.NET discussed at length; their future, their
strengths, and the platforms or technologies that use/support them.

It seems that to really understand the matter, one has to get into the
depths of the database engines, as has been discussed here. A call for
data from Access to another database always goes through several layers
and a simple DAO vs. ADO discussion cannot be comprehensive until it
includes a Jet/ODBC or mdb/ADP/SQL Server/MSDE discussion/explanation.

I find this somewhat frustrating. I started out wanting to interact
with a Foxpro .dbc/.dbf data container and I ended up discussing Jet,
ODBC, DAO, and ADO just to learn how to do this. You can't say much
about connecting to other databases until someone says ADO.

I'm happy to say that somehow out of luck and desperation I have my
forms interacting with my Foxpro app and never had to learn a single
line of DAO or ADO, praise the Lord. To seasoned Access developers it
would have been easy but when everyone screams ADO it looks daunting.
Especially when view threads as this one.

My post has rambled long enough. If I meant to say anything at all, I
wanted to push my opinion that this confusion on DAO vs. ADO is no less
than frustrating. And the communities who work with standardization
are not helping us out.

Feb 18 '06 #26

P: n/a
"Lyle Fairfield" <ly***********@aim.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:
Larry Linson wrote:
You would not be using Jet, even behind the scenes, if you
created an Access project, ADP


MichKa: ... But in the ADP case -- if you use wizards, then Jet
gets loaded. If you import or export anything via the wizard, then
Jet gets loaded. If you use any of the DoCmd.Transfer* methods,
then Jet gets loaded. And if you use any form or report, then Jet
gets loaded (for the Jet expression service)....


Lyle, do you have a cite for that quotation?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Feb 18 '06 #27

P: n/a
"HK" <Ha**********@gmail.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
In researching it, I found that DAO vs ADO, .mdb vs ADP, etc.
discussions are being continued everywhere I turn. It seems that
there is such a massive confusion and large misunderstanding about
these technologies and their future.


I don't think there's any confusion at all among those who actually
understand the issues involved.

1. In Access, when using Jet as your data store, DAO is the best
technology.

2. In an Access MDB, when using SQL Server as your data store, DAO
via ODBC performs quite well, though there are some things that ADO
provides that may be very useful in that context.

3. An ADP elminates the Jet db engine from data communications with
SQL Server, and uses ADO otherwise, but has myriad problems,
inconstencies and bugs, some of them specific to ADPs, some of them
having to do with the engineering of ADO (e.g., ADO guesses about
how things should be processed behind the scenes, and sometimes
guesses wrong; strangely enough, this was one of the criticisms of
DAO/Jet that justified the invention of the Jet-less ADP).

4. The only versino of ADO that Access supports is just as obsolete
as DAO, because all current development of MS's flagship db
abstraction layer is now going into ADO.NET, which has little in
common with classic ADO beyond the name (what it does have in common
is there because they are two technologies for doing the same
things; ADO and DAO have a lot in common, too, i.e., there are a lot
of things that are done almost exactly the same way, or in ways that
are quite similar).

Aside from the abandonment of classic ADO and the problems that
eventually appearedn in ADPs in releases of Access subsequent to
2000, all of these things were pretty clear on the front end. It was
very clear from the introcuction of ADO as native data access layer
in Access 2000 that there was absolutely no logical reason to use
anything but DAO when connecting to Jet data.

Any confusion that resulted was entirely Microsoft's fault, and MS
has backtracked in many respects on this issue.

Some of us recognized the DAO vs. ADO situation for what it was (a
Microsoft marketing effort) and never learned ADO at all. Those of
us who did have been pretty well-served by that decision.

But you didn't have to be a rocket scientist to figure it out -- you
just had to have a basic understanding of the tools you were using.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Feb 18 '06 #28

P: n/a
"HK" <Ha**********@gmail.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
My post has rambled long enough. If I meant to say anything at
all, I wanted to push my opinion that this confusion on DAO vs.
ADO is no less than frustrating. And the communities who work
with standardization are not helping us out.


This problem was created by Microsoft. Blame them, not the
communities that trusted Microsoft's long-term plans.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Feb 18 '06 #29

P: n/a
Larry Linson wrote:
Jim,

In the development environment which _matters_ to Microsoft, that is DotNet,
they already _HAVE_ given up on classic ADO. Its successor is ADO.NET which
is based on a different object model.

Unless I am wrong, the PDC (Professional Developers Conference) almost
totally emphasizes DotNet and the ADO to which they refer so frequently is
ADO.NET -- not the "classic ADO" to which Lyle's quote refers.

Larry Linson
Microsoft Access MVP


Thanks for the information Larry. Having used classic ADO but not
ADO.NET I was unaware that the object model had changed. Do you know
of a reference that explains the differences between classic ADO and
ADO.NET with emphasis on the "why?" I'll keep that in mind as I
continue to download and view the presentations.

James A. Fortune
CD********@FortuneJames.com

Feb 19 '06 #31

P: n/a
No, I haven't kept any references / links, Jim. I have not ventured into
actually _doing_ DotNet, just kept up with the environment as it develops.
Visual Studio 2005 is still strongly enterprise-oriented, but with some
improvements for (or concessions to?) the "little guys". I have more hopes,
from what I've seen published about the goals for the next version,
codenamed "Orcas".

The only times I use ADO are when I am working on someone else's database.
But, I have seen enough of both to know that there's some apparently
similarity, and some real differences.

Larry Linson
Microsoft Access MVP
<CD********@FortuneJames.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Larry Linson wrote:
Jim,

In the development environment which _matters_ to Microsoft, that is
DotNet,
they already _HAVE_ given up on classic ADO. Its successor is ADO.NET
which
is based on a different object model.

Unless I am wrong, the PDC (Professional Developers Conference) almost
totally emphasizes DotNet and the ADO to which they refer so frequently
is
ADO.NET -- not the "classic ADO" to which Lyle's quote refers.

Larry Linson
Microsoft Access MVP


Thanks for the information Larry. Having used classic ADO but not
ADO.NET I was unaware that the object model had changed. Do you know
of a reference that explains the differences between classic ADO and
ADO.NET with emphasis on the "why?" I'll keep that in mind as I
continue to download and view the presentations.

James A. Fortune
CD********@FortuneJames.com

Feb 19 '06 #32

P: n/a
CD********@FortuneJames.com wrote:
Do you know
of a reference that explains the differences between classic ADO and
ADO.NET with emphasis on the "why?" I'll keep that in mind as I
continue to download and view the presentations.


Lyle's reference to MSDN in another thread combined with the hints in
PDC 05 will provide me with enough information to get the "why?" Being
based on XML is a motivation I understand already. I'll try to compare
ADO.NET's DataSet with C#'s IEnumerable to see if the data structure is
moving toward a unified one. It would be interesting to use language
integrated SQL along with WinFS to get the list of files in a directory
directly. It helps that MS doesn't do much randomly. I've got a lot
of thinking to do once I finish wading through all the PDC 05
information.

James A. Fortune
CD********@FortuneJames.com

Most relational databases are really one-dimensional in a certain
sense. Most schemas use foreign keys to impose a set/subset relation
on data. That relation enforces a hierarchy or tree and constitutes
only one abstract facet of knowledge.

Feb 19 '06 #33

This discussion thread is closed

Replies have been disabled for this discussion.