OpenRecordSet ...

[Russell Potter]

I'm trying to create a recordset using "currentDB.Open RecordSet", using a
query as the
"source" string (the only parameter: so all the others are set to their
defaults which, I believe,
means the recordset returned will be a "table" one), but VB keeps giving me
"type mismatch" errors. Should this problem be resolved by changing the
"options" parameter
so that a compatible recordset will be returned, thus eliminating the error?

Thanks,
Russell Potter

[Steve Jorgensen]

Re: OpenRecordSet ...

You'll have to post the actual code for us to really be able to help you.

On Mon, 8 Dec 2003 19:00:03 +1100, "Russell Potter" <rpotter AT iinet DOTnet
DOT au> wrote:
[color=blue]
>I'm trying to create a recordset using "currentDB.Open RecordSet", using a
>query as the
>"source" string (the only parameter: so all the others are set to their
>defaults which, I believe,
>means the recordset returned will be a "table" one), but VB keeps giving me
>"type mismatch" errors. Should this problem be resolved by changing the
>"options" parameter
>so that a compatible recordset will be returned, thus eliminating the error?
>
>Thanks,
>Russell Potter
>[/color]

[Russell Potter]

Re: OpenRecordSet ...

OK then, here's the code in question (that generates the error):
--------------------

Dim r As Recordset
....
Set r = CurrentDb.OpenR ecordset( _
"SELECT Desc as Topic, CategoryName as " & _
"Category, StatusName as Status FROM " & _
"qryTopicsOverv iew")

---------------------

so, as you can see, the "source" string is a/an SQL statement,
which I was thinking might be causing the type mismatch
since, in the absence of a "type" parameter, a "table" type
recordset is created while, if I remember correctly, a/an SQL
statement creates something like a "dynaset" type recordset ...

But am I barking up the right tree here?

Russell

"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
news:44c8tv0emb ami2s0m0tdlts63 7uj1vokig@4ax.c om...[color=blue]
> You'll have to post the actual code for us to really be able to help you.
>
> On Mon, 8 Dec 2003 19:00:03 +1100, "Russell Potter" <rpotter AT iinet[/color]
DOTnet[color=blue]
> DOT au> wrote:
>[color=green]
> >I'm trying to create a recordset using "currentDB.Open RecordSet", using a
> >query as the
> >"source" string (the only parameter: so all the others are set to their
> >defaults which, I believe,
> >means the recordset returned will be a "table" one), but VB keeps giving[/color][/color]
me[color=blue][color=green]
> >"type mismatch" errors. Should this problem be resolved by changing the
> >"options" parameter
> >so that a compatible recordset will be returned, thus eliminating the[/color][/color]
error?[color=blue][color=green]
> >
> >Thanks,
> >Russell Potter
> >[/color]
>[/color]

[Steve Jorgensen]

Re: OpenRecordSet ...

First off, although it is not the cause of the problem you are asking about,
you have a serious problem here in how you are using CurrentDB. CurrentDB is
not a reference to apermanent Database object instance, it is a function that
returns a new database object instance each time it is called. That means
that, by the time you try to read the recordset, it's database instance will
be out of scope, and this can lead to unpreductable results. You shoulr
always assign CurrentDB to a DAO.Database variable before using it, and you
should set DAO object variable references = Nothing in reverse order when yuo
are done with them.

Next, since you are correctly passing a string to the first argument of
OpenRecordset, I suspect that the error might be occurring in the query
itself. To test this, try typing that same SQL into a querydef object, and
running it. There is a clear problem in yur SQL that might or might not be
the cause of your error, but will need to be fixed, and that is that you are
using the SQL keyword, Desc as a column name without putting brackets around
it. I recommend not using that name at all, but if you do, it needs brackets.

So the steps are to first change the Desc column name or putting brackets
around it, then try pasting that directly into a querydef, and seeing if it
runs from there. Finally, I recommend that you make your code look something
like this...

