• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

access odbc export

how can i export a query result to excel when the query results are from an external database(odbc)?
0
scoot13
Asked:
scoot13
  • 7
  • 7
1 Solution
 
mgrattanCommented:
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
 
scoot13Author Commented:
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
 
JCECommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
scoot13Author Commented:
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
 
mgrattanCommented:
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
 
scoot13Author Commented:
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
 
mgrattanCommented:
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
 
scoot13Author Commented:
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
 
mgrattanCommented:
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
 
scoot13Author Commented:
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
 
mgrattanCommented:
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
 
scoot13Author Commented:
any thoughts on why it would be having problems using a pass thru query?
0
 
mgrattanCommented:
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
 
scoot13Author Commented:
thanks for your help.  i had pretty much drawn the same conclusion.  thanks again.
0
 
mgrattanCommented:
You're welcome.  Glad I was able to provide at least some help.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now