RecordsetClone or Recordset.Clone

[Sirocco]

Does ADO only work with adp files? I'm trying to convert some real simple
DAO to ADO and it's not working. I"m working with an mdb file, in v2000
on NT and 2003 on XP, mdb backend. I have all the proper references (could
the order be wrong?). It involves searching for a record by using a
combobox and recordset clones. The DAO version works fine - it was
created by the wizard. The ADO sample I'm using is in Mary Chipman's "MS
Access Developers guide to SQL server" seems to have the exact conversion,
on p176, but the edits I'm applying simply don't work, I'm getting a type
mismatch error. Anybody else experience this problem, or similar. Calling
Mary Chipman....

Many thanks in advance.

 

[John Nurick]

Are your declarations ambiguous? Remember that ADODB and DAO libraries
both contain Recordset objects, so
Dim rsR As Recordset
may not mean the same to the compiler as it does to you. It's much safer
to be explicit:
Dim rsR As ADODB.Recordset

 

[Sirocco]

Oh yes, I've made these declarations explicit, but can't get them to work,
even with numerous variations. If I can't get something this simple to
work, the prospect of re-working more complex code seems doomed. Someone
show me the light!

Thanks for responding.

 

[John Nurick]

As far as I know you can use ADO with an mdb back end, but it's much
easier to use DAO so I don't bother - and there are a few things that
can only be done with DAO.

As for type mismatch errors, about 100% of them happen beause the
programmer has got confused (often by the help system<g>). It happens to
all of us. For example,

Dim adoRS As ADODB.Recordset

Set adoRS = Forms(0).RecordsetClone

looks fine but gives a type mismatch error because
Access.Form.RecordsetClone returns a DAO recordset. This isn't clear
from the help system or even the object browser, but can be proved with

Sub Xxx()
Dim o As Object

Set o = Forms(0).RecordsetClone
If TypeOf o Is dao.Recordset Then
Debug.Print "DAO"
ElseIf TypeOf o Is ADODB.Recordset Then
Debug.Print "ADO"
Else
Debug.Print "Something else"
End If
Set o = Nothing
End Sub

Oh yes, I've made these declarations explicit, but can't get them to work,
even with numerous variations. If I can't get something this simple to
work, the prospect of re-working more complex code seems doomed. Someone
show me the light!

Thanks for responding.

 

[Sirocco]

But I'm converting existing DAO-compliant code to ADO because I'm going to
put the tables on a SQL back end. Now, since ADO can also be used with a
mdb back end, I'm doing some DAO to ADO conversion BEFORE I make the switch
to SQL - keep in mind that after the switch to SQL, I'm keeping the mdb
front end. Now, I haven't made the switch yet, and am testing the ADO code
with my mdb front and back ends, BUT it seems some of the ADO code doesn't
work, even code obtained from reputable sources, which is both surprising
and discouraging. Just to be sure, what references need to be checked?

As far as I know you can use ADO with an mdb back end, but it's much
easier to use DAO so I don't bother - and there are a few things that
can only be done with DAO.

As for type mismatch errors, about 100% of them happen beause the
programmer has got confused (often by the help system<g>). It happens to
all of us. For example,

Dim adoRS As ADODB.Recordset

Set adoRS = Forms(0).RecordsetClone

looks fine but gives a type mismatch error because
Access.Form.RecordsetClone returns a DAO recordset. This isn't clear
from the help system or even the object browser, but can be proved with

Sub Xxx()
Dim o As Object

Set o = Forms(0).RecordsetClone
If TypeOf o Is dao.Recordset Then
Debug.Print "DAO"
ElseIf TypeOf o Is ADODB.Recordset Then
Debug.Print "ADO"
Else
Debug.Print "Something else"
End If
Set o = Nothing
End Sub

Oh yes, I've made these declarations explicit, but can't get them to work,
even with numerous variations. If I can't get something this simple to
work, the prospect of re-working more complex code seems doomed. Someone
show me the light!

Thanks for responding.

Are your declarations ambiguous? Remember that ADODB and DAO libraries
both contain Recordset objects, so
Dim rsR As Recordset
may not mean the same to the compiler as it does to you. It's much safer
to be explicit:
Dim rsR As ADODB.Recordset

 

