473,320 Members | 1,862 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,320 software developers and data experts.

recordsets in DAO v. ADO

I have a form with a subform on it. When the user enters information
into the form, I create a sql query in vba and use it to open a
recordset. Then I set the subform's recordset to it.

Using DAO, everything works fine. When I rewrite the code to use ADO,
I get no records in my recordset.

'the dao code (returns records):

Dim d_db as DAO.database
Dim d_rst as DAO.recordset
Set d_db = currentDb()

Dim strTest As String
strTest = "SELECT tblA.AddressesID, tblA.Address1 " & _
"FROM tblA " & _
"INNER JOIN lkpStates ON tblA.StatesID = lkpStates.StatesID "
& _
"WHERE address1 LIKE '*17*' " & _
"ORDER BY Address1"

Set d_rst = d_db.OpenRecordset(strTest)
Set Me.subfrmLookupAddress.Form.Recordset = d_rst

'the ado code (doesn't return records):
Dim a_rst as ADODB.Recordset
Dim a_cnn as ADODB.Connection

Set a_rst = New ADODB.Recordset
a_rst.Open strTest, cnn, adOpenKeyset, adLockOptimistic, adCmdText
Set Me.subfrmLookupAddress.Form.Recordset = a_rst

Can anyone tell me what I'm doing wrong? (My code assigns the
subform's recordset to either the DAO or the ADO recordset, not both
as here.) Also, why can't I close my DAO/ADO recordset object as soon
as I assign the form's recordset property to it? I assumed my
variables and the form's recordset were all pointers, but I guess not.

Thanks,
Sharon
Nov 12 '05 #1
4 3716
When you use ADO, JET uses the ANSI behavior for Like, not the DAO behavior.
This means that the *s are no longer wildcards, an you need to use %s instead.
If you want consistent LIKE behavior with JET via both DAO and ADO, you can
use the ALike (ANSI Like) operator. This operator uses the ANSI wildcards
regardless (always % and _, not * and ?).

On 29 Dec 2003 12:40:06 -0800, sh*********@incamail.com (Sharon Stern) wrote:
I have a form with a subform on it. When the user enters information
into the form, I create a sql query in vba and use it to open a
recordset. Then I set the subform's recordset to it.

Using DAO, everything works fine. When I rewrite the code to use ADO,
I get no records in my recordset.

'the dao code (returns records):

Dim d_db as DAO.database
Dim d_rst as DAO.recordset
Set d_db = currentDb()

Dim strTest As String
strTest = "SELECT tblA.AddressesID, tblA.Address1 " & _
"FROM tblA " & _
"INNER JOIN lkpStates ON tblA.StatesID = lkpStates.StatesID "
& _
"WHERE address1 LIKE '*17*' " & _
"ORDER BY Address1"

Set d_rst = d_db.OpenRecordset(strTest)
Set Me.subfrmLookupAddress.Form.Recordset = d_rst

'the ado code (doesn't return records):
Dim a_rst as ADODB.Recordset
Dim a_cnn as ADODB.Connection

Set a_rst = New ADODB.Recordset
a_rst.Open strTest, cnn, adOpenKeyset, adLockOptimistic, adCmdText
Set Me.subfrmLookupAddress.Form.Recordset = a_rst

Can anyone tell me what I'm doing wrong? (My code assigns the
subform's recordset to either the DAO or the ADO recordset, not both
as here.) Also, why can't I close my DAO/ADO recordset object as soon
as I assign the form's recordset property to it? I assumed my
variables and the form's recordset were all pointers, but I guess not.

Thanks,
Sharon


Nov 12 '05 #2
I just thought to wonder - why are you opening a recordset, then setting the
form's Recordset property when it's simpler and less problematic to just set
the form's RecordSource to the SQL statement (which would automatically be DAO
in an MDB and use the DAO wildcards for LIKE). Also, it appears that you are
working in an MDB, so the use of ADO anywhere at all probably has more
downsides than benefits, regardless of what Microsoft suggests.

On 29 Dec 2003 12:40:06 -0800, sh*********@incamail.com (Sharon Stern) wrote:
I have a form with a subform on it. When the user enters information
into the form, I create a sql query in vba and use it to open a
recordset. Then I set the subform's recordset to it.

Using DAO, everything works fine. When I rewrite the code to use ADO,
I get no records in my recordset.

'the dao code (returns records):

