Recordset under Access2000 
Author Message
 Recordset under Access2000

Hi all,

I need your help berfore I go completely mad regarding the OpenRecordset
function in Access2000. I have a database with two tables called TimeSheets
and Jobs. All I want to do is search throught the TimeSheets table pull out
the job nubers and stick them into the Jobs table. Easy Yes?... Not {*filter*}y
likely. I am using the following code in a module just to print out a field:

Public Sub PopulateJobList()
    Dim db As Database
    Dim rs As Recordset
    Dim a$

    a$ = "SELECT * FROM TimeSheets ORDER BY [Task1JobNo];"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(a$)
' <<<<<< DOES NOT WORK!!!!!!! Type Mismatch ERROR 13!!!!!!
    Do
        Debug.Print rs.Fields("Task1JobNo").Value
        rs.MoveNext
    Loop
    rs.Close
End Sub

Everything works fine until I try to execute the OpenRecordset command where
it just comes back and says "Type Mismatch" Error 13. Why? I have tried
adding in the extra parameters like the type and lock stuff but Type
Mismatch. I have tried the following as well:

    Set rs = db.OpenRecordset("TimeSheets", dbOpenTable)

But... yes you've guess it "Type Mismatch". Why doesn't that work its in
both of my books and a {*filter*}y example in the MSDN!!!!!

I have even tried rebooting incase Microsoft decided that it was time for my
PC to mess up but that didn't work either.

What am I doing wrong guys and galls?

