473,224 Members | 1,434 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

Help Converting DAO to ADO Calls in Access 2000

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
7 4493
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
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
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
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
"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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: wiredog | last post by:
I am struggling rewriting my query from MS Access' IIF, Then to SQL Servers TSQL language. I am hoping some one can give me some guidance. I believe I have the first portion of the query correct...
8
by: baustin75 | last post by:
Posted: Mon Oct 03, 2005 1:41 pm Post subject: cannot mail() in ie only when debugging in php designer 2005 -------------------------------------------------------------------------------- ...
1
by: sparks | last post by:
I have done a LOT of databases in access 97 but I am new to access 2000. We are S L O W L Y converting over. Yesterday I had a 97 database that I had to run the converter to 2000 and everything...
2
by: Penn | last post by:
Hi, Has anyone converted from 2.0? Let me know how it went - and if there is a good reference for converting the code in addition to the database. - SH
2
by: Themis Papassilekas | last post by:
So, I don't know how tricky this is, but thought I'd ask... Here in the office there's an inventory db that was written in Access 2.0. Nobody cared to upgrade it since its creation back in an era...
3
by: JMCN | last post by:
hello i am in the midst of converting all of the "inherited" databases from access 97 to access 2000. one of the issues i have is the basic one: dao.recordset. i have read that i need to simply...
2
by: Rohit | last post by:
Hi, My organisation is using Access 97 and going to replace it with access 2000. Users have n number mdb files created in 97. After installation of Access 2000 I need to conver ALL the mdbs to...
8
by: justicerulesok | last post by:
Hello, I've just started a new job where at interview I said my week point was MS Access. I got the job & they've asked me to create a DB on Access 97 to log calls to the IT department & keep a...
10
by: NEWSGROUPS | last post by:
I work for an organization that is migrating from Access 2000 to Access 2003. About 5 or 6 years ago we migrated from Access 97 to Access 2000 with no problem. Will I have trouble converting these...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.