recordsets in DAO v. ADO

[Sharon Stern]

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.AddressesI D, tblA.Address1 " & _
"FROM tblA " & _
"INNER JOIN lkpStates ON tblA.StatesID = lkpStates.State sID "
& _
"WHERE address1 LIKE '*17*' " & _
"ORDER BY Address1"

Set d_rst = d_db.OpenRecord set(strTest)
Set Me.subfrmLookup Address.Form.Re cordset = d_rst

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

Set a_rst = New ADODB.Recordset
a_rst.Open strTest, cnn, adOpenKeyset, adLockOptimisti c, adCmdText
Set Me.subfrmLookup Address.Form.Re cordset = 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

[Steve Jorgensen]

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*********@inc amail.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.AddressesI D, tblA.Address1 " & _
"FROM tblA " & _
"INNER JOIN lkpStates ON tblA.StatesID = lkpStates.State sID "
& _
"WHERE address1 LIKE '*17*' " & _
"ORDER BY Address1"

Set d_rst = d_db.OpenRecord set(strTest)
Set Me.subfrmLookup Address.Form.Re cordset = d_rst

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

Set a_rst = New ADODB.Recordset
a_rst.Open strTest, cnn, adOpenKeyset, adLockOptimisti c, adCmdText
Set Me.subfrmLookup Address.Form.Re cordset = 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

[Steve Jorgensen]

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*********@inc amail.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.AddressesI D, tblA.Address1 " & _
"FROM tblA " & _
"INNER JOIN lkpStates ON tblA.StatesID = lkpStates.State sID "
& _
"WHERE address1 LIKE '*17*' " & _
"ORDER BY Address1"

Set d_rst = d_db.OpenRecord set(strTest)
Set Me.subfrmLookup Address.Form.Re cordset = d_rst

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

Set a_rst = New ADODB.Recordset
a_rst.Open strTest, cnn, adOpenKeyset, adLockOptimisti c, adCmdText
Set Me.subfrmLookup Address.Form.Re cordset = 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

[Sharon Stern]

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*********@inc amail.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.AddressesI D, tblA.Address1 " & _
"FROM tblA " & _
"INNER JOIN lkpStates ON tblA.StatesID = lkpStates.State sID "
& _
"WHERE address1 LIKE '*17*' " & _
"ORDER BY Address1"

Set d_rst = d_db.OpenRecord set(strTest)
Set Me.subfrmLookup Address.Form.Re cordset = d_rst

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

Set a_rst = New ADODB.Recordset
a_rst.Open strTest, cnn, adOpenKeyset, adLockOptimisti c, adCmdText
Set Me.subfrmLookup Address.Form.Re cordset = 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

[Steve Jorgensen]

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*********@inc amail.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*********@inc amail.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.AddressesI D, tblA.Address1 " & _
> "FROM tblA " & _
> "INNER JOIN lkpStates ON tblA.StatesID = lkpStates.State sID "
>& _
> "WHERE address1 LIKE '*17*' " & _
> "ORDER BY Address1"
>
>Set d_rst = d_db.OpenRecord set(strTest)
>Set Me.subfrmLookup Address.Form.Re cordset = d_rst
>
>'the ado code (doesn't return records):
>Dim a_rst as ADODB.Recordset
>Dim a_cnn as ADODB.Connectio n
>
>Set a_rst = New ADODB.Recordset
>a_rst.Open strTest, cnn, adOpenKeyset, adLockOptimisti c, adCmdText
>Set Me.subfrmLookup Address.Form.Re cordset = 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