Dim d_db as DAO.database
Dim d_rst as DAO.recordset
Set d_db = currentDb()

Dim strTest As String
strTest = "SELECT tblA.AddressesID, tblA.Address1 " & _
"FROM tblA " & _
"INNER JOIN lkpStates ON tblA.StatesID = lkpStates.StatesID "
& _
"WHERE address1 LIKE '*17*' " & _
"ORDER BY Address1"

Set d_rst = d_db.OpenRecordset(strTest)
Set Me.subfrmLookupAddress.Form.Recordset = d_rst

'the ado code (doesn't return records):
Dim a_rst as ADODB.Recordset
Dim a_cnn as ADODB.Connection

Set a_rst = New ADODB.Recordset
a_rst.Open strTest, cnn, adOpenKeyset, adLockOptimistic, adCmdText
Set Me.subfrmLookupAddress.Form.Recordset = a_rst

Can anyone tell me what I'm doing wrong? (My code assigns the
subform's recordset to either the DAO or the ADO recordset, not both
as here.) Also, why can't I close my DAO/ADO recordset object as soon
as I assign the form's recordset property to it? I assumed my
variables and the form's recordset were all pointers, but I guess not.

Thanks,
Sharon


Nov 12 '05 #3
Thanks for the answers Steve.

I don't know why I wasn't just setting the RecordSource -- it's much
simpler to do it that way.
Also, it appears that you are
working in an MDB, so the use of ADO anywhere at all probably has moredownsides than benefits, regardless of what Microsoft suggests.
You're right, I'm working in an MDB. I'm using ADO where possible
because the original idea was that this project would be upsized to
SQL Server (though that's looking more and more unlikely). What are
the downsides of using ADO in an MDB, assuming it doesn't get upsized?
-- Sharon
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<m0********************************@4ax.com>. .. I just thought to wonder - why are you opening a recordset, then setting the
form's Recordset property when it's simpler and less problematic to just set
the form's RecordSource to the SQL statement (which would automatically be DAO
in an MDB and use the DAO wildcards for LIKE). Also, it appears that you are
working in an MDB, so the use of ADO anywhere at all probably has more
downsides than benefits, regardless of what Microsoft suggests.

On 29 Dec 2003 12:40:06 -0800, sh*********@incamail.com (Sharon Stern) wrote:
I have a form with a subform on it. When the user enters information
into the form, I create a sql query in vba and use it to open a
recordset. Then I set the subform's recordset to it.

Using DAO, everything works fine. When I rewrite the code to use ADO,
I get no records in my recordset.

'the dao code (returns records):

Dim d_db as DAO.database
Dim d_rst as DAO.recordset
Set d_db = currentDb()

Dim strTest As String
strTest = "SELECT tblA.AddressesID, tblA.Address1 " & _
"FROM tblA " & _
"INNER JOIN lkpStates ON tblA.StatesID = lkpStates.StatesID "
& _
"WHERE address1 LIKE '*17*' " & _
"ORDER BY Address1"

Set d_rst = d_db.OpenRecordset(strTest)
Set Me.subfrmLookupAddress.Form.Recordset = d_rst

'the ado code (doesn't return records):
Dim a_rst as ADODB.Recordset
Dim a_cnn as ADODB.Connection

Set a_rst = New ADODB.Recordset
a_rst.Open strTest, cnn, adOpenKeyset, adLockOptimistic, adCmdText
Set Me.subfrmLookupAddress.Form.Recordset = a_rst

Can anyone tell me what I'm doing wrong? (My code assigns the
subform's recordset to either the DAO or the ADO recordset, not both
as here.) Also, why can't I close my DAO/ADO recordset object as soon
as I assign the form's recordset property to it? I assumed my
variables and the form's recordset were all pointers, but I guess not.

Thanks,
Sharon

Nov 12 '05 #4
In my experience, it's actually better to keep using DAO with Access as a
front-end to SQL Server. ADO makes more sense in a VB project or VBA project
that is not in Access. I'll have time to post a longer reply later.

On 30 Dec 2003 13:12:41 -0800, sh*********@incamail.com (Sharon Stern) wrote:
Thanks for the answers Steve.

I don't know why I wasn't just setting the RecordSource -- it's much
simpler to do it that way.
Also, it appears that you are
working in an MDB, so the use of ADO anywhere at all probably has

more
downsides than benefits, regardless of what Microsoft suggests.


