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* 7 4497
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*
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*
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*
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*
"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
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 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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
--------------------------------------------------------------------------------
...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |