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

recordsets in DAO v. ADO

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.