Help with converting DAO to ADO

[robert d via AccessMonster.com]

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.Worksp aces(0)
Set dbName = wsName.OpenData base(CurrentPro ject.FullName)
Set rsName = dbName.OpenReco rdset("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.Connectio n
Dim rsName As New ADODB.Recordset

and here's where I start to get confused:

Set cnnName = Application.Cur rentProject.Con nection????
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.c om
http://www.accessmonster.com/Uwe/For...ccess/200602/1

[Allen Browne]

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.c om" <u6836@uwe> wrote in message
news:5bfe8a6753 6d4@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.Worksp aces(0)
Set dbName = wsName.OpenData base(CurrentPro ject.FullName)
Set rsName = dbName.OpenReco rdset("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.Connectio n
Dim rsName As New ADODB.Recordset

and here's where I start to get confused:

Set cnnName = Application.Cur rentProject.Con nection????
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.

[Lyle Fairfield]

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®.

[robert d via AccessMonster.com]

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 counterproducti ve 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.c om
http://www.accessmonster.com/Uwe/For...ccess/200602/1

[Lyle Fairfield]

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.

[Larry Linson]

"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 "knowledgea ble '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

[Larry Linson]

"robert d via AccessMonster.c om" <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
counterproducti ve at that point.

Allen has already given you the points to cover showing why DAO is the
appropriate choice. You could add that "knowledgea ble 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

[robert d via AccessMonster.com]

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
counterproducti ve at that point.

Allen has already given you the points to cover showing why DAO is the
appropriate choice. You could add that "knowledgea ble 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.c om
http://www.accessmonster.com/Uwe/For...ccess/200602/1

[CDMAPoster]

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 "knowledgea ble '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********@Fort uneJames.com

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

[Kjell Harnesk]

On 16 Feb 2006 20:01:43 -0800, Lyle Fairfield wrote:

or marinated artichoke hearts

What is artichoke?
--
K