Transferring a Query To Excel

[Guest]

Dear all,

I have a query named [Query_Name]. I want to transfer the recordset over to
Excel.

So by referring to the <http://www.mvps.org/access/modules/mdl0035.htm>, I
have the following codes:

…
Dim db As QueryTable
…
Set db = CurrentDb
....
Set rs = db.OpenRecordset("Query_Name", dbOpenTable)

Then I’ll get the following error:
Run-time error ‘13’ – Type mismatch.

The debugger will point to the “Set db = CurrentDb†statement.

What kinds of mistake I have made?

Thanks in advance!!

 

[Brendan Reynolds]

Dim db As QueryTable

... should be ...

Dim db As Database

Also don't use dbOpenTable. You can only use the dbOpenTable option with
local tables, not with queries or linked tables. Try dbOpenDynaset or don't
pass the optional argument at all, as dbOpenDynaset is the default if the
source is not a local table. See the help topic 'OpenRecordset Method (DAO)'
for details.

 

[Nikos Yannacopoulos]

Florence,

Dim db As QueryTable

What is this? Unless it's a new object type in A2007 (which I doubt), it
doesn't exist! I couldn't find it in the referenced link either... you
were probably aiming to type
Dim db As Database

HTH,
Nikos

 

[Guest]

Dear Brendan,

Same error message prompted. The debugger this time stopped at:
Set rs = db.OpenRecordset("Query_Name")
or
Set rs = db.OpenRecordset("Query_Name", dbOpenDynaset)
The Watches shows that the value of "rs" is Nothing.
(Remark: Dim rs As Recordset)

What should I do now?

Thx!!

Dim db As QueryTable

... should be ...

Dim db As Database

Also don't use dbOpenTable. You can only use the dbOpenTable option with
local tables, not with queries or linked tables. Try dbOpenDynaset or don't
pass the optional argument at all, as dbOpenDynaset is the default if the
source is not a local table. See the help topic 'OpenRecordset Method (DAO)'
for details.

--
Brendan Reynolds
Access MVP

Dear all,

I have a query named [Query_Name]. I want to transfer the recordset over
to
Excel.

So by referring to the <http://www.mvps.org/access/modules/mdl0035.htm>, I
have the following codes:

.
Dim db As QueryTable
.
Set db = CurrentDb
...
Set rs = db.OpenRecordset("Query_Name", dbOpenTable)

Then I'll get the following error:
Run-time error '13' - Type mismatch.

The debugger will point to the "Set db = CurrentDb" statement.

What kinds of mistake I have made?

Thanks in advance!!

 

[Guest]

Dear Brendan,

The script has been changed like this:
…
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Query_Name", dbOpenDynaset)
…
Same error message prompted and the debugger stopped at the 4th line no
matter with dbOpenDynaset or not.
The Watches shows that the value of “rs†is Nothing.
What should I do now?

Thx!!

Dim db As QueryTable

... should be ...

Dim db As Database

Also don't use dbOpenTable. You can only use the dbOpenTable option with
local tables, not with queries or linked tables. Try dbOpenDynaset or don't
pass the optional argument at all, as dbOpenDynaset is the default if the
source is not a local table. See the help topic 'OpenRecordset Method (DAO)'
for details.

--
Brendan Reynolds
Access MVP

Dear all,

I have a query named [Query_Name]. I want to transfer the recordset over
to
Excel.

So by referring to the <http://www.mvps.org/access/modules/mdl0035.htm>, I
have the following codes:

.
Dim db As QueryTable
.
Set db = CurrentDb
...
Set rs = db.OpenRecordset("Query_Name", dbOpenTable)

Then I'll get the following error:
Run-time error '13' - Type mismatch.

The debugger will point to the "Set db = CurrentDb" statement.

What kinds of mistake I have made?

Thanks in advance!!

 

[Brendan Reynolds]

It's probably the problem with ADO vs DAO references explained here ...

http://www.mvps.org/access/bugs/bugs0031.htm

--
Brendan Reynolds
Access MVP

Dear Brendan,

Same error message prompted. The debugger this time stopped at:
Set rs = db.OpenRecordset("Query_Name")
or
Set rs = db.OpenRecordset("Query_Name", dbOpenDynaset)
The Watches shows that the value of "rs" is Nothing.
(Remark: Dim rs As Recordset)

What should I do now?

Thx!!

Dim db As QueryTable

... should be ...

Dim db As Database

Also don't use dbOpenTable. You can only use the dbOpenTable option with
local tables, not with queries or linked tables. Try dbOpenDynaset or
don't
pass the optional argument at all, as dbOpenDynaset is the default if the
source is not a local table. See the help topic 'OpenRecordset Method
(DAO)'
for details.

--
Brendan Reynolds
Access MVP

Dear all,

I have a query named [Query_Name]. I want to transfer the recordset
over
to
Excel.

So by referring to the
<http://www.mvps.org/access/modules/mdl0035.htm>, I
have the following codes:

.
Dim db As QueryTable
.
Set db = CurrentDb
...
Set rs = db.OpenRecordset("Query_Name", dbOpenTable)

Then I'll get the following error:
Run-time error '13' - Type mismatch.

The debugger will point to the "Set db = CurrentDb" statement.

What kinds of mistake I have made?

Thanks in advance!!

 

[Guest]

Dear Nikos,

The script has been changed like this:
…
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Query_Name", dbOpenDynaset)
…
Same error message prompted and the debugger stopped at the 4th line no
matter with dbOpenDynaset or not.
The Watches shows that the value of “rs†is Nothing.

Any idea?

 

[Guest]

Thanks a lot Brendan,

I have specified the "rs" like this and it works now:
Dim rs As DAO.Recordset

Cheers!!

It's probably the problem with ADO vs DAO references explained here ...

http://www.mvps.org/access/bugs/bugs0031.htm

--
Brendan Reynolds
Access MVP

Dear Brendan,

Same error message prompted. The debugger this time stopped at:
Set rs = db.OpenRecordset("Query_Name")
or
Set rs = db.OpenRecordset("Query_Name", dbOpenDynaset)
The Watches shows that the value of "rs" is Nothing.
(Remark: Dim rs As Recordset)

What should I do now?

Thx!!

Dim db As QueryTable

... should be ...

Dim db As Database

Also don't use dbOpenTable. You can only use the dbOpenTable option with
local tables, not with queries or linked tables. Try dbOpenDynaset or
don't
pass the optional argument at all, as dbOpenDynaset is the default if the
source is not a local table. See the help topic 'OpenRecordset Method
(DAO)'
for details.

--
Brendan Reynolds
Access MVP

Dear all,

I have a query named [Query_Name]. I want to transfer the recordset
over
to
Excel.

So by referring to the
<http://www.mvps.org/access/modules/mdl0035.htm>, I
have the following codes:

.
Dim db As QueryTable
.
Set db = CurrentDb
...
Set rs = db.OpenRecordset("Query_Name", dbOpenTable)

Then I'll get the following error:
Run-time error '13' - Type mismatch.

The debugger will point to the "Set db = CurrentDb" statement.

What kinds of mistake I have made?

Thanks in advance!!