Solved

access odbc export

Posted on 2000-04-03
15
256 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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
 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

777 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