Error Exporting Access Query Results to Excel

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...
-- 
Ron Mittelman
0
Utf
5/4/2010 10:46:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

1 Replies
749 Views

Similar Articles

[PageSpeed] 35

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" <RonMittelman@discussions.microsoft.com> wrote in message 
news:BEF705C1-639E-4502-B9B7-494B9373B2AA@microsoft.com...
> 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...
> -- 
> Ron Mittelman 


0
Ken
5/7/2010 3:37:53 PM
Reply:

Similar Artilces:

Converting QuatroPro to Excel
How can convert a quatropro spreadsheet to an excel spreadsheet. Any help? Thanks, Bob Open the file in Quattro Pro and save it in one of the supplied Excel formats. -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Bob" <anonymous@discussions.microsoft.com> wrote in message news:36c601c40128$9e26a970$a601280a@phx.gbl... > How can convert a quatropro spreadsheet to an excel > spreadsheet. Any help? > Thanks, > Bob ...

How do I use a "subtract" function in Excel?
Hi Kelly if you want to subtract the value in B1 from the value in A1 and have the answer display in C1 then in C1 type =A1-B1 Hope this helps Cheers JulieD "Kelly" <Kelly@discussions.microsoft.com> wrote in message news:5DF4C83F-6BF1-40A8-BC8A-692058A5C70B@microsoft.com... > ...

Error: Invalid byte was found at byte index 63.
Does anyone know what this means: "Invalid byte was found at byte index 63. " If yes, please help. Apogee Apogee wrote: > Does anyone know what this means: > > "Invalid byte was found at byte index 63. " That means exactly what it says: At index 63 XML parser found a byte, which is invalid either according document's encoding or is forbidden in XML documents at all (see list of allowed in XML characters at http://www.w3.org/TR/2000/REC-xml-20001006#charsets) -- Oleg Tkachenko http://www.tkachenko.com/blog Multiconn Technologies, Israel ...

Excel 2007
When I select cells to copy as a picture in Excel 2007, the resolution is terrible. Text and objects with shadow's are very blotchy when pasting the picture. How do you change the resolution of a 'Copy Picture'? ...

Input Excel 'Password to Open' through control in access form
Hi All, We know,Excel has prompt password to open it files. Is it possible to create a code that can supplies the excel prompt password?.So that when we open the excel file through our access control in a form, the excel files can be opened automatically.But when the excel files opened from its default icon,it will prompt a password first. ...

Error Generating the Offlice Address Book
I have a mixed site with 3 5.5 server and 4 2003 servers. I installed 2003 SP1 a few weeks back and since then I'm having an issue generating my Offline Address Book. Here the event log messages I'm getting. Event ID 9331: OALGen encountered error 80040107 (internal ID 501023d) accessing the public folder store while generating the offline address list for address list '/'. - Default Offline Address List For more information, click http://www.microsoft.com/contentredirect.asp. Event ID 9335: OALGen encountered error 80040107 while cleaning the offline address list public ...

XCH Error 3092, OAB replication
I am getting error 3092 for OAB in Exchnage 2003 (migrated from 5.5) "Error 1129 occurred while processing a replication event. Folder: (3-8) NON_IPM_SUBTREE\OFFLINE ADDRESS BOOK\EX:/o" Tried to delete offilne addressbook and recreate but error has not stopped. Any help will be a great help on where to look to get rid of this issue. Thanks ...

Compare records in a query then write equation??
Hi all, i have a problem and i need help, the case is as follows: the default rule is that i have 4 fields, (Date, Team, Score). each team is allowed to have one score per day but sometimes it can have 2 scores per day, when this is the case i wanna multiply each score by a certain number and have one score instead of 2 scores (similar to average but not average). So, i need to have a condition which compares records, and if this is the case, formulates this equation and gives me one score instead of 2 scores. Please help SELECT Team, [Date], Sum(Score)/Count(Score) * IIF(Count(Score) =1,1,S...

Excel not Access
I have designed an Access database that holds records relating to my stores audit results going back for about 5 years plus a load more information relating to these stores. This was used to produe a pack once a month, however a change in senior management means that I have got to shelve this and prodce a similar pack in Excel. The idea would be that the user could select a month or a 12 mnth date range that would produce data that could then be used to populate a number of excel templates that have been designed. Having not used excel for years I would be grateful for any suggestion...

How to keep format when importing Excel into SQL.
Can anybody help me please? When I import Excel file into SQL, a field that formated as 0000000000 (custom), loosing leading zeros. I tried to change data type in SQL after importing. No luck. I appretiate any help. See if using IMEX=1 helps: http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html -- Plamen Ratchev http://www.SQLStudio.com Plamen, I tried this, but got an error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered." Thank you very much for looking into this. "Plamen Ratchev" wr...

Auto-format in Microsoft Excel.
Each time i try to enter a number range, for example, 8- 10, in Excel, it constantly re-formats it to a date. If I change the formatting to "General" it turns it into a random number, usually 38209. I would like to turn off ALL auto-formatting, but that's probably asking too much. How do I disable this frustrating feature? thanks. Hi Paul When you enter "8-10" in a cell, that's not strictly a number but Excel thinks you mean a date so converts it as such. A date is a number and in your example the number 38209 represents August 10 2004 which is not a ra...

Invalid XML error when I open customization setting
I have a problem when I try to open customization setting after I import an entity. The system errored "Invalid XML" "The XML passed to the platform is not well-formed XML". Please recommend how to resolve this problem. Thanks. ...

Opening Excel Workbooks
I'm running into an issue where if I click on an Excel file through My Documents, it doesn't automatically bring it up. I get the toolbar but the actual spreadsheet doesn't appear on the screen. I have to click on the taskbar to get it to pop up. If I already have Excel active and I open a file through Excel, this doesn't happen. Any ideas? Here is a similar thread: http://www.excelforum.com/showthread.php?s=&threadid=237195 Rolli -- Message posted from http://www.ExcelForum.com Hi, Take a look at Tools-Options-General tab- uncheck ignore other application...

Radar chart in Access 2007 report
Can you add a Radar chart to an access 2207 report? ...

Strange Access Denied Problem with Windows 7
I got a new computer about six months ago that came with Windows Vista Home Premium 64bit. Before that I had done all of my .NET development either on an XP Pro VM or my former XP Pro computer at home. Shortly after getting my new computer at home, I also got a license for VMWare to be able to test my software on multiple platforms and configurations. I had wrote an application originally in VB.NET that was a simple backup utility. It supports mutiple backup configurations. Any given copnfiguration would define a backup which would be a list of files to backup, a list of folders to ...

Unexpected error message on closing an Excel file
Suddenly I am getting the following message when I try to close a workbook: "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again" The mysterious thing is that it does not happen consistently and that, after I click OK after the above message, I can still save the file. What might be the cause of this error message and can the "invalid reference" be tracked down using one of the utility add-ins such as J. Walkenbach's PUP? If it only happens when you close ...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...

Linker Error after upgrade from VC7.1
Hello all, After upgrading a VC7.1 project to visual studio 2005, it failed to build in the release configuration with the follwoing error : 1>nafxcw.lib(winocc.obj) : error LNK2005: "public: class CWnd * __thiscall CWnd::GetDlgItem(int)const " (?GetDlgItem@CWnd@@QBEPAV1@H@Z) already defined in InstallDlg.obj The debug build works fine. The project uses MFC in a static library. Well, after doing some research, it seems that this one is tied to the fact that in a release configuration, _AFX_ENABLE_INLINES is defined, so inline functions are embedded in the .obj file. Sure there...

help with simple maths in excel
I want to do a simple arithmatic excercise in excel for my grand daughter. It is stuff like 2+2 =4 Smart me has hit a problem at the first hurdle... I need to put 2 in one cell the + in another cell and then 2 in another = in another and then she puts the answer in the next one. So the above would have 4 cells completed and she would put the answer in the 5th one. When I use the + or = sign in a cell of its own it (excel) thinks I am doing an equation, is there a way around this? I will work on the answer like if she gets it correct or wrong how I will do that...a sound or som...

HELP! Need to export hourly sales data on POS (NOT RMS)
How can I export hourly sales data across a date range? For instance, I want to show hourly sales for the month of October so I can graph it and post it in our break room. If I can't export hourly data, can I export daily sales? The built-in reports don't address this data format. This is a multi-part message in MIME format. ------=_NextPart_000_008E_01C826DC.CBC512D0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit Mark, This should work for you. Keep in mind it takes up to 5-10 minutes to load...

adding name /creating field/query?
Hello, I can create an invoice_number field in a query using the primary field ID from the main table as invoice_number: ID but if ID say is 100, I cannot work out how to create renewal_invoice_100 Cheers Geoff Geoff We aren't there. We can't see what you're looking at. Where did "renewal_invoice_100" come from and what does it mean? Please post the SQL statement of the query you are trying to use. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://micro...

How To Copy MS Word mailing labels into Excel
I have a word doc that I want to put into Excel. I want to add some more fields to the names and addresses. Is this simple or do I have to learn how to program? Michael Rodriguez City of Grand Prairie Michael, have you tried to copy and paste the data into excel? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "Michael Rodriguez" <mrodrigu@gptx.org> wrote in messa...

Filter recordset using query results
Hi all I have a form based on a query called [qry Quarterly Planning], it lists all Itineraries on the system. On this form you can filter records by specifying a Start and End Date for the [ReviewDate] and/or [Specialist]. It is a subform on a main unbound form, lets call this Subform1. Along side this I have another subform (Subform2) which displays ReviewDates that exist against an Itinerary. In other words Subform1 has a start date of an activity and if the activity lasts longer than 1 day, then the additional dates are stored in Subform2 (ItineraryDates). Currently when I...

How do I use traffic lights in excel
I am wanting to use traffic lights in excel that change colour based on the result of a variance cell, ie if the result of the cell is 10 make the traffic light green, if it is 20 make the traffic light amber, if the result is 30 make the traffic light red. How do I do this? Shorty Format>Conditional Formatting>Cell Value is: Note: you can add up to 3 conditions(4 if you count default) Gord Dibben Excel MVP On Wed, 22 Dec 2004 16:35:03 -0800, Shorty <Shorty@discussions.microsoft.com> wrote: >I am wanting to use traffic lights in excel that change colour based on the &g...

error on upgrade: ID 4386
Hello, I am upgrading from DPM 2010 RC to DPM 2010 RTM, 64 bit version, on Win 2008 R2 standard. The RC is working without any issues. The upgrade scenario is supported. At the very end of the process the upgrade fails giving me the following. _____ The SQL Server installation failed because a restart was pending on this computer. Restart the computer and then start DPM Setup again. ID: 4386. Details: Unknown error (0x84be0bc2) _____ Restarting the server does not correct the issue. On the next attempt I am getting the same error message. Where to look for the pending rest...