You're right, I'm working in an MDB. I'm using ADO where possible
because the original idea was that this project would be upsized to
SQL Server (though that's looking more and more unlikely). What are
the downsides of using ADO in an MDB, assuming it doesn't get upsized?
-- Sharon
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<m0********************************@4ax.com>. ..
I just thought to wonder - why are you opening a recordset, then setting the
form's Recordset property when it's simpler and less problematic to just set
the form's RecordSource to the SQL statement (which would automatically be DAO
in an MDB and use the DAO wildcards for LIKE). Also, it appears that you are
working in an MDB, so the use of ADO anywhere at all probably has more
downsides than benefits, regardless of what Microsoft suggests.

On 29 Dec 2003 12:40:06 -0800, sh*********@incamail.com (Sharon Stern) wrote:
>I have a form with a subform on it. When the user enters information
>into the form, I create a sql query in vba and use it to open a
>recordset. Then I set the subform's recordset to it.
>
>Using DAO, everything works fine. When I rewrite the code to use ADO,
>I get no records in my recordset.
>
>'the dao code (returns records):
>
>Dim d_db as DAO.database
>Dim d_rst as DAO.recordset
>Set d_db = currentDb()
>
>Dim strTest As String
>strTest = "SELECT tblA.AddressesID, tblA.Address1 " & _
> "FROM tblA " & _
> "INNER JOIN lkpStates ON tblA.StatesID = lkpStates.StatesID "
>& _
> "WHERE address1 LIKE '*17*' " & _
> "ORDER BY Address1"
>
>Set d_rst = d_db.OpenRecordset(strTest)
>Set Me.subfrmLookupAddress.Form.Recordset = d_rst
>
>'the ado code (doesn't return records):
>Dim a_rst as ADODB.Recordset
>Dim a_cnn as ADODB.Connection
>
>Set a_rst = New ADODB.Recordset
>a_rst.Open strTest, cnn, adOpenKeyset, adLockOptimistic, adCmdText
>Set Me.subfrmLookupAddress.Form.Recordset = a_rst
>
>Can anyone tell me what I'm doing wrong? (My code assigns the
>subform's recordset to either the DAO or the ADO recordset, not both
>as here.) Also, why can't I close my DAO/ADO recordset object as soon
>as I assign the form's recordset property to it? I assumed my
>variables and the form's recordset were all pointers, but I guess not.
>
>Thanks,
>Sharon


Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Marco Alting | last post by:
Is it possible to INNER JOIN two recordsets in ASP? I don't mean the normal JOIN you would use with two tables, but actually join the recordsets in ASP?
2
by: Steve Marciniak | last post by:
I'm trying to display different recordsets (which are 1 field each) as columns right next to one another. For example, Recordset1 is displayed on the left hand side of the screen. Recordset2 is...
6
by: Steve Jorgensen | last post by:
I keep having problems in which ADO disconnected recordset work under some circumstances, but lose all their data at other times, having no rows or fields, though the recordset object still exists....
2
by: Pieter Linden | last post by:
The answer to this one is probably "test it yourself and find out!", but I'll ask anyway. Pursuant to my previous question - sending separate recordsets to Word using the CreateTableFromRecordset...
1
by: lakshmi | last post by:
Hi all, I recently rewrote a data intensive C++ program in C#. The C++ program was traversing 3 recordsets that were all open at the same time. I replaced those 3 recordsets with 3 .NET data...
16
by: Randy Harris | last post by:
I was inspired by the recent discussion of returning multiple recordsets to ADO from a stored procedure. (Amazed is probably more accurate). I asked about how to accomplish same with Oracle and...
24
by: Donald Grove | last post by:
I want to populate an array with values from an ado recordset (multiple rows) I use the absolute position of the cursor in the recordset to define the row of my array to be populated. I have a...
4
by: mrmagoo | last post by:
I'm building a vb.net Forms project that is getting data from a SQL Server database. One of the main goals of the project is to be really responsive to events, such as textbox change events. I...
4
by: rdemyan via AccessMonster.com | last post by:
Can someone help me with creating code that will look for DAO recordsets in modules and then check to see if the recordset is also closed in the module. All of my recordsets are of the form rs*...
11
by: BeckR | last post by:
Hello - Thanks for reading my post. I am a newbie when it comes to VBA programming, but have managed to do what I need to do, until now. I have an Access 2000 database (running WinXP Pro...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
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)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.