Dim db As DAO.Database
Dim rst As DAO.Recordset
....
Set db = CurrnetDB
Set rst = db.OpenRecordse t( _
"SELECT [Desc] as Topic, CategoryName as " & _
"Category, StatusName as Status FROM " & _
"qryTopicsOverv iew")
...
rst.Close: Set rst = Nothing
Set db = Nothing

On Mon, 8 Dec 2003 19:30:12 +1100, "Russell Potter" <rpotter AT iinet DOTnet
DOT au> wrote:
[color=blue]
>OK then, here's the code in question (that generates the error):
>--------------------
>
> Dim r As Recordset
> ....
> Set r = CurrentDb.OpenR ecordset( _
> "SELECT Desc as Topic, CategoryName as " & _
> "Category, StatusName as Status FROM " & _
> "qryTopicsOverv iew")
>
>---------------------
>
>so, as you can see, the "source" string is a/an SQL statement,
>which I was thinking might be causing the type mismatch
>since, in the absence of a "type" parameter, a "table" type
>recordset is created while, if I remember correctly, a/an SQL
>statement creates something like a "dynaset" type recordset ...
>
>But am I barking up the right tree here?
>
>Russell
>
>"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
>news:44c8tv0em bami2s0m0tdlts6 37uj1vokig@4ax. com...[color=green]
>> You'll have to post the actual code for us to really be able to help you.
>>
>> On Mon, 8 Dec 2003 19:00:03 +1100, "Russell Potter" <rpotter AT iinet[/color]
>DOTnet[color=green]
>> DOT au> wrote:
>>[color=darkred]
>> >I'm trying to create a recordset using "currentDB.Open RecordSet", using a
>> >query as the
>> >"source" string (the only parameter: so all the others are set to their
>> >defaults which, I believe,
>> >means the recordset returned will be a "table" one), but VB keeps giving[/color][/color]
>me[color=green][color=darkred]
>> >"type mismatch" errors. Should this problem be resolved by changing the
>> >"options" parameter
>> >so that a compatible recordset will be returned, thus eliminating the[/color][/color]
>error?[color=green][color=darkred]
>> >
>> >Thanks,
>> >Russell Potter
>> >[/color]
>>[/color]
>[/color]

[Edward]

Re: OpenRecordSet ...

"Russell Potter" <rpotter AT iinet DOTnet DOT au> wrote in message news:<3fd42e7b$ 0$1736$5a62ac22 @freenews.iinet .net.au>...[color=blue]
> I'm trying to create a recordset using "currentDB.Open RecordSet", using a
> query as the
> "source" string (the only parameter: so all the others are set to their
> defaults which, I believe,
> means the recordset returned will be a "table" one), but VB keeps giving me
> "type mismatch" errors. Should this problem be resolved by changing the
> "options" parameter
> so that a compatible recordset will be returned, thus eliminating the error?
>
> Thanks,
> Russell Potter[/color]


Could you post the code, please? There may be (an)other error(s) of
which you are not aware.

Edward
--
The reading group's reading group:

http://www.bookgroup.org.uk

[Terry Kreft]

Re: OpenRecordSet ...

Make sure you have a reference to DAO and then change
Dim r As Recordset
to
Dim r As DAO.Recordset

.... and no you won't get a "table" type recordset from this you'll get a
Dynaset (Dynamic Recordset)


Terry