[John Nurick]

If you have omitted references that need to be included, you'll get
compile-time errors. If you have references you think are un-needed,
uncheck them and see what happens when you compile.

Once the code compiles, start testing it and identify the places where
it's not doing what you expect.

But I'm converting existing DAO-compliant code to ADO because I'm going to
put the tables on a SQL back end. Now, since ADO can also be used with a
mdb back end, I'm doing some DAO to ADO conversion BEFORE I make the switch
to SQL - keep in mind that after the switch to SQL, I'm keeping the mdb
front end. Now, I haven't made the switch yet, and am testing the ADO code
with my mdb front and back ends, BUT it seems some of the ADO code doesn't
work, even code obtained from reputable sources, which is both surprising
and discouraging. Just to be sure, what references need to be checked?

As far as I know you can use ADO with an mdb back end, but it's much
easier to use DAO so I don't bother - and there are a few things that
can only be done with DAO.

As for type mismatch errors, about 100% of them happen beause the
programmer has got confused (often by the help system<g>). It happens to
all of us. For example,

Dim adoRS As ADODB.Recordset

Set adoRS = Forms(0).RecordsetClone

looks fine but gives a type mismatch error because
Access.Form.RecordsetClone returns a DAO recordset. This isn't clear
from the help system or even the object browser, but can be proved with

Sub Xxx()
Dim o As Object

Set o = Forms(0).RecordsetClone
If TypeOf o Is dao.Recordset Then
Debug.Print "DAO"
ElseIf TypeOf o Is ADODB.Recordset Then
Debug.Print "ADO"
Else
Debug.Print "Something else"
End If
Set o = Nothing
End Sub

Oh yes, I've made these declarations explicit, but can't get them to work,
even with numerous variations. If I can't get something this simple to
work, the prospect of re-working more complex code seems doomed. Someone
show me the light!

Thanks for responding.

Are your declarations ambiguous? Remember that ADODB and DAO libraries
both contain Recordset objects, so
Dim rsR As Recordset
may not mean the same to the compiler as it does to you. It's much safer
to be explicit:
Dim rsR As ADODB.Recordset

 

[Brendan Reynolds]

Why not post the actual code that raises the error?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

But I'm converting existing DAO-compliant code to ADO because I'm going to
put the tables on a SQL back end. Now, since ADO can also be used with a
mdb back end, I'm doing some DAO to ADO conversion BEFORE I make the
switch
to SQL - keep in mind that after the switch to SQL, I'm keeping the mdb
front end. Now, I haven't made the switch yet, and am testing the ADO
code
with my mdb front and back ends, BUT it seems some of the ADO code doesn't
work, even code obtained from reputable sources, which is both surprising
and discouraging. Just to be sure, what references need to be
checked?

As far as I know you can use ADO with an mdb back end, but it's much
easier to use DAO so I don't bother - and there are a few things that
can only be done with DAO.

As for type mismatch errors, about 100% of them happen beause the
programmer has got confused (often by the help system<g>). It happens to
all of us. For example,

Dim adoRS As ADODB.Recordset

Set adoRS = Forms(0).RecordsetClone

looks fine but gives a type mismatch error because
Access.Form.RecordsetClone returns a DAO recordset. This isn't clear
from the help system or even the object browser, but can be proved with

Sub Xxx()
Dim o As Object

Set o = Forms(0).RecordsetClone
If TypeOf o Is dao.Recordset Then
Debug.Print "DAO"
ElseIf TypeOf o Is ADODB.Recordset Then
Debug.Print "ADO"
Else
Debug.Print "Something else"
End If
Set o = Nothing
End Sub

Oh yes, I've made these declarations explicit, but can't get them to work,
even with numerous variations. If I can't get something this simple
to
work, the prospect of re-working more complex code seems doomed. Someone
show me the light!

Thanks for responding.

Are your declarations ambiguous? Remember that ADODB and DAO libraries
both contain Recordset objects, so
Dim rsR As Recordset
may not mean the same to the compiler as it does to you. It's much safer
to be explicit:
Dim rsR As ADODB.Recordset