Help Converting DAO to ADO Calls in Access 2000

[David C. Barber]

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*

[Ian Hinson]

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*

[Steve Jorgensen]

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*

[David C. Barber]

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*

[David C. Barber]

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*

[Rick Brandt]

"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

[Steve Jorgensen]

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*
>
>
>

[David W. Fenton]

"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