Please Please Please can anyone help me before I burn all of my Access books
and destroy my {*filter*}y Microsoft Office 2000 CD :-(

Thanks in advance,

Del



Thu, 18 Sep 2003 04:02:16 GMT  
 Recordset under Access2000
did you set the reference in the tools menu from ADO to DAO??

Quote:

>Hi all,

>I need your help berfore I go completely mad regarding the OpenRecordset
>function in Access2000. I have a database with two tables called TimeSheets
>and Jobs. All I want to do is search throught the TimeSheets table pull out
>the job nubers and stick them into the Jobs table. Easy Yes?... Not {*filter*}y
>likely. I am using the following code in a module just to print out a field:

>Public Sub PopulateJobList()
>    Dim db As Database
>    Dim rs As Recordset
>    Dim a$

>    a$ = "SELECT * FROM TimeSheets ORDER BY [Task1JobNo];"
>    Set db = CurrentDb()
>    Set rs = db.OpenRecordset(a$)
>' <<<<<< DOES NOT WORK!!!!!!! Type Mismatch ERROR 13!!!!!!
>    Do
>        Debug.Print rs.Fields("Task1JobNo").Value
>        rs.MoveNext
>    Loop
>    rs.Close
>End Sub

>Everything works fine until I try to execute the OpenRecordset command where
>it just comes back and says "Type Mismatch" Error 13. Why? I have tried
>adding in the extra parameters like the type and lock stuff but Type
>Mismatch. I have tried the following as well:

>    Set rs = db.OpenRecordset("TimeSheets", dbOpenTable)

>But... yes you've guess it "Type Mismatch". Why doesn't that work its in
>both of my books and a {*filter*}y example in the MSDN!!!!!

>I have even tried rebooting incase Microsoft decided that it was time for my
>PC to mess up but that didn't work either.

>What am I doing wrong guys and galls?

>Please Please Please can anyone help me before I burn all of my Access books
>and destroy my {*filter*}y Microsoft Office 2000 CD :-(

>Thanks in advance,

>Del

--

Name  :Trisha Phoon
URL   : http://www.*-*-*.com/ ;    



Thu, 18 Sep 2003 09:43:16 GMT  
 Recordset under Access2000
Yes i've included Microsoft DAO 3.6 object library.

I have had the same code working perfectly under VB6 with no problems at
all. It seems that Access2000 does not like you using openrecordset!?!?

Del


Quote:
> did you set the reference in the tools menu from ADO to DAO??


> >Hi all,

> >I need your help berfore I go completely mad regarding the OpenRecordset
> >function in Access2000. I have a database with two tables called
TimeSheets
> >and Jobs. All I want to do is search throught the TimeSheets table pull
out
> >the job nubers and stick them into the Jobs table. Easy Yes?... Not
{*filter*}y
> >likely. I am using the following code in a module just to print out a
field:

> >Public Sub PopulateJobList()
> >    Dim db As Database
> >    Dim rs As Recordset
> >    Dim a$

> >    a$ = "SELECT * FROM TimeSheets ORDER BY [Task1JobNo];"
> >    Set db = CurrentDb()
> >    Set rs = db.OpenRecordset(a$)
> >' <<<<<< DOES NOT WORK!!!!!!! Type Mismatch ERROR 13!!!!!!
> >    Do
> >        Debug.Print rs.Fields("Task1JobNo").Value
> >        rs.MoveNext
> >    Loop
> >    rs.Close
> >End Sub

> >Everything works fine until I try to execute the OpenRecordset command
where
> >it just comes back and says "Type Mismatch" Error 13. Why? I have tried
> >adding in the extra parameters like the type and lock stuff but Type
> >Mismatch. I have tried the following as well:

> >    Set rs = db.OpenRecordset("TimeSheets", dbOpenTable)

> >But... yes you've guess it "Type Mismatch". Why doesn't that work its in
> >both of my books and a {*filter*}y example in the MSDN!!!!!

> >I have even tried rebooting incase Microsoft decided that it was time for
my
> >PC to mess up but that didn't work either.

> >What am I doing wrong guys and galls?

> >Please Please Please can anyone help me before I burn all of my Access
books
> >and destroy my {*filter*}y Microsoft Office 2000 CD :-(

> >Thanks in advance,

> >Del

> --

> Name  :Trisha Phoon
> URL   : http://www.*-*-*.com/




Thu, 18 Sep 2003 20:50:46 GMT  
 Recordset under Access2000

Quote:

>Yes i've included Microsoft DAO 3.6 object library.

>I have had the same code working perfectly under VB6 with no problems at
>all. It seems that Access2000 does not like you using openrecordset!?!?

>Del

Del

As well as setting a reference to the DAO object library, because the Recordset
object appears in both ADO and DAO with different properties, you need to
disambiguate which type it is:

Dim rsDAO As DAO.Recordset

If you are running A2K, and you still have ADO referenced, chances are that it
is higher up the order than DAO, and therefore because you previously didn't
say what type of recordset it was, Access assumed that it was an ADO recordset.

Jon

Access tips & tricks - http://www.applecore99.com
Microsoft Access webring -
http://nav.webring.yahoo.com/hub?ring=microsoftaccess



Thu, 18 Sep 2003 21:46:50 GMT  
 Recordset under Access2000
Thanks Jon that was the problem ADO had a higher priority than DAO. :-) Now
why wasn't this in my Access books considering that ADO ssems to be
referenced automatically? I must have missed it.

What's better ADO or DAO and how would you know when to use either one? I'm
really new to all this and the books don't seem to give good examples of
were these should be used in preference so I can't get my head around them
yet.

Del

P.S. Thanks to Trisha as well for pointing this out. I just didn't know
exactly what you were getting at! ;-)


Quote:

writes:

> >Yes i've included Microsoft DAO 3.6 object library.

> >I have had the same code working perfectly under VB6 with no problems at
> >all. It seems that Access2000 does not like you using openrecordset!?!?

> >Del

> Del

> As well as setting a reference to the DAO object library, because the
Recordset
> object appears in both ADO and DAO with different properties, you need to
> disambiguate which type it is:

> Dim rsDAO As DAO.Recordset

> If you are running A2K, and you still have ADO referenced, chances are
that it
> is higher up the order than DAO, and therefore because you previously
didn't
> say what type of recordset it was, Access assumed that it was an ADO
recordset.

> Jon

> Access tips & tricks - http://www.applecore99.com
> Microsoft Access webring -
> http://nav.webring.yahoo.com/hub?ring=microsoftaccess



Fri, 19 Sep 2003 02:21:48 GMT  
 Recordset under Access2000

Quote:

>Thanks Jon that was the problem ADO had a higher priority than DAO. :-) Now
>why wasn't this in my Access books considering that ADO ssems to be
>referenced automatically? I must have missed it.

>What's better ADO or DAO and how would you know when to use either one? I'm
>really new to all this and the books don't seem to give good examples of
>were these should be used in preference so I can't get my head around them
>yet.

>Del

>P.S. Thanks to Trisha as well for pointing this out. I just didn't know
>exactly what you were getting at! ;-)

Well, both technologies are not being developed further. If you are doing
active web pages (ie using Active Server Pages) then you will probably find ADO
more useful. However, ADO doesn't seem to be as complete as DAO, and DAO has
been sufficient for enough years for enough developers. I would say that stay
with DAO at the moment, as it is probably easier to get support from the
newsgroups for this Object library.

Jon

Access tips & tricks - http://www.applecore99.com
Microsoft Access webring -
http://nav.webring.yahoo.com/hub?ring=microsoftaccess



Fri, 19 Sep 2003 04:14:55 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Need help with Recordsets in Access2000

2. Recordset error Access2000 ADODB

3. How to use adodb.recordset object in access2000?

4. ADO Access2000 disconnected recordset

5. Access2000/Word2000 Automation Print Hangs

6. Use DAO code in Access2000

7. RecordCount using ADO (Access2000)

8. Converting from Access97 to Access2000

9. Creating Backup in Access2000

10. Access2000 - format 6152346.00 to 6.15 ???

11. access97 to access2000

12. Converting Access97 to Access2000 code

 

 
Powered by phpBB® Forum Software