Error Exporting Access Query Results to Excel

[Ron Mittelman]

I'm having 2 different problems exporting query results from Access to Excel
(Office 2007).

Problem 1:

I do what I've always done, in Access VBA get ADO recordset from query
results, instantiate Excel, get a range object, use the range object's
CopyFromRecordset method. This has always worked in the (pre-Office 2007)
past for me. It is much easier than iterating records and columns and
assigning values to cells. Now, I get an error -2147467259 Automation Error
Unspecified Error. I'm guessing this is because 2 of my recordset fields are
pictures in Attachment type fields, which I've never used. I'm guessing
Excel doesn't like this field type in the CopyFromRecordset.

Problem 2:

I tried to mitigate problem 1 by modifying the query to not include the
Attachment fields. This is totally not working.

I'll start by describing my database. I'm using a template supplied by
Access for the Contacts database. I created my database, then added various
fields to the contacts table. The query I'm using is called "Contacts
Extended", which has a custom field, "Send To", which is the result of an IIf
statement which tests other column values and uses them if non-null. The
query basically says "Select [Send To], Contacts.* From Contacts Order By..."

This query of course returns all of the fields in the table plus one extra,
and causes problem 1 above.

I make a new query called "Contact List", which says "Select [Send To],
Contacts.Name, Contacts.Address, ... etc. From Contacts Order By...". Both
queries work perfectly fine in Access. In VBA code, I do the following:

Dim oRS As ADODB.Recordset
oRS.Open "[Contacts Extended]", CurrentProject.Connection, adOpenStatic,
adLockReadOnly

The above query works, but gives me problem 1 adding it to the worksheet in
Excel.

If I replace the query name above with "[Contact List]" and execute the
code, I get:

Run-time error '-2147217900 (80040e14)':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or
'UPDATE'.

No matter what fields I put in the query (including all fields), this
happens. On the query which selects [Send To], Contacts.* everything works
fine.

What is the deal here? Any ideas?

Thanks...

 

[Ken Snell]

My guess is that the SQL statement you are trying to use for the second
query (the subset) is not properly formatted / has bad syntax; or something
in the SQL statement is being incorrectly read/interpreted by the recordset
call in VBA code.

Open your Contact List query in SQL mode, and copy the full SQL statement.
Paste it into a reply to this thread; let's see if it may contain something
that would error in ADO recordset.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

I'm having 2 different problems exporting query results from Access to
Excel
(Office 2007).

Problem 1:

I do what I've always done, in Access VBA get ADO recordset from query
results, instantiate Excel, get a range object, use the range object's
CopyFromRecordset method. This has always worked in the (pre-Office 2007)
past for me. It is much easier than iterating records and columns and
assigning values to cells. Now, I get an error -2147467259 Automation
Error
Unspecified Error. I'm guessing this is because 2 of my recordset fields
are
pictures in Attachment type fields, which I've never used. I'm guessing
Excel doesn't like this field type in the CopyFromRecordset.

Problem 2:

I tried to mitigate problem 1 by modifying the query to not include the
Attachment fields. This is totally not working.

I'll start by describing my database. I'm using a template supplied by
Access for the Contacts database. I created my database, then added
various
fields to the contacts table. The query I'm using is called "Contacts
Extended", which has a custom field, "Send To", which is the result of an
IIf
statement which tests other column values and uses them if non-null. The
query basically says "Select [Send To], Contacts.* From Contacts Order
By..."

This query of course returns all of the fields in the table plus one
extra,
and causes problem 1 above.

I make a new query called "Contact List", which says "Select [Send To],
Contacts.Name, Contacts.Address, ... etc. From Contacts Order By...".
Both
queries work perfectly fine in Access. In VBA code, I do the following:

Dim oRS As ADODB.Recordset
oRS.Open "[Contacts Extended]", CurrentProject.Connection, adOpenStatic,
adLockReadOnly

The above query works, but gives me problem 1 adding it to the worksheet
in
Excel.

If I replace the query name above with "[Contact List]" and execute the
code, I get:

Run-time error '-2147217900 (80040e14)':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT'
or
'UPDATE'.

No matter what fields I put in the query (including all fields), this
happens. On the query which selects [Send To], Contacts.* everything
works
fine.

What is the deal here? Any ideas?

Thanks...