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]

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:

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

[Russell Potter]

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" <no****@nospam. nospam> wrote in message
news:44******** *************** *********@4ax.c om...

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:

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]

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:

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" <no****@nospam. nospam> wrote in message
news:44******* *************** **********@4ax. com...

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:

>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 givingme >"type mismatch" errors. Should this problem be resolved by changing the
>"options" parameter
>so that a compatible recordset will be returned, thus eliminating theerror? >
>Thanks,
>Russell Potter
>

[Edward]

"Russell Potter" <rpotter AT iinet DOTnet DOT au> wrote in message news:<3f******* *************** @freenews.iinet .net.au>...

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

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]

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:3f******** **************@ freenews.iinet. net.au...

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" <no****@nospam. nospam> wrote in message
news:44******** *************** *********@4ax.c om...

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:

I'm trying to create a recordset using "currentDB.Open RecordSet", using aquery as the
"source" string (the only parameter: so all the others are set to theirdefaults 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

[Russell Potter]

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" <no****@nospam. nospam> wrote in message
news:q4******** *************** *********@4ax.c om...

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:

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" <no****@nospam. nospam> wrote in message
news:44******* *************** **********@4ax. com...

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:

>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]

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:

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" <no****@nospam. nospam> wrote in message
news:q4******* *************** **********@4ax. com...

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:

>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" <no****@nospam. nospam> wrote in message
>news:44******* *************** **********@4ax. com...
>> You'll have to post the actual code for us to really be able to helpyou. >>
>> 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",using a >> >query as the
>> >"source" string (the only parameter: so all the others are set totheir >> >defaults which, I believe,
>> >means the recordset returned will be a "table" one), but VB keepsgiving >me
>> >"type mismatch" errors. Should this problem be resolved by changingthe >> >"options" parameter
>> >so that a compatible recordset will be returned, thus eliminating the
>error?
>> >
>> >Thanks,
>> >Russell Potter
>> >
>>
>

[Russell Potter]

Steve

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.

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