"Russell Potter" <rpotter AT iinet DOTnet DOT au> wrote in message
news:3fd4358c$0 $1729$5a62ac22@ freenews.iinet. net.au...[color=blue]
> OK then, here's the code in question (that generates the error):
> --------------------
>
> Dim r As Recordset
> ....
> Set r = CurrentDb.OpenR ecordset( _
> "SELECT Desc as Topic, CategoryName as " & _
> "Category, StatusName as Status FROM " & _
> "qryTopicsOverv iew")
>
> ---------------------
>
> so, as you can see, the "source" string is a/an SQL statement,
> which I was thinking might be causing the type mismatch
> since, in the absence of a "type" parameter, a "table" type
> recordset is created while, if I remember correctly, a/an SQL
> statement creates something like a "dynaset" type recordset ...
>
> But am I barking up the right tree here?
>
> Russell
>
> "Steve Jorgensen" <nospam@nospam. nospam> wrote in message
> news:44c8tv0emb ami2s0m0tdlts63 7uj1vokig@4ax.c om...[color=green]
> > You'll have to post the actual code for us to really be able to help[/color][/color]
you.[color=blue][color=green]
> >
> > On Mon, 8 Dec 2003 19:00:03 +1100, "Russell Potter" <rpotter AT iinet[/color]
> DOTnet[color=green]
> > DOT au> wrote:
> >[color=darkred]
> > >I'm trying to create a recordset using "currentDB.Open RecordSet", using[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > >query as the
> > >"source" string (the only parameter: so all the others are set to[/color][/color][/color]
their[color=blue][color=green][color=darkred]
> > >defaults which, I believe,
> > >means the recordset returned will be a "table" one), but VB keeps[/color][/color][/color]
giving[color=blue]
> me[color=green][color=darkred]
> > >"type mismatch" errors. Should this problem be resolved by changing[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > >"options" parameter
> > >so that a compatible recordset will be returned, thus eliminating the[/color][/color]
> error?[color=green][color=darkred]
> > >
> > >Thanks,
> > >Russell Potter
> > >[/color]
> >[/color]
>
>[/color]

[Russell Potter]

Re: OpenRecordSet ...

Steve.

Sorry to sound like such a complete dunce and waste everyone's time,
but the code you gave me to try wouldn't compile because I don't have
a reference to the DAO type library, but I can't add the reference
because, no matter what I do, my "Tools -> references" command
seems to stay permanently disabled.

So .. I was wondering ... under what circumstances would it usually
become enabled?

Thanks,
Russell

"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
news:q4g8tvg1fu 1oqtoaah60i7qvt 3dgv2ic5u@4ax.c om...[color=blue]
> First off, although it is not the cause of the problem you are asking[/color]
about,[color=blue]
> you have a serious problem here in how you are using CurrentDB. CurrentDB[/color]
is[color=blue]
> not a reference to apermanent Database object instance, it is a function[/color]
that[color=blue]
> returns a new database object instance each time it is called. That means
> that, by the time you try to read the recordset, it's database instance[/color]
will[color=blue]
> be out of scope, and this can lead to unpreductable results. You shoulr
> always assign CurrentDB to a DAO.Database variable before using it, and[/color]
you[color=blue]
> should set DAO object variable references = Nothing in reverse order when[/color]
yuo[color=blue]
> are done with them.
>
> Next, since you are correctly passing a string to the first argument of
> OpenRecordset, I suspect that the error might be occurring in the query
> itself. To test this, try typing that same SQL into a querydef object,[/color]
and[color=blue]
> running it. There is a clear problem in yur SQL that might or might not[/color]
be[color=blue]
> the cause of your error, but will need to be fixed, and that is that you[/color]
are[color=blue]
> using the SQL keyword, Desc as a column name without putting brackets[/color]
around[color=blue]
> it. I recommend not using that name at all, but if you do, it needs[/color]
brackets.[color=blue]
>
> So the steps are to first change the Desc column name or putting brackets
> around it, then try pasting that directly into a querydef, and seeing if[/color]
it[color=blue]
> runs from there. Finally, I recommend that you make your code look[/color]
something[color=blue]
> like this...
>
> Dim db As DAO.Database
> Dim rst As DAO.Recordset
> ....
> Set db = CurrnetDB
> Set rst = db.OpenRecordse t( _
> "SELECT [Desc] as Topic, CategoryName as " & _
> "Category, StatusName as Status FROM " & _
> "qryTopicsOverv iew")
> ...
> rst.Close: Set rst = Nothing
> Set db = Nothing
>
> On Mon, 8 Dec 2003 19:30:12 +1100, "Russell Potter" <rpotter AT iinet[/color]
DOTnet[color=blue]
> DOT au> wrote:
>[color=green]
> >OK then, here's the code in question (that generates the error):
> >--------------------
> >
> > Dim r As Recordset
> > ....
> > Set r = CurrentDb.OpenR ecordset( _
> > "SELECT Desc as Topic, CategoryName as " & _
> > "Category, StatusName as Status FROM " & _
> > "qryTopicsOverv iew")
> >
> >---------------------
> >
> >so, as you can see, the "source" string is a/an SQL statement,
> >which I was thinking might be causing the type mismatch
> >since, in the absence of a "type" parameter, a "table" type
> >recordset is created while, if I remember correctly, a/an SQL
> >statement creates something like a "dynaset" type recordset ...
> >
> >But am I barking up the right tree here?
> >
> >Russell
> >
> >"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
> >news:44c8tv0em bami2s0m0tdlts6 37uj1vokig@4ax. com...[color=darkred]
> >> You'll have to post the actual code for us to really be able to help[/color][/color][/color]
you.[color=blue][color=green][color=darkred]
> >>
> >> On Mon, 8 Dec 2003 19:00:03 +1100, "Russell Potter" <rpotter AT iinet[/color]
> >DOTnet[color=darkred]
> >> DOT au> wrote:
> >>
> >> >I'm trying to create a recordset using "currentDB.Open RecordSet",[/color][/color][/color]
using a[color=blue][color=green][color=darkred]
> >> >query as the
> >> >"source" string (the only parameter: so all the others are set to[/color][/color][/color]
their[color=blue][color=green][color=darkred]
> >> >defaults which, I believe,
> >> >means the recordset returned will be a "table" one), but VB keeps[/color][/color][/color]
giving[color=blue][color=green]
> >me[color=darkred]
> >> >"type mismatch" errors. Should this problem be resolved by changing[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> >"options" parameter
> >> >so that a compatible recordset will be returned, thus eliminating the[/color]
> >error?[color=darkred]
> >> >
> >> >Thanks,
> >> >Russell Potter
> >> >
> >>[/color]
> >[/color]
>[/color]

[Steve Jorgensen]

Re: OpenRecordSet ...

Oh - the code you originally posted can only work if there is reference to
DAO. Because CurrentDB returns a DAO database instance, but your variable was
an ADO recordset since you didn't have the DAO reference. That doesn't work.

In Access 2000 and newer, by default, only the ADO reference appears in a new
datbase. To get the DAO reference, you have to add it. I don't know why your
Tools -> References is disabled, but it could be that you are trying to access
it when you code is running or stopeed in break mode. Try resetting the code,
and see if that fixes it.

On Tue, 9 Dec 2003 16:40:54 +1100, "Russell Potter" <rpotter AT iinet DOTnet
DOT au> wrote:
[color=blue]
>Steve.
>
>Sorry to sound like such a complete dunce and waste everyone's time,
>but the code you gave me to try wouldn't compile because I don't have
>a reference to the DAO type library, but I can't add the reference
>because, no matter what I do, my "Tools -> references" command
>seems to stay permanently disabled.
>
>So .. I was wondering ... under what circumstances would it usually
> become enabled?
>
>Thanks,
>Russell
>
>"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
>news:q4g8tvg1f u1oqtoaah60i7qv t3dgv2ic5u@4ax. com...[color=green]
>> First off, although it is not the cause of the problem you are asking[/color]
>about,[color=green]
>> you have a serious problem here in how you are using CurrentDB. CurrentDB[/color]
>is[color=green]
>> not a reference to apermanent Database object instance, it is a function[/color]
>that[color=green]
>> returns a new database object instance each time it is called. That means
>> that, by the time you try to read the recordset, it's database instance[/color]
>will[color=green]
>> be out of scope, and this can lead to unpreductable results. You shoulr
>> always assign CurrentDB to a DAO.Database variable before using it, and[/color]
>you[color=green]
>> should set DAO object variable references = Nothing in reverse order when[/color]
>yuo[color=green]
>> are done with them.
>>
>> Next, since you are correctly passing a string to the first argument of
>> OpenRecordset, I suspect that the error might be occurring in the query
>> itself. To test this, try typing that same SQL into a querydef object,[/color]
>and[color=green]
>> running it. There is a clear problem in yur SQL that might or might not[/color]
>be[color=green]
>> the cause of your error, but will need to be fixed, and that is that you[/color]
>are[color=green]
>> using the SQL keyword, Desc as a column name without putting brackets[/color]
>around[color=green]
>> it. I recommend not using that name at all, but if you do, it needs[/color]
>brackets.[color=green]
>>
>> So the steps are to first change the Desc column name or putting brackets
>> around it, then try pasting that directly into a querydef, and seeing if[/color]
>it[color=green]
>> runs from there. Finally, I recommend that you make your code look[/color]
>something[color=green]
>> like this...
>>
>> Dim db As DAO.Database
>> Dim rst As DAO.Recordset
>> ....
>> Set db = CurrnetDB
>> Set rst = db.OpenRecordse t( _
>> "SELECT [Desc] as Topic, CategoryName as " & _
>> "Category, StatusName as Status FROM " & _
>> "qryTopicsOverv iew")
>> ...
>> rst.Close: Set rst = Nothing
>> Set db = Nothing
>>
>> On Mon, 8 Dec 2003 19:30:12 +1100, "Russell Potter" <rpotter AT iinet[/color]
>DOTnet[color=green]
>> DOT au> wrote:
>>[color=darkred]
>> >OK then, here's the code in question (that generates the error):
>> >--------------------
>> >
>> > Dim r As Recordset
>> > ....
>> > Set r = CurrentDb.OpenR ecordset( _
>> > "SELECT Desc as Topic, CategoryName as " & _
>> > "Category, StatusName as Status FROM " & _
>> > "qryTopicsOverv iew")
>> >
>> >---------------------
>> >
>> >so, as you can see, the "source" string is a/an SQL statement,
>> >which I was thinking might be causing the type mismatch
>> >since, in the absence of a "type" parameter, a "table" type
>> >recordset is created while, if I remember correctly, a/an SQL
>> >statement creates something like a "dynaset" type recordset ...
>> >
>> >But am I barking up the right tree here?
>> >
>> >Russell
>> >
>> >"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
>> >news:44c8tv0em bami2s0m0tdlts6 37uj1vokig@4ax. com...
>> >> You'll have to post the actual code for us to really be able to help[/color][/color]
>you.[color=green][color=darkred]
>> >>
>> >> On Mon, 8 Dec 2003 19:00:03 +1100, "Russell Potter" <rpotter AT iinet
>> >DOTnet
>> >> DOT au> wrote:
>> >>
>> >> >I'm trying to create a recordset using "currentDB.Open RecordSet",[/color][/color]
>using a[color=green][color=darkred]
>> >> >query as the
>> >> >"source" string (the only parameter: so all the others are set to[/color][/color]
>their[color=green][color=darkred]
>> >> >defaults which, I believe,
>> >> >means the recordset returned will be a "table" one), but VB keeps[/color][/color]
>giving[color=green][color=darkred]
>> >me
>> >> >"type mismatch" errors. Should this problem be resolved by changing[/color][/color]
>the[color=green][color=darkred]
>> >> >"options" parameter
>> >> >so that a compatible recordset will be returned, thus eliminating the
>> >error?
>> >> >
>> >> >Thanks,
>> >> >Russell Potter
>> >> >
>> >>
>> >[/color]
>>[/color]
>[/color]

[Russell Potter]

Re: OpenRecordSet ...


Steve
[color=blue]
> Oh - the code you originally posted can only work if there is reference to
> DAO. Because CurrentDB returns a DAO database instance, but your variable[/color]
was[color=blue]
> an ADO recordset since you didn't have the DAO reference. That doesn't[/color]
work.

Thanks for that info: I figured out for myself, through some of
experimentation ,
the circumstances under which the "Tools -> References" command becomes
enabled: I just needed, , as you said in your earlier post, to reset the
code. I
hadn't had enough time to do those experiments yesterday before I posted so
prematurely :-).

Once that DAO reference was enabled, and I'd changed the code to
what you had suggested, it compiled fine and, even better, now works
perfectly! ... and getting that recordset populated correctly means my data
now exports beautifully to both Word and Excel, impressing the boss no end,
so thanks!

Russell