Solved

access odbc export

Posted on 2000-04-03
15
253 Views
Last Modified: 2006-11-17
how can i export a query result to excel when the query results are from an external database(odbc)?
0
Comment
Question by:scoot13
  • 7
  • 7
15 Comments
 
LVL 14

Expert Comment

by:mgrattan
ID: 2681831
I don't think it matters where the results come from.  If you have a query in Access you can save the results to Excel using the Docmd.TransferSpreadsheet action--

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel97,"QueryName","FileName",True

QueryName is the query you want to export.
FileName is the Excel file to which you want to export.
True is if you want to export the field names as column headers.

0
 

Author Comment

by:scoot13
ID: 2681856
is your code part of the sql statement?  it looks like vb.  i try exporting the filtered results and it makes me reconnect to the database.  then it allows me to choose a folder(and name it, what type of format) to place the data.  when i select save all, it returns an error message "format is not supported".  any help is appreciated.  thank you
0
 
LVL 1

Expert Comment

by:JCE
ID: 2681858
Hallo scoot13

Highligt your query in the database window and use "Save as/export" on the Archive menu!

Why don't you link the external data direct into Excel?

Good Luck
JCE

0
 

Author Comment

by:scoot13
ID: 2681875
the results are too large for excel.  over 65k records.  i've done exactly what you said and i get an error that says "Operation is not supported for this type of object".  
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 2681878
My code is VB.  You can run it from a button on a form.  Just copy/paste the code into the button's OnClick event.  If you are not familiar with VB in Access then you can do this manually via the Save As/Export menu as suggested by JCE.

If you need to do this frequently then code is the way to go since it can be easily modified to accept parameters which can be passed from the same Form from which you are clicking the button to run the code.

I think JCE had a good suggestion with using Excel to link your database.  If you have the ODBC add-in installed this should be fairly straight-forward.
0
 

Author Comment

by:scoot13
ID: 2681892
my results are too large for excel to handle.  when i try and use the Save as/Export function i get a error message saying "Operation is not supported for this type of object". i've never used vb in access only in excel.  i was hoping not to have to go that route though.

0
 
LVL 14

Expert Comment

by:mgrattan
ID: 2681901
Assuming you have created and saved a Select query in Access that selects records from a linked ODBC table.  Run the query so you can see the results; then select File,Save As/Export from the menu.  This should work.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:scoot13
ID: 2681918
its a pass through query that i created with sql.  when i do file, save as...i save it as a query. then when i select the export function i get the error "Operation is not supported for this type of object".  it seems like it doesn't want to export query object.

0
 
LVL 14

Expert Comment

by:mgrattan
ID: 2681931
Are you viewing the results or just have the Query object selected in the Database Container?  You need to have the results visible to export them to another file.
0
 

Author Comment

by:scoot13
ID: 2681939
good question, although i'm not sure i completely understand it.  i can see the results, and they are in what looks like a normal Acces database table.  would this be the database container?
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 2681955
The database container is the screen with the tabs labeled "Tables", "Queries","Forms", "Reports","Modules".  If you are viewing the results in what looks like an Access table then you have opened the query in a "Datasheet" view which is suitable for export.  If you are still having a problem it might be due to the fact that this is a pass through query instead of a standard Select query.  In that case you will probably want to try changing the query to a simple Select query.  It might take a little longer to run than a pass through query but you will then be able to export the results.
0
 

Author Comment

by:scoot13
ID: 2681958
any thoughts on why it would be having problems using a pass thru query?
0
 
LVL 14

Accepted Solution

by:
mgrattan earned 50 total points
ID: 2682017
My SWAG (Scientific Wild-Ass Guess) is that Access can't save the results since the results weren't generated by Access--they were generated by the ODBC database server.  I wasn't able to find any good info on this in the Access knowledge base.  Sorry :-(.

0
 

Author Comment

by:scoot13
ID: 2682025
thanks for your help.  i had pretty much drawn the same conclusion.  thanks again.
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 2682033
You're welcome.  Glad I was able to provide at least some help.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now