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

Help Converting DAO to ADO Calls in Access 2000

P: n/a
I've upsized an Access 2000 MDB to an ADP and changed the tables from a
linked Access Backend to SQL Server. Now I'm told I can't use DAO any more
and need to convert to ADO. While I've used ADO in VB6, there I created my
connection object and had easy access to it. All I want to know is how do I
convert:

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = currentdb
Set rs = db.Open ...

To equivalent ADO code. Is there a built-in connection object I can use?

Thanks!

*David*


Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
The nearest ADO equivalent to the code you wrote is:
Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim lngAffected as Long

Set cnn = CurrentProject.Connection
Set rs = cnn.Execute(strSQL, lngAffected, adCmdText)
'To open a table without SQL use
Set rs = cnn.Execute(strTablename, lngAffected, adCmdTableDirect)

But recordsets created in that way are read-only.
To create an updatable recordset use:
Set rst = New ADODB.Recordset
rst.Open "MyTable", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
or
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

HTH,
Ian.

"David C. Barber" <da***@NOSPAMdbarber.com> wrote in message
news:Fo********************@comcast.com...
I've upsized an Access 2000 MDB to an ADP and changed the tables from a
linked Access Backend to SQL Server. Now I'm told I can't use DAO any
more
and need to convert to ADO. While I've used ADO in VB6, there I created
my
connection object and had easy access to it. All I want to know is how do
I
convert:

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = currentdb
Set rs = db.Open ...

To equivalent ADO code. Is there a built-in connection object I can use?

Thanks!

*David*

Nov 13 '05 #2

P: n/a
In my experience, you will probably live to regret the decision to convert the
app to an ADP. It's usually a much better to upsize by just migrating the
tables to SQL Server and create links in the front-end MDB.

On Sat, 27 Nov 2004 00:11:21 -0700, "David C. Barber"
<da***@NOSPAMdbarber.com> wrote:
I've upsized an Access 2000 MDB to an ADP and changed the tables from a
linked Access Backend to SQL Server. Now I'm told I can't use DAO any more
and need to convert to ADO. While I've used ADO in VB6, there I created my
connection object and had easy access to it. All I want to know is how do I
convert:

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = currentdb
Set rs = db.Open ...

To equivalent ADO code. Is there a built-in connection object I can use?

Thanks!

*David*


Nov 13 '05 #3

P: n/a
In my perhaps limited understanding, I thought you couldn't link to SQL
Server from an .MDB, due in part to the fact that .MDB internally uses DAO
calls, which are incompatible with SQL Server 2000.

What problems are .ADP projects giving you that you don't get from .MDB
files?

*David*

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:j7********************************@4ax.com...
In my experience, you will probably live to regret the decision to convert the app to an ADP. It's usually a much better to upsize by just migrating the
tables to SQL Server and create links in the front-end MDB.

On Sat, 27 Nov 2004 00:11:21 -0700, "David C. Barber"
<da***@NOSPAMdbarber.com> wrote:
I've upsized an Access 2000 MDB to an ADP and changed the tables from a
linked Access Backend to SQL Server. Now I'm told I can't use DAO any moreand need to convert to ADO. While I've used ADO in VB6, there I created myconnection object and had easy access to it. All I want to know is how do Iconvert:

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = currentdb
Set rs = db.Open ...

To equivalent ADO code. Is there a built-in connection object I can use?

Thanks!

*David*

Nov 13 '05 #4

P: n/a
Thank for the help.

Could I get away with saying:

Dim rs as New ADODB.Recordset
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, , adCmdText

That is more in line with the current programming style. And since I have
about 110 such open statements to convert, simpler is better.

*David*

"Ian Hinson" <pp******@bigpond.net.au> wrote in message
news:JY******************@news-server.bigpond.net.au...
The nearest ADO equivalent to the code you wrote is:
Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim lngAffected as Long

Set cnn = CurrentProject.Connection
Set rs = cnn.Execute(strSQL, lngAffected, adCmdText)
'To open a table without SQL use
Set rs = cnn.Execute(strTablename, lngAffected, adCmdTableDirect)

But recordsets created in that way are read-only.
To create an updatable recordset use:
Set rst = New ADODB.Recordset
rst.Open "MyTable", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
or
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

HTH,
Ian.

"David C. Barber" <da***@NOSPAMdbarber.com> wrote in message
news:Fo********************@comcast.com...
I've upsized an Access 2000 MDB to an ADP and changed the tables from a
linked Access Backend to SQL Server. Now I'm told I can't use DAO any
more
and need to convert to ADO. While I've used ADO in VB6, there I created
my
connection object and had easy access to it. All I want to know is how do I
convert:

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = currentdb
Set rs = db.Open ...

To equivalent ADO code. Is there a built-in connection object I can use?
Thanks!

*David*


Nov 13 '05 #5

P: n/a
"David C. Barber" <da***@NOSPAMdbarber.com> wrote in message
news:Er********************@comcast.com...
In my perhaps limited understanding, I thought you couldn't link to SQL
Server from an .MDB, due in part to the fact that .MDB internally uses DAO
calls, which are incompatible with SQL Server 2000.


mmm, no. You can link to anything that provides an ODBC driver which SQL Server
2000 still has.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #6

P: n/a
Actually, Access was a pretty useful front-end to MS SQL Server long before
there was such a thing as an ADP. ADPs have never become widely used, and
they are still far from a mature technology, IMO.

Since ADPs have not caught on widely, and since MS is focusing all their
effort on the upcoming all-.NET based office suite, chances are that ADPs will
never mature, and ADP-based code will become pretty much unsupported. This
conclusion is supported by the fact that no new ADP features or fixes were
announced with the release of Access 2003, and some fixes have been very much
needed.

On Sat, 27 Nov 2004 18:19:05 -0700, "David C. Barber"
<da***@NOSPAMdbarber.com> wrote:
In my perhaps limited understanding, I thought you couldn't link to SQL
Server from an .MDB, due in part to the fact that .MDB internally uses DAO
calls, which are incompatible with SQL Server 2000.

What problems are .ADP projects giving you that you don't get from .MDB
files?

*David*

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:j7********************************@4ax.com.. .
In my experience, you will probably live to regret the decision to convert

the
app to an ADP. It's usually a much better to upsize by just migrating the
tables to SQL Server and create links in the front-end MDB.

On Sat, 27 Nov 2004 00:11:21 -0700, "David C. Barber"
<da***@NOSPAMdbarber.com> wrote:
>I've upsized an Access 2000 MDB to an ADP and changed the tables from a
>linked Access Backend to SQL Server. Now I'm told I can't use DAO anymore >and need to convert to ADO. While I've used ADO in VB6, there I createdmy >connection object and had easy access to it. All I want to know is howdo I >convert:
>
> Dim db as DAO.Database
> Dim rs as DAO.Recordset
>
> Set db = currentdb
> Set rs = db.Open ...
>
>To equivalent ADO code. Is there a built-in connection object I can use?
>
>Thanks!
>
> *David*
>
>
>


Nov 13 '05 #7

P: n/a
"David C. Barber" <da***@NOSPAMdbarber.com> wrote in
news:Er********************@comcast.com:
In my perhaps limited understanding, I thought you couldn't link
to SQL Server from an .MDB, due in part to the fact that .MDB
internally uses DAO calls, which are incompatible with SQL Server
2000.


Where did you get that idiotic idea?

DAO has been used for years for getting SQL Server data. There's
absolutely nothing incompatible about it. Indeed, the mere
allegation of incompatibility suggests that you don't even really
understand what DAO is.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.