Error Exporting Access Query Results to Excel

  • Thread starter Thread starter Ron Mittelman
  • Start date Start date
R

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...
 
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/




Ron Mittelman said:
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...
Click to expand...
 
